Sum Pivot by Dynamic Quarter

  • girl_bj

    SSCommitted

    Points: 1724

    i have a report to create a prediction for 12 months (yearly prediction by month). example when will be the shipment date is base on input from user. the report will run by month. for example this month is July, shipment will only show July onward. i manage to create the dynamic pivot. how can i include the by quarter in between? i am not sure how to count the quarter.

    create table #temp
    (
    country varchar(20),
    ship varchar(20),
    amount money
    )

    insert into #temp values ('German', '2020-07', 1000.00)
    insert into #temp values ('USA', '2020-07', 500.00)
    insert into #temp values ('AUS', '2020-09', 800.00)
    insert into #temp values ('AUS', '2020-08', 700.00)
    insert into #temp values ('German', '2020-12', 1100.00)
    insert into #temp values ('UK', '2021-03', 1100.00)
    insert into #temp values ('Africa', '2021-02', 1100.00)

    DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)
    ,@cols2 AS NVARCHAR(MAX)

    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(ship)
    FROM #temp c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    set @cols2 =STUFF(( select distinct ',IsNull(['+ship+'],0) as ['+ship+']' from #temp
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    set @query = 'SELECT country, ' + @cols2 + ' from
    (
    select country
    , amount
    , ship
    from #temp
    ) x
    pivot
    (
    max(amount)
    for ship in (' + @cols + ')
    ) p '

    execute(@query)

    I would like to include in between by Quarter 1,2,3,4. Is this possible? Currently I am doing it manually in excel.

    Capture

  • Mr. Brian Gale

    SSC-Insane

    Points: 23053

    This query is close.  The only thing it isn't doing QUITE right is the ordering for the Quarters:

    USE [Admin];
    GO
    CREATE TABLE [#temp]
    (
    [country] VARCHAR(20)
    , [ship] VARCHAR(20)
    , [amount] MONEY
    );

    INSERT INTO [#temp]
    VALUES
    (
    'German'
    , '2020-07'
    , 1000.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'USA'
    , '2020-07'
    , 500.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'AUS'
    , '2020-09'
    , 800.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'AUS'
    , '2020-08'
    , 700.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'German'
    , '2020-12'
    , 1100.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'UK'
    , '2021-03'
    , 1100.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'Africa'
    , '2021-02'
    , 1100.00
    );

    INSERT INTO [#temp]
    (
    [country]
    , [ship]
    , [amount]
    )
    SELECT DISTINCT
    [country]
    , LEFT([ship], 5) + 'Quarter' + CAST(DATEPART( QUARTER
    , CAST([ship] + '-01' AS DATE)
    ) AS VARCHAR(255))
    , SUM([amount]) OVER (PARTITION BY
    [country]
    , DATEPART( QUARTER
    , CAST([ship] + '-01' AS DATE)
    )
    )
    FROM [#temp];

    DECLARE
    @cols AS NVARCHAR(MAX)
    , @query AS NVARCHAR(MAX)
    , @cols2 AS NVARCHAR(MAX);

    SET @cols = STUFF( (
    SELECT DISTINCT
    ',' + QUOTENAME([c].[ship])
    FROM [#temp] AS [c]
    FOR XML PATH(''), TYPE
    ).[value]( '.'
    , 'NVARCHAR(MAX)'
    )
    , 1
    , 1
    , ''
    );
    SET @cols2 = STUFF( (
    SELECT DISTINCT
    ',IsNull([' + [ship] + '],0) as [' + [ship] + ']'
    FROM [#temp]
    FOR XML PATH(''), TYPE
    ).[value]( '.'
    , 'NVARCHAR(MAX)'
    )
    , 1
    , 1
    , ''
    );

    SET @query = N'SELECT country, ' + @cols2 + N' from
    (
    select country
    , amount
    , ship
    from #temp
    ) x
    pivot
    (
    max(amount)
    for ship in (' + @cols + N')
    ) p ';

    EXECUTE (@query);

    DROP TABLE [#temp];

    Is that close enough or do you need the ordering of the quarters to be correct?

    EDIT REASON - ran SQL Prompt SQL Format on the code to make it easier to read.

    EDIT 2: Query which has the order you are looking for:

    USE [Admin];
    GO
    CREATE TABLE [#temp]
    (
    [country] VARCHAR(20)
    , [ship] VARCHAR(20)
    , [amount] MONEY
    );

    INSERT INTO [#temp]
    VALUES
    (
    'German'
    , '2020-07'
    , 1000.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'USA'
    , '2020-07'
    , 500.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'AUS'
    , '2020-09'
    , 800.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'AUS'
    , '2020-08'
    , 700.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'German'
    , '2020-12'
    , 1100.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'UK'
    , '2021-03'
    , 1100.00
    );
    INSERT INTO [#temp]
    VALUES
    (
    'Africa'
    , '2021-02'
    , 1100.00
    );

    DECLARE @minQuarter SMALLINT;
    SELECT
    @minQuarter = DATEPART( QUARTER
    , MIN(CAST([ship] + '-01' AS DATE))
    )
    FROM [#temp];

    INSERT INTO [#temp]
    (
    [country]
    , [ship]
    , [amount]
    )
    SELECT DISTINCT
    [country]
    , LEFT([ship], 5) + 'Quarter' + CAST(DATEPART( QUARTER
    , CAST([ship] + '-01' AS DATE)
    ) AS VARCHAR(255))
    , SUM([amount]) OVER (PARTITION BY
    [country]
    , DATEPART( QUARTER
    , CAST([ship] + '-01' AS DATE)
    )
    )
    FROM [#temp];

    DECLARE
    @cols AS NVARCHAR(MAX) = N''
    , @query AS NVARCHAR(MAX)
    , @cols2 AS NVARCHAR(MAX) = N'';



    DECLARE @iteration INT = 0;
    WHILE @iteration < 4
    BEGIN

    IF (LEN(@cols) = 0)
    BEGIN
    SET @cols = ISNULL( STUFF( (
    SELECT DISTINCT
    ',' + QUOTENAME([c].[ship])
    FROM [#temp] AS [c]
    WHERE [c].[ship] NOT LIKE '%Quarter%'
    AND DATEPART( QUARTER
    , CAST([c].[ship] + '-01' AS DATE)
    ) = @minQuarter
    FOR XML PATH(''), TYPE
    ).[value]( '.'
    , 'NVARCHAR(MAX)'
    )
    , 1
    , 1
    , ''
    )
    , ''
    );
    SET @cols2 = ISNULL( STUFF( (
    SELECT DISTINCT
    ',IsNull([' + [ship] + '],0) as [' + [ship] + ']'
    FROM [#temp]
    WHERE [ship] NOT LIKE '%Quarter%'
    AND DATEPART( QUARTER
    , CAST([ship] + '-01' AS DATE)
    ) = @minQuarter
    FOR XML PATH(''), TYPE
    ).[value]( '.'
    , 'NVARCHAR(MAX)'
    )
    , 1
    , 1
    , ''
    )
    , ''
    );
    END;
    ELSE
    BEGIN
    SET @cols = @cols + N',' + ISNULL( STUFF( (
    SELECT DISTINCT
    ',' + QUOTENAME([c].[ship])
    FROM [#temp] AS [c]
    WHERE [c].[ship] NOT LIKE '%Quarter%'
    AND DATEPART( QUARTER
    , CAST([c].[ship] + '-01' AS DATE)
    ) = @minQuarter
    FOR XML PATH(''), TYPE
    ).[value]( '.'
    , 'NVARCHAR(MAX)'
    )
    , 1
    , 1
    , ''
    )
    , ''
    );
    SET @cols2 = @cols2 + N',' + ISNULL( STUFF( (
    SELECT DISTINCT
    ',IsNull([' + [ship] + '],0) as [' + [ship] + ']'
    FROM [#temp]
    WHERE [ship] NOT LIKE '%Quarter%'
    AND DATEPART( QUARTER
    , CAST([ship] + '-01' AS DATE)
    ) = @minQuarter
    FOR XML PATH(''), TYPE
    ).[value]( '.'
    , 'NVARCHAR(MAX)'
    )
    , 1
    , 1
    , ''
    )
    , ''
    );
    END;
    SELECT TOP (1)
    @cols = @cols + N',' + ISNULL( QUOTENAME([ship])
    , ''
    )
    FROM [#temp]
    WHERE [ship] LIKE ('%Quarter' + CAST(@minQuarter AS VARCHAR(25)));
    SELECT TOP (1)
    @cols2 = @cols2 + ISNULL( ',ISNULL([' + [ship] + '],0) AS [' + [ship] + ']'
    , ''
    )
    FROM [#temp]
    WHERE [ship] LIKE ('%Quarter' + CAST(@minQuarter AS VARCHAR(25)));

    SET @iteration = @iteration + 1;
    SET @minQuarter = @minQuarter + 1;
    IF @minQuarter = 5
    BEGIN
    SET @minQuarter = 1;
    END;
    PRINT @iteration;
    PRINT @cols;
    PRINT @cols2;
    END;
    IF (RIGHT(@cols, 1) = ',')
    BEGIN
    SET @cols = SUBSTRING( @cols
    , 0
    , LEN(@cols)
    );
    SET @cols2 = SUBSTRING( @cols2
    , 0
    , LEN(@cols2)
    );
    END;
    SET @query = N'SELECT country, ' + @cols2 + N' from
    (
    select country
    , amount
    , ship
    from #temp
    ) x
    pivot
    (
    max(amount)
    for ship in (' + @cols + N')
    ) p ';

    EXECUTE (@query);

    DROP TABLE [#temp];

    Now the only thing it doesn't do (but your expected output doesn't either) is show you quarters where the value is 0 (in this case quarter 2).  Plus it only handles 4 quarters properly.  If the data extends further than 4 quarters, then you will have problems.

  • girl_bj

    SSCommitted

    Points: 1724

    Hi, thanks Brian. Edit 1 is the one im looking for to sum by quarter. But the ordering are not in order. How can I order it without applying Order by with case condition?

  • Mr. Brian Gale

    SSC-Insane

    Points: 23053

    Can you provide some sample data where it is not ordered correctly?  All of my testing on my system indicates that it should  be ordered correctly EXCEPT if you go past 4 quarters.  The script relies HEAVILY on the time frame being 4 quarters or less.  If it exceeds 4 quarters, then it will fail.  If you need it to span more than 4 quarters, my approach gets much more messy and there are likely better approaches in that case.

    Once you go past 4 quarters you get the problem of 2020-Quarter1 showing totals from 2021-Quarter1 as well.  Your sample data looked like it was only for a 1 year period and thus should be fine.

    The other use case where you may hit snags is if you use single-digit months anywhere as you are storing it as a string.  So 2020-1 is going to be greater than 2020-02 and thus will show up AFTER.  ie the ordering will be 2020-02 then 2020-1.  And if you use a mix of "01" and "1" for the month, they will both show up.

    If you need to span more than 4 quarters (or you have some sample data that breaks the ordering), let me know and I can try to figure out how to fix it.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply