Applying different time period Groupings to a set of data

Question

The following was a pattern I started to use two years ago and it is repeated over and over in my legacy code.

It effectively groups the same data using different time periods.

Is there a standard way I should be approaching this or is this long winded method as good as I'll get?

Another way of putting this question is how can the following be made more concise?
All 4 queries come out of the same data source and all four go into the same output table can these 4 queries be amalgamated into 1 shorter script?

DECLARE @myDate DATETIME = CONVERT(DATETIME,CONVERT(VARCHAR(11),GETDATE(),106)); 
DECLARE @myFirstDateLastMth CHAR(8) =CONVERT(CHAR(6),DATEADD(mm,-1,@myDate-1),112) + '01';
DECLARE @myFirstDateCurrentMth CHAR(8) =CONVERT(CHAR(6),DATEADD(mm,0,@myDate-1),112) + '01'; 

DELETE FROM WH.dbo.tb_myTable

--day on day==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod =
    CASE 
        WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 1,112) THEN 'Day'                    
        WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 2,112) THEN 'Day-1'  
    END,
  Game              = x.Name,
  Score             = SUM(x.Score),
  Ticks             = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHEREx.DateKey >= CONVERT(VARCHAR(11),@myDate - 2,112)
GROUP BY
  CASE 
    WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 1,112) THEN 'Day'                    
    WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 2,112) THEN 'Day-1'  
  END,
  x.Name;

--wk on wk==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod =
        CASE 
        WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 7,112) THEN 'Week'                  
        WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 7,112) 
                    AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 14,112)  
                            THEN 'Week-1'
    END,
  Game               = x.Name,
  Score              = SUM(x.Score),
  Ticks              = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHERE   x.DateKey >= CONVERT(VARCHAR(11),@myDate - 14,112)
GROUP BY
  CASE 
    WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 7,112) THEN 'Week'                  
    WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 7,112) 
                AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 14,112)  
                        THEN 'Week-1'
    END,
  g.Name;                   


--mth on mth==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod =
    CASE 
        WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 28,112) THEN 'Month'                    
        WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 28,112) 
                        AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 56,112)  
                                THEN 'Month-1'
    END,
  Game               = x.Name,
  Score              = SUM(x.Score),
  Ticks              = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHERE   x.DateKey >=  CONVERT(VARCHAR(11),@myDate - 56,112)
GROUP BY
  CASE 
    WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 28,112) THEN 'Month'                    
    WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 28,112) 
                AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 56,112)  
                        THEN 'Month-1'
  END,
  g.Name;                   


--MTD and PrevCalMonth==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod
  = CASE 
        WHEN  x.DateKey >= @myFirstDateCurrentMth   THEN 'MTD'
        WHEN  x.DateKey < @myFirstDateCurrentMth  
                AND  x.DateKey >=@myFirstDateLastMth THEN 'PrevCalMonth'                                      
  END,
  Game              = x.Name,
  Score             = SUM(x.Score),
  Ticks             = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHERE   x.DateKey >=  CONVERT(CHAR(6),DATEADD(mm,-1,@myDate-1),112) + '01'
GROUP BY
  CASE 
    WHEN  x.DateKey >= @myFirstDateCurrentMth   THEN 'MTD'
    WHEN  x.DateKey < @myFirstDateCurrentMth  
            AND  x.DateKey >=@myFirstDateLastMth THEN 'PrevCalMonth'            
  END,
  g.Name;   

Answer 1

Use parameters - VALUES As a Table Source and apply them as parameters in CROSS APPLY with derived table

DECLARE @myDate datetime = CAST(GETDATE() AS date);
IF OBJECT_ID('WH.dbo.tb_myTable') IS NOT NULL DROP TABLE WH.dbo.tb_myTable
SELECT TimePeriod, Game, Score, Ticks, ScorePerTicks
INTO WH.dbo.tb_myTable
FROM (VALUES('Day', DATEADD(day, -1, @myDate), @myDate),
            ('Day-1', DATEADD(day, -2, @myDate), DATEADD(day, -2, @myDate)),
            ('Week', DATEADD(day, -7, @myDate), @myDate),
            ('Week-1', DATEADD(day, -14, @myDate), DATEADD(day, -8, @myDate)),
            ('Month', DATEADD(day, -28, @myDate), @myDate),
            ('Month-1', DATEADD(day, -56, @myDate), DATEADD(day, -29, @myDate)),
            ('MTD', DATEADD(DAY, 1 - DAY(@myDate), @myDate), @myDate), 
            ('PrevCalMonth', DATEADD(DAY, 1 - DAY(@myDate), DATEADD(MONTH, -1, @myDate)), DATEADD(DAY,  - DAY(@myDate), @myDate)))
