Tried to pivot... but it executes faster when it is hardcoded

  •  

    Users want a value spread across multiple columns... approximately 110 columns by month.  So if you look at the code below, you will see a start date and an end date and four value columns.  My users want me to create a four columns for each month of the year spanning from 2017,01,01 to 2025,12,31 and then put the numbers found in each of those four columns if the month in question is between the start and end month.  The sample has three rows... but we are looking at thousands of rows.  I tried to do it with a SQL pivot and it worked as I did one pivot, but got unusable when I did four pivots.  I ended up hard coding it, but there has to be a better way.

    drop table if exists #t

    create table #t (id int

    ,name nvarchar(250)

    ,startdate date

    ,enddate date

    ,monthlybudget decimal(18,2)

    ,monthlyincome decimal(18,2)

    ,monthlyexpense decimal(18,2)

    ,monthlydiff decimal(18,2)

    )

    insert into #t (id, name, startdate, enddate, monthlybudget, monthlyincome, monthlyexpense, monthlydiff)

    values (1, 'Store ABC', datefromparts(2017,01,01), datefromparts(2017,08,01), 5000, 1000, 3500, 500)

    ,(2, 'Store DEF', datefromparts(2017,05,01), datefromparts(2018,03,01), 200000, 180000, 19000, 1000)

    ,(3, 'Store GHI', datefromparts(2022,01,01), datefromparts(2022,09,01), 9000, 1000, 8000, 7000)

    select t.*

    ,case when datefromparts(2025,06,01) between startdate and enddate then monthlybudget Else 0 End [2025-06-01 monthlybudget] (repeat for every possible date)

    ,case when datefromparts(2025,06,01) between startdate and enddate then monthlyincome Else 0 End [2025-06-01 monthlyincome] (repeat for every possible date)

    ,case when datefromparts(2025,06,01) between startdate and enddate then monthlyexpense Else 0 End [2025-06-01 monthlyexpense] (repeat for every possible date)

    ,case when datefromparts(2025,06,01) between startdate and enddate then monthlydiff Else 0 End [2025-06-01 monthlydiff] (repeat for every possible date)

    from #t t

    I can't use a dynamic pivot because this has to be in a view... because the calling program can't call a stored procedure.

  • For the final result, do you need the format you've portrayed for the monthly column names or can we economize and clean those up a bit.  For example, you have the following as a column name...

    2025-06-01 monthlybudget

    Since we know all of these columns are "monthly" in nature, can we clean them up to be...

    2025-06-01 Budget

    ... or even ...

    Jun-2025 Budget

    ... or will doing such a thing break the application and we need to use the exact format you have provided in your coded sample?

    And, yes, I'm asking these questions because there is an easy way to do this and auto-magically generate the code for a view.  I'll demonstrate it but just looking for particulars so I can do it right the first time. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The column names in the examples were just there to protect the innocent... I'm open to new names, I can rename them as needed I would suppose.

  • That seems a very odd requirement. Are you sure your users really know what they want?

  •  

    Jonathan AC Roberts wrote:

    That seems a very odd requirement. Are you sure your users really know what they want?

    Probably not... but they want what they want.  They have already ignored the fact that this will essentially add 108 columns to their view and the fact that it is hardcoded (luckily out to month/year where I probably won't be supporting it anymore) and the fact that the numbers won't add up.

  • Something along these lines should let you generate the code:

    DECLARE @sql nvarchar(max)

    ;WITH
    cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cte_tally10 c1
    CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
    )
    SELECT @sql = STUFF(CAST((SELECT
    N',~~ SUM(CASE WHEN ''' + CONVERT(nvarchar(max), DATEADD(MONTH, t.number, '20170101'), 112) +
    ''' BETWEEN startdate AND enddate THEN monthlybudget ELSE 0 END) AS [' +
    CONVERT(nvarchar(10), DATEADD(MONTH, t.number, '20170101'), 120) + ' monthlybudget]'
    FROM cte_tally1000 t
    WHERE t.number BETWEEN 0 AND DATEDIFF(MONTH, '20170101', '20251231')
    ORDER BY t.number
    FOR XML PATH('')
    ) AS nvarchar(max)), 1, 1, '')
    SET @sql = N'CREATE VIEW dbo.your_view_name~~AS~~SELECT ' + @sql +
    '~~FROM dbo.your_table_name ';
    SET @sql = REPLACE(@sql, N'~~', NCHAR(13) + NCHAR(10))

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    --SELECT @sql
    EXEC(@sql)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Ah... I see what you did there.  Using the dynamic to create a static sql view.

Viewing 7 posts - 1 through 6 (of 6 total)

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