SQLTeam.com | Weblogs | Forums

Date range for report


#1

So I am trying to create a report for monthly part sales, per part, in specified time period. If there was not a sale of the item in a month then return a 0.

sELECT prt.VenPrtId, FORMAT(sls.DateInvoice,'yyyyMM') as YM, Sum(itm.QtyShip) as Qty
from PTSLSITM itm
join
PTPRT PRT on prt.prtid=itm.ptitm
join
PTSLS sls on sls.SlsId=Itm.SlsId
where sls.Estimate=0
and sls.DateInvoice between '12/01/2017' and '07/01/2018'
group by prt.VenPrtId, FORMAT(sls.DateInvoice,'yyyyMM')
order by prt.VenPrtId, FORMAT(sls.DateInvoice,'yyyyMM')

Results Are

VenPrtId YM QTY
000 446 16 49 201712 3
000 446 16 49 201801 1
000 446 16 49 201803 1
000 446 16 49 201807 1

Results Wanted

VenPrtId YM QTY
000 446 16 49 201712 3
000 446 16 49 201801 1
000 446 16 49 201802 0
000 446 16 49 201803 1
000 446 16 49 201804 0
000 446 16 49 201805 0
000 446 16 49 201806 0
000 446 16 49 201807 1

#2

hi

the reason why this happens

201801
201802
201804

notice the 201803 missing
for this 0 wont come

what you have to is
use tally table
and create a date month range
201801
201802
201803
201804
here it creates the missing 201803

so now it shows 0

201801 10
201802 7
201803 0
201804 15

hope you understand
:slight_smile:
:slight_smile:

please let me know
i can do it SQL with sample data
and show

\


#3

I'm not sure how to write a tally table.


#4

Like this
https://www.dotnetheaven.com/article/generate-sequence-of-dates-using-tally-tables-in-sql-server


#5

Hi cardgunner

There are several ways

If you have the overall idea of what tally table ..is. How does it fit into this scenario

I googled and found this video

Please give me 1 day
Not able to access my computer
I will give complete details in 1 day

Seniors please help


#6

The tally table should be a table function, but here it is as a cte. Use this and then left join to your results above. Any Nulls can be cast as 0

Declare @StartDate date = '1/12/2017',
        @EndDate Date = '1/7/2018'

;WITH e1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt(n))
    , e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b)
    , e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b)
    , DateYears AS (SELECT Year(@StartDate) YearCoverage
					union 
					Select Year(@EndDate))
    , eTally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e2 a CROSS JOIN e4 b)

select PRT.prt.VenPrtId, cast(Months + '/1/' + cast(YearCoverage as char(4)) as Date) CalendarMonths 
  from (
SELECT top 12 right('00' + cast(n as varchar(2)), 2) as Months
 FROM [eTally]) v
	Cross Apply DateYears
	Cross apply PTPRT PRT

#7

harishgg1,
I think I got the idea. In order to get the part# and all months I think I will have to first create a coss join with a distinct parts and the dates. Then do a left join with the earlier select and create an ISNULL for the null values to be 0. I'm working on this I'll get back if it works.

Thank you