We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Rollup summaries by date

jdr0606
jdr0606 asked
on
Medium Priority
326 Views
Last Modified: 2012-05-11
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'
into LPStatistics.dbo.tmpSLA
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
	AND IV2.LOCNCODE='MEMPHIS'
left join COMP.dbo.hm00500 hm5 on hm5.itemnmbr=iv1.itemnmbr
WHERE 
	SOP1.SOPTYPE = sop2.SOPTYPE 
	AND ((SOP1.ORDRDATE between getdate()-1 and getdate()-1) 
	AND (SOP1.SOPTYPE=2) 
	AND (sop2.CMPNTSEQ=0))


/* Total Lines */
insert into LPStatistics.dbo.ItemSLA
Select
	MAX(OrderDate) as 'SLADate'
	, 'Total' as 'SLAType'
	, COUNT(ItemNumber) as 'SLATotalLines'
from LPStatistics.dbo.tmpSLA

Open in new window

Comment
Watch Question

A couple of questions:
1. Does this need to create summary for all days till the current date on any particular day or just the summary for that day?
2. What is the expected output if this runs on holiday?

Also, it would be great if you could provide the sample data and expected output.
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.