RParameters(TimePeriod, BDate, EDate)
  CROSS APPLY (SELECT x.Name AS Game,
                      SUM(x.Score) AS Score,
                      SUM(x.Ticks) AS Ticks,
                      SUM(x.Score) / SUM(x.Ticks) AS ScorePerTicks
               FROM #LimitedBetinfo x
               WHERE DateKey BETWEEN RParameters.BDate AND RParameters.EDate
               GROUP BY Name) AS o

Demo on SQLFiddle

Answer 2

I would make it a single insert statement.

Would prefer for now not to use the group by grouping sets, cube, or rollup as that I don't see how I could limit the rows calculated over individual day groups from being less than those calculated over larger time period groups.

So, to keep that from happening you could create a common-table-expression (;WITH mycte AS (...subquery...)), temp table, table variable, or XML formatted text object that would contain the time periods, one row/element for each.

This script can also be run with more or less time periods defined to get all results with only one trip from the app to the server.

Here's an example with temp table, that could also be easily made into a table variable:

--Define time periods
CREATE TABLE #TempTimePeriods (
    TimePeriod VARCHAR(20) PRIMARY KEY,
    TPBegin VARCHAR(11) NOT NULL,
    TPEnd VARCHAR(11) NULL
);

DECLARE @myDate DATETIME = '2012-10-10';
DECLARE @myDateMinusOne DATETIME = DATEADD(dd, -1, @myDate);
INSERT INTO #TempTimePeriods ( TimePeriod, TPBegin, TPEnd )
SELECT [TimePeriod], CONVERT(VARCHAR(11), TPBegin, 112) TPBegin, CONVERT(VARCHAR(11), TPEnd, 112) TPEnd
FROM (
    SELECT 'Day'          [TimePeriod], @myDate - 1 TPBegin, @myDate -  1 TPEnd UNION ALL
    SELECT 'Day-1'        [TimePeriod], @myDate - 2 TPBegin, @myDate -  2 TPEnd UNION ALL
    SELECT 'Week'         [TimePeriod], @myDate - 7 TPBegin,                                  NULL TPEnd UNION ALL
    SELECT 'Week-1'       [TimePeriod], @myDate - 14 TPBegin, @myDate -  8 TPEnd UNION ALL
    SELECT 'Month'        [TimePeriod], @myDate - 28 TPBegin,                                  NULL TPEnd UNION ALL
    SELECT 'Month-1'      [TimePeriod], @myDate - 56 TPBegin, @myDate - 29 TPEnd UNION ALL
    SELECT 'MTD'          [TimePeriod], DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne) TPBegin, NULL TPEnd UNION ALL
    SELECT 'PrevCalMonth' [TimePeriod], DATEADD(mm,-1,DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne)) TPBegin, DATEADD(dd, -1 * DAY(@myDateMinusOne), @myDateMinusOne) TPEnd
) TT;  

And here is the main query...

--compute/insert results
INSERT INTO WH.dbo.tb_myTable
SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM #TempTimePeriods TimePeriods
--for periods with no data use left outer join to return 0-value results, otherwise inner join
LEFT OUTER JOIN #LimitedBetInfo x 
ON x.DateKey >= [TimePeriods].TPBegin
AND (
    [TimePeriods].TPEnd IS NULL
    OR x.DateKey <= [TimePeriods].TPEnd
)
GROUP BY TimePeriods.TimePeriod, x.Name

You could also eliminate the the #TempTimePeriods table using a Common-Table-Expression below:

