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')
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
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.
