Complex rolling scenario (CROSS APPLY and OUTER APPLY example)

whytheq

I currently have data like the following (but bigger!)

/*--:::::::::::
DROP TABLE #target
DROP TABLE #Fact
*/--:::::::::::
CREATE TABLE #target 
    (
    PlayerKey INT,
    Name            VARCHAR(8),
    LiveKey     INT
    );
INSERT INTO #target 
    values
    (1,'michael',20130103),
    (2,'jackson',20130107);

CREATE TABLE #Fact 
    (
    DateKey     INT,
    PlayerKey INT,
    Amount      INT
    );
INSERT INTO #Fact 
    values
    (20130101,1,10),
    (20130102,1,90),
    (20130103,1,18),
    (20130103,2,79),
    (20130103,3,99),
    (20130104,2,15),
    (20130105,1,12),
    (20130105,2,15),
    (20130106,1,60),
    (20130107,1,96),
    (20130107,2,88),
    (20130107,4,28),
    (20130108,1,13),
    (20130108,2,15),
    (20130109,1,33),
    (20130109,2,67),
    (20130110,1,19),
    (20130110,2,17)
    ;

The start of the query is as follows.

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
        SELECT  rn = ROW_NUMBER() OVER(PARTITION BY d.Name ORDER BY f.DateKey),
                f.DateKey,
                d.Name,
                f.Amount
        FROM    #Fact f
                INNER JOIN #target d ON
                  f.PlayerKey = d.PlayerKey AND
                  f.DateKey >= d.LiveKey AND
                  f.DateKey < CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),d.LiveKey,112))+@NumDays),112)
        )
SELECT  x.*,
        "RollingAmount" = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte x;

This gives the following:

enter image description here

Assuming that we have a DimDate production view available how do I ensure that michael has a row for 20130104 with an amount of 0?

Also is it possible in the same script, to add new columns "AmountAll" and "AmountAllRolling" which would give numbers across all the players including PlayerKeys 3 and 4? I'm guessing this would involve changing the INNER JOIN to a LEFT OUTER JOIN?

So given the above the final result would be as follows:

enter image description here


EDIT

via all the excellent help from Bogdan I've got the following.
I've added an extra total AmountGroup that is the total across the specified players - this was just "nice-to-have" and not part of the original specification.

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
        SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY x.DateKey),
                x.DateKey,
                d.Name,
                Amount      = ISNULL(f.Amount,0),
                AmountGroup = ISNULL(f.AmountGroup,0),
                AmountAll   = ISNULL(f.AmountAll,0)
        FROM    (
                SELECT  t.*, 
                EndLiveKey = CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112))
                FROM    #target t
                ) d 
                CROSS APPLY
                    (
                SELECT  dm.DateKey
                FROM    WHData.dbo.vw_DimDate dm
                WHERE   dm.DateKey >= d.LiveKey AND
                        dm.DateKey < d.EndLiveKey           
                ) x
                OUTER APPLY
                (
                SELECT  Amount  = SUM(CASE WHEN PlayerKey1 = PlayerKey2 THEN fbase.Amount END),
                        AmountGroup = SUM(CASE WHEN inGroup = 1 THEN fbase.Amount ELSE 0 END),
                        AmountAll   = SUM(fbase.Amount)
                FROM
                    (
                    SELECT  fct.Amount, 
                            fct.PlayerKey AS PlayerKey1, 
                        d.PlayerKey AS PlayerKey2,
                        CASE WHEN tt.PlayerKey IS NULL THEN 0 ELSE 1 END AS inGroup
                    FROM    #Fact fct 
                        LEFT OUTER JOIN #target tt ON
                        fct.PlayerKey = tt.PlayerKey 
                    WHERE   fct.DateKey = x.DateKey
                ) fbase
            ) f
        )
SELECT   y.*,
        "RollingAmount"     = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountGroup"  = SUM(AmountGroup) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountAll"  = SUM(AmountAll) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;
Bogdan Sahlean

I assume that you have a DimDate table with the following structure:

CREATE TABLE DimDate
(
DateKey INT PRIMARY KEY
);

and DateKey column doesn't has gaps.

Solution:

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
            SELECT  x.DateKey,
                    d.Name,
                    Amount = ISNULL(f.Amount,0)
            FROM    
            (
                SELECT  t.*, CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112)) AS EndLiveKey
                FROM    #target t
            ) d 
            CROSS APPLY
            (
                SELECT  dm.DateKey
                FROM    DimDate dm
                WHERE   dm.DateKey >= d.LiveKey 
                AND     dm.DateKey < d.EndLiveKey           
            ) x
            LEFT OUTER JOIN #Fact f 
            ON f.PlayerKey = d.PlayerKey 
            AND f.DateKey = x.DateKey
        )
SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DateKey),
        y.*,
        "RollingAmount" = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;

Edit #1:

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
            SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY x.DateKey),
                    x.DateKey,
                    d.Name,
                    Amount      = ISNULL(f.Amount,0),
                    AmountAll   = ISNULL(fall.AmountAll,0)
            FROM    
            (
                SELECT  t.*, CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112)) AS EndLiveKey
                FROM    #target t
            ) d 
            CROSS APPLY
            (
                SELECT  dm.DateKey
                FROM    DimDate dm
                WHERE   dm.DateKey >= d.LiveKey 
                AND     dm.DateKey < d.EndLiveKey           
            ) x
            OUTER APPLY
            (
                SELECT  SUM(fct.Amount) AS Amount
                FROM    #Fact fct 
                WHERE   fct.DateKey = x.DateKey
                AND     fct.PlayerKey = d.PlayerKey
            ) f
            OUTER APPLY
            (
                SELECT  SUM(fct.Amount) AS AmountAll 
                FROM    #Fact fct 
                WHERE   fct.DateKey = x.DateKey
            ) fall
        )
SELECT  
        y.*,
        "RollingAmount"     = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountAll"  = SUM(AmountAll) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;

Edit #2:

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
            SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY x.DateKey),
                    x.DateKey,
                    d.Name,
                    Amount      = ISNULL(f.Amount,0),
                    AmountAll   = ISNULL(f.AmountAll,0)
            FROM    
            (
                SELECT  t.*, EndLiveKey = CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112))
                FROM    #target t
            ) d 
            CROSS APPLY
            (
                SELECT  dm.DateKey
                FROM    DimDate dm
                WHERE   dm.DateKey >= d.LiveKey 
                AND     dm.DateKey < d.EndLiveKey           
            ) x
            OUTER APPLY
            (
                SELECT  AmountAll   = SUM(fbase.Amount),
                        Amount      = SUM(CASE WHEN PlayerKey1 = PlayerKey2 THEN fbase.Amount END)
                FROM
                (
                    SELECT  fct.Amount, fct.PlayerKey AS PlayerKey1, d.PlayerKey AS PlayerKey2
                    FROM    #Fact fct 
                    WHERE   fct.DateKey = x.DateKey
                ) fbase
            ) f
        )
SELECT  
        y.*,
        "RollingAmount"     = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountAll"  = SUM(AmountAll) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related