Applying different time period Groupings to a set of data

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 periodsCREATE 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) TPEndFROM (    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 resultsINSERT INTO WH.dbo.tb_myTableSELECT 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 ScorePerTickFROM #TempTimePeriods TimePeriods--for periods with no data use left outer join to return 0-value results, otherwise inner joinLEFT OUTER JOIN #LimitedBetInfo x ON x.DateKey >= [TimePeriods].TPBeginAND (    [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_myTableSELECT 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 ScorePerTickFROM [TimePeriods]--for periods with no data use left outer join to return 0-value results, otherwise inner joinLEFT OUTER JOIN #LimitedBetInfo xON x.DateKey >= [TimePeriods].TPBeginAND (     [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 definitionsDECLARE @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 ScorePerTickFROM (    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 joinLEFT OUTER JOIN #LimitedBetInfo xON x.DateKey >= [TimePeriods].TPBeginAND (     [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)ASBEGINSET 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 ScorePerTickFROM (    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)) TimePeriodsLEFT OUTER JOIN #LimitedBetInfo xON x.DateKey BETWEEN TimePeriods.TPBegin AND TimePeriods.TPEndGROUP BY TimePeriods.TimePeriod, x.NameEND

Which could be run as:

--This declare is just an example, it could be instead a parameter passed from an applicationDECLARE @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_myTableEXEC dbo.GetTimePeriodAggregates @TimePeriodsXmlString=@ThisExecutionsXmlString

You can create this stored procedure

CREATE PROCEDURE InsertData    @minLimit date,    @maxLimit date,    @minTerm nvarchar(50),    @maxTerm nvarchar(50)ASBEGIN    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.NameENDGO

And use like this

TRUNCATE TABLE tb_myTableDECLARE @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' 

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_myTableSELECT TimePeriod, Game, Score, Ticks, ScorePerTicksINTO WH.dbo.tb_myTableFROM (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