Calculate the Number of Open Backorders/Canceled SKUs in a Given Period

— Calculate the Number of Open Backorders/Canceled SKUs in a Given Period

— Variables below …
–Change the = ‘X’ to IN (‘3’, ‘4’) to include both invoices and returns
— Change the year(docdate) XXXX with the year
— Change the month(docdate) XX with the period designation
— Change the = ‘XXXX’ to IN (‘2015′,’2016’) to include multiple years
— Change the = ‘XX’ to IN (’01’, ’02’, ’03’) to include multiple months

select day(d.docdate) [date], count(d.sopnumbe) [# of orders with backorders/canceled items]
from
(select a.sopnumbe, a.itemnmbr, a.qtytoinv [QTY to Invoice], a.qtyfulfil [QTY Fulfilled], a.qtytbaor [QTY to Back Order], a.qtycance [QTY Canceled], b.docdate
from sop10200 a
inner join sop10100 b on
b.SOPNUMBE = a.SOPNUMBE
where a.soptype = ‘2’ and year(b.docdate) = ‘XXXX’ and month(b.docdate) = ‘XX’
) d
where d.[QTY to Back Order] <> 0 or d.[QTY Canceled] <> 0
group by d.docdate