DECLARE @myDate DATETIME = '2012-10-10';
DECLARE @myDateMinusOne DATETIME = DATEADD(dd, -1, @myDate);
;WITH TimePeriods AS (
    SELECT [TimePeriod], CONVERT(VARCHAR(11), TPBegin, 112) TPBegin, CONVERT(VARCHAR(11), TPEnd, 112) TPEnd
    FROM (
        SELECT 'Day'          [TimePeriod], @myDate - 1 TPBegin, @myDate -  1 TPEnd UNION ALL
        SELECT 'Day-1'        [TimePeriod], @myDate - 2 TPBegin, @myDate -  2 TPEnd UNION ALL
        SELECT 'Week'         [TimePeriod], @myDate - 7 TPBegin,                                  NULL TPEnd UNION ALL
        SELECT 'Week-1'       [TimePeriod], @myDate - 14 TPBegin, @myDate -  8 TPEnd UNION ALL
        SELECT 'Month'        [TimePeriod], @myDate - 28 TPBegin,                                  NULL TPEnd UNION ALL
        SELECT 'Month-1'      [TimePeriod], @myDate - 56 TPBegin, @myDate - 29 TPEnd UNION ALL
        SELECT 'MTD'          [TimePeriod], DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne) TPBegin, NULL TPEnd UNION ALL
        SELECT 'PrevCalMonth' [TimePeriod], DATEADD(mm,-1,DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne)) TPBegin, DATEADD(dd, -1 * DAY(@myDateMinusOne), @myDateMinusOne) TPEnd
    ) TT
)
INSERT INTO WH.dbo.tb_myTable
SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM [TimePeriods]
--for periods with no data use left outer join to return 0-value results, otherwise inner join
LEFT OUTER JOIN #LimitedBetInfo x
ON x.DateKey >= [TimePeriods].TPBegin
AND (
     [TimePeriods].TPEnd IS NULL
     OR x.DateKey <= [TimePeriods].TPEnd
)
GROUP BY [TimePeriods].TimePeriod, x.Name

And lastly you could define the time periods in an XML string-handy for passing to a stored procedure if that's your preference and proceed as follows:

--example XML string with time period definitions
DECLARE @TimePeriodsXml NVARCHAR(MAX) = '
<TimePeriod name="Day" tpbegin="20121010" tpend="20121010" />
<TimePeriod name="Day-1" tpbegin="20121009" tpend="20121009" />
<TimePeriod name="Week" tpbegin="20121004"/>
<TimePeriod name="Week-1" tpbegin="20120927" tpend="20121004" />
<TimePeriod name="Month" tpbegin="20120913" />
<TimePeriod name="Month-1" tpbegin="20120815" tpend="20120912" />
<TimePeriod name="MTD" tpbegin="20121001" />
<TimePeriod name="PrevCalMonth" tpbegin="20120901" tpend="20120930" />
';

and the main query modified to read the XML:

SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM (
    SELECT
    E.TimePeriod.value('./@name', 'VARCHAR(20)') TimePeriod,
    E.TimePeriod.value('./@tpbegin', 'VARCHAR(20)') TPBegin,
    E.TimePeriod.value('./@tpend', 'VARCHAR(20)') TPEnd
    FROM (
        SELECT CAST(@TimePeriodsXml AS XML) tpxml
    ) TT
    CROSS APPLY tpxml.nodes('/TimePeriod') AS E(TimePeriod)
) TimePeriods
--for periods with no data use left outer join to return 0-value results, otherwise inner join
LEFT OUTER JOIN #LimitedBetInfo x
ON x.DateKey >= [TimePeriods].TPBegin
AND (
     [TimePeriods].TPEnd IS NULL
     OR x.DateKey <= [TimePeriods].TPEnd
)
GROUP BY TimePeriods.TimePeriod, x.Name

For an example of how the XML stringed query could be turned into a procedure, to support a single parameter of 1 or more time periods:

CREATE PROCEDURE dbo.GetTimePeriodAggregates
@TimePeriodsXmlString NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM (
    SELECT
    E.TimePeriod.value('./@name', 'VARCHAR(20)') TimePeriod,
    E.TimePeriod.value('./@tpbegin', 'VARCHAR(20)') TPBegin,
    E.TimePeriod.value('./@tpend', 'VARCHAR(20)') TPEnd
    FROM (
        SELECT CAST(@TimePeriodsXml AS XML) tpxml
    ) TT
    CROSS APPLY tpxml.nodes('/TimePeriod') AS E(TimePeriod)
) TimePeriods
LEFT OUTER JOIN #LimitedBetInfo x
ON x.DateKey BETWEEN TimePeriods.TPBegin AND TimePeriods.TPEnd
GROUP BY TimePeriods.TimePeriod, x.Name
END

Which could be run as:

--This declare is just an example, it could be instead a parameter passed from an application
DECLARE @ThisExecutionsXmlString NVARCHAR(MAX) = N'
<TimePeriod name="Day" tpbegin="20121010" tpend="20121010" />
<TimePeriod name="Day-1" tpbegin="20121009" tpend="20121009" />
<TimePeriod name="Week" tpbegin="20121004"/>
<TimePeriod name="Week-1" tpbegin="20120927" tpend="20121004" />
<TimePeriod name="Month" tpbegin="20120913" />
<TimePeriod name="Month-1" tpbegin="20120815" tpend="20120912" />
<TimePeriod name="MTD" tpbegin="20121001" />
<TimePeriod name="PrevCalMonth" tpbegin="20120901" tpend="20120930" />
';

INSERT INTO WH.dbo.tb_myTable
EXEC dbo.GetTimePeriodAggregates @TimePeriodsXmlString=@ThisExecutionsXmlString

Answer 3

It seems that this may be the job for CUBE groupings.

Sorry, I will not give you exact solution to your problem, but the MOCKUP form of select should be like:

select * from
(
    select *,count(*) amount from
    (
        select datepart(HOUR, login_time) as hour, 
               datepart(MINUTE, login_time) as minute, 
               cmd as name
        from sys.sysprocesses
    ) tmp
    group by cube(tmp.hour, tmp.minute, tmp.name)
) tmp2
where tmp2.name is not null and
      (
      (tmp2.hour is not null and tmp2.minute is null) or
      (tmp2.hour is null and tmp2.minute is not null)
      )

One minus - that cube generates too much data for your problem here. So it needs to be filtered out. A big plus would be that you will only need just ONE select into temporary table.

Answer 4

A possible improvement on fred's answer. Not in terms of speed, just readability / modifiability by removing the extra CASE. As a suggestion, I also replaced the passing of both strings (e.g. DAY and DAY-1) with a single string and to have the other just be a concat; this would however cause PrevCalMonth to be displayed as MTD-1 instead (though there are some work-arounds for this).

CREATE PROCEDURE InsertData
  @minLimit date, @maxLimit date, @string nvarchar(50)
AS
  INSERT INTO tb_myTable
  SELECT TimePeriod, Name, SUM(Score) Score, SUM(Ticks) Ticks, 
    SUM(Score)/SUM(Ticks) ScorePerTick 
  FROM
  (
    SELECT *, /* or 'Name, Score, Ticks,' */
      TimePeriod = CASE WHEN x.DateKey >= @maxLimit THEN @string ELSE @string+'-1' END
    FROM #LimitedBetinfo x
    WHERE x.DateKey >= @minLimit
  ) A
  GROUP BY TimePeriod, Name
GO

And use like this:

TRUNCATE TABLE tb_myTable

DECLARE @today date = cast(getdate() as date)
DECLARE @yesterday date = dateadd(day, -1, @today)
EXECUTE dbo.InsertData @yesterday, @today, N'Day' 

DECLARE @thisweek date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)
DECLARE @lastweek date = DATEADD(ww, -1, @thisweek)
EXECUTE dbo.InsertData @lastweek, @thisweek, N'Week' 

DECLARE @prev28 date = dateadd(day, -28, @today)
DECLARE @prev56 date = dateadd(day, -56, @today)
EXECUTE dbo.InsertData @prev56, @prev28, N'Month' 

DECLARE @thismonth date = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
DECLARE @lastmonth date = DATEADD(mm, -1, @thismonth)
EXECUTE dbo.InsertData @lastmonth, @thismonth, N'MTD' 

Answer 5

You can create this stored procedure

CREATE PROCEDURE InsertData
    @minLimit date,
    @maxLimit date,
    @minTerm nvarchar(50),
    @maxTerm nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO tb_myTable
    SELECT 
        [TimePeriod]        = CASE WHEN x.DateKey >= @maxLimit THEN @maxTerm ELSE @minTerm END,
        [Game]              = x.Name,
        [Score]             = SUM(x.[Score]),
        [Ticks]             = SUM(x.[Ticks]),
        [ScorePerTick]      = SUM(x.[Score])/SUM(x.[Ticks])

    FROM #LimitedBetinfo x
    WHERE x.DateKey >= @minLimit
    GROUP BY 
        CASE WHEN x.DateKey >= @maxLimit THEN @maxTerm ELSE @minTerm END,
        x.Name

END
GO

And use like this

TRUNCATE TABLE tb_myTable

DECLARE @today date = cast(getdate() as date)
DECLARE @yesterday date = dateadd(day, -1, @today)
EXECUTE dbo.InsertData @yesterday, @today, N'Day-1', N'Day' 

DECLARE @thisweek date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)
DECLARE @lastweek date = DATEADD(ww, -1, @thisweek)
EXECUTE dbo.InsertData @lastweek, @thisweek, N'Week-1', N'Week' 

DECLARE @prev28 date = dateadd(day, -28, @today)
DECLARE @prev56 date = dateadd(day, -56, @today)
EXECUTE dbo.InsertData @prev56, @prev28, N'Month-1', N'Month' 

DECLARE @thismonth date = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
DECLARE @lastmonth date = DATEADD(mm, -1, @thismonth)
EXECUTE dbo.InsertData @lastmonth, @thismonth, N'PrevCalMonth', N'MTD'