Rollup summaries by date

Posted on 2011-04-20
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.
	, sop2.SOPNUMBE as 'SalesOrderNumber'
	, sop2.ITEMNMBR as 'ItemNumber'
	, IV1.ITMCLSCD as 'ItemType'
	, HM5.HM_String_10 as 'Level'
into LPStatistics.dbo.tmpSLA
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
	AND ((SOP1.ORDRDATE between getdate()-1 and getdate()-1) 
	AND (sop2.CMPNTSEQ=0))

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

Open in new window

Question by:jdr0606
    LVL 13

    Expert Comment

    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.
    LVL 23

    Accepted Solution

    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.

        @date datetime
    RETURNS datetime
        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)
            SET @return = @date
        RETURN @return

    Open in new window

    Here is how you can use it:

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

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now