• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Rollup summaries by date

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

0
jdr0606
Asked:
jdr0606
1 Solution
 
sameer2010Commented:
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.
0
 
wdosanjosCommented:
The best approach is to create a function that does the evaluation for you and use it in your queries.

Here is the function definition. It assumes your Holiday Table is called Holidays and it has a column called Holiday (datetime).  Please update it accordingly if that's not the case.

CREATE FUNCTION WorkDay 
(
    @date datetime
)
RETURNS datetime
AS
BEGIN
    DECLARE @return datetime
    DECLARE @dw int

    SET @dw = DATEPART(dw, @date)
    
    IF @dw = 7 -- Saturday?
        SET @return = DATEADD(day, 2, @date)
    ELSE IF @dw = 1 -- Sunday?
        SET @return = DATEADD(day, 1, @date)
    ELSE IF EXISTS (Select 1 From Holidays Where Holiday = @date) -- Is a holiday?
        Select @return = MIN(NextDay)
          From (Select Case DATEPART(dw, holiday) 
                            When 6 Then DATEADD(day, 3, holiday) -- Friday holiday
                            When 7 Then DATEADD(day, 2, holiday) -- Saturday holiday
                            Else DATEADD(day, 1, holiday)
                       End As NextDay
                  From Holidays
                 Where holiday > @date) n
         Where NextDay not in (Select Holiday From Holidays)
    ELSE
        SET @return = @date
        
    RETURN @return

END
GO

Open in new window


Here is how you can use it:

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

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now