I have a summary that I will be running daily that will create some total summaries.
The issue I have is that I need to create those daily summaries by day back to 12/2/2011.
In addtion to just summarizing each day, If it is Saturday or Sunday they will roll into the Monday work day.
In addition to Saturday and Sunday I also have a table of holidays that would be treated similar to Saturday and Sunday and roll into the next working day.
I have attached a snippet of my code which works for Monday through Friday.
Thanks for any assistance.
SELECT SOP1.ORDRDATE as 'OrderDate'
, sop2.SOPNUMBE as 'SalesOrderNumber'
, sop2.ITEMNMBR as 'ItemNumber'
, IV1.ITMCLSCD as 'ItemType'
, IV2.ORDRPNTQTY as 'OrderQTY'
, HM5.HM_String_10 as 'Level'
FROM COMP.dbo.SOP10100_SOP30200_UNION SOP1
join COMP.dbo.sop10200_sop30300_union sop2 on sop2.sopnumbe=sop1.sopnumbe
left join COMP.dbo.iv00101 iv1 on iv1.itemnmbr=sop2.itemnmbr
left join COMP.dbo.iv00102 iv2 on iv2.itemnmbr=sop2.itemnmbr
left join COMP.dbo.hm00500 hm5 on hm5.itemnmbr=iv1.itemnmbr
SOP1.SOPTYPE = sop2.SOPTYPE
AND ((SOP1.ORDRDATE between getdate()-1 and getdate()-1)
/* Total Lines */
insert into LPStatistics.dbo.ItemSLA
MAX(OrderDate) as 'SLADate'
, 'Total' as 'SLAType'
, COUNT(ItemNumber) as 'SLATotalLines'