Looking for tips on Optimizing

  • I am probably going about this the wrong way, but my current solution is by using dynamic SQL and a loop.

    Table creation:

    DECLARE @dataTable TABLE (Item VARCHAR(255), Quantity INT, WeekStart DATE)
    DECLARE @headerTable TABLE (Item CHAR(4), Date1 VARCHAR(25), Date2 VARCHAR(25), Date3 VARCHAR(25))

    HeaderTable actually has columns Date1 through Date52.  I excluded those for sake of simplicity.

    These tables are used in an SSRS report.  The header table is used for the first row of data (ie the header).  In the headerTable, Date1 is the most recent Monday.  Date2 is the following Monday and so on.  So to populate the header table with sample data, the following query would do it:

    INSERT INTO @headerTable
    VALUES ('Item', '18/01/2021', '25/01/2021', '01/02/2021')

    The header table will only ever contain 1 row.  This table is auto-populated by stored procedure that truncates the table and populates it on a weekly basis.  The first column is static and will ALWAYS contain the text 'Item'.  Only the date vales are changing.  I am using static values above to make the question easier to reproduce.

    The problem comes in that my FINAL table is going to be a UNION of the header table along with a calculated table that is essentially pivoting the data table based on the values in the header table.  The data table does NOT have 52 rows per item.  It can have anywhere between 1 and 52.  For this example though, we have 3 acceptable values for the WeekStart column which are in the header table.  The data table MAY contain values larger than the last date (date52 with actual data, date3 with the sample data), but will never contain dates that fall on a Tuesday through Sunday; only Monday.  Dates greater than 52 need to be ignored and not included.

    Sample data for the data table:

    INSERT INTO @dataTable
    SELECT 'WidgetA' AS Item,
    10 AS Quantity,
    CAST('18/01/2021' AS DATE) AS WeekStart
    UNION
    SELECT 'WidgetA' AS Item,
    10 AS Quantity,
    CAST('01/02/2021' AS DATE) AS WeekStart
    UNION
    SELECT 'WidgetB' AS Item,
    10 AS Quantity,
    CAST('18/01/2021' AS DATE) AS WeekStart

    The output I need is in this format:

    SELECT Item, DAte1, Date2, Date3
    FROM @headerTable
    UNION
    SELECT 'WidgetA' AS Item, CAST(10 AS VARCHAR(25)) AS Date1, CAST(0 AS VARCHAR(25)) AS Date2, CAST(10 AS VARCHAR(25)) AS Date3
    UNION
    SELECT 'WidgetB' AS Item, CAST(10 AS VARCHAR(25)) as Date1, CAST(0 AS VARCHAR(25)) AS Date2, CAST(0 AS VARCHAR(25)) AS Date3

    We do need to cast the Date1 through Date3 to VARCHAR as the headers are in VARCHAR format.  Without that, the UNION fails.

    My current method is dynamic SQL to update DateX where X is a counter in a while loop by comparing WeekStart to DateX in the header table where X is that counter again.

    I would prefer to do this without the loop, but I can't think of a good way to do so.  The loop is efficient as there are under 2000 distinct Items at the moment and there should never, and I don't expect that getting much bigger if at all.  I just think there should be an easier way to do this without looping.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Yeah, this can be definitely done in a standard SELECT.

    Do you want to show only the weeks that had a match or do you want to show all 52 weeks, with, say, NULL for any Item that didn't have data for that week?

    Showing a variable number of weeks is trickier, since you'd have to determine matched dates across all items in order to to the final output.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • The HEADER table will always have a value for each week  There is never a case where the header table will have a NULL for a week.

    In the data table, there MAY not be a match for that week (see the sample data).  If there is no match, then a 0 MUST be in place of the NULL.

    The final table (using my method with a loop and dynamic SQL) gives me exactly 1610 rows in the final table.  With the loop logic is it pretty easy to do, but is not how SQL likes to work and makes it more difficult to tune the execution plan view has 52 entries for populating that final data table.  First being an insert the rest being updates.

    The loop + dynamic SQL works, I just don't like that method.  I hate using loops in SQL and hate using dynamic SQL if there is a better method.  But in this case, I just couldn't think of anything.  Especially since on Monday, that header table is going to change Date1 to Date2, Date2 to Date3 and Date3 to DateAdd(day,7,Date3) (not exactly how the logic works behind that stored procedure, but that is the general effect, using the sample data that I did above.  Real data would do all the way up to 52 and 52 would get the DATEADD on it).

  • Maybe something like this?:


    SELECT Item, Date1, Date2, Date3
    FROM @headerTable
    UNION ALL
    SELECT
    Item,
    CAST(MAX(CASE WHEN week# = 1 THEN Quantity END) AS varchar(10)),
    CAST(MAX(CASE WHEN week# = 2 THEN Quantity END) AS varchar(10)),
    CAST(MAX(CASE WHEN week# = 3 THEN Quantity END) AS varchar(10))
    FROM (
    SELECT
    dt.Item, SUM(dt.Quantity) AS Quantity, dt.WeekStart,
    DATEDIFF(DAY, MAX(ht.Date1), dt.WeekStart) / 7 + 1 AS week#
    FROM @dataTable dt
    CROSS APPLY (
    SELECT TOP (1) ht.Date1, ht.Date3 /*Date52*/ AS Date3 /*Date52*/
    FROM @headerTable ht
    ) AS ht
    WHERE dt.WeekStart >= ht.Date1 AND
    dt.WeekStart <= ht.Date3 /*Date52*/
    GROUP BY
    dt.Item, dt.WeekStart
    ) AS dt
    GROUP BY Item

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • yep!  That looks like it should work and looks more efficient than my loop approach!  And easier to tune as it isn't 52 loops!

    That one looks much nicer than my approach.  Thanks Scott!

  • You're welcome!  I'm glad it helped.  Interesting q to play with.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • A general solution with PIVOT.

    USE master;
    GO
    DROP DATABASE IF EXISTS TestDB;
    GO
    CREATE DATABASE TestDB;
    GO
    USE TestDB;

    CREATE TABLE dbo.Datatable
    (
    Item VARCHAR(255),
    Quantity INT,
    WeekStart DATE
    );
    GO
    INSERT INTO dbo.Datatable VALUES
    ('WidgetX', 10, '2019-1-18'), -- before

    ('WidgetA', 10, '2021-1-25'),
    ('WidgetA', 11, '2021-2-1'),
    ('WidgetB', 12, '2021-1-25'),

    ('WidgetC', 22, '2021-2-1'),
    ('WidgetC', 23, '2021-12-6'),

    ('WidgetY', 10, '2022-2-18'); -- after
    GO
    -- Many data
    INSERT INTO dbo.Datatable
    SELECT Item,
    Quantity,
    DATEADD(WEEK, 1, WeekStart)
    FROM dbo.Datatable
    GO 10
    INSERT INTO dbo.Datatable
    SELECT CONCAT(Item, RIGHT(Item, 1)),
    Quantity,
    DATEADD(WEEK, 1, WeekStart)
    FROM dbo.Datatable
    GO 5
    SELECT *
    FROM dbo.Datatable
    ORDER BY Item, WeekStart;
    GO
    SET DATEFIRST 1; -- monday

    DECLARE @Date DATE = SYSDATETIME();
    DECLARE @Sql NVARCHAR(MAX);

    WITH AllWeeks (WeekID, Monday)
    AS
    (
    SELECT 1 AS WeekID,
    DATEADD(DAY, - DATEPART (WEEKDAY, @Date) + 1, @Date) AS Monday
    UNION ALL
    SELECT WeekID + 1 AS WeekID,
    DATEADD(DAY, 7, Monday) AS Monday
    FROM AllWeeks
    WHERE WeekID < 52
    ),
    Projektion
    AS
    (
    SELECT STRING_AGG (CONCAT('[', Monday, ']'), ', ') WITHIN GROUP (ORDER BY WeekID) AS Columns
    FROM AllWeeks
    ),
    RowConstructor
    AS
    (
    SELECT STRING_AGG (CONCAT('(''', Monday, ''')'), ', ') WITHIN GROUP (ORDER BY WeekID) AS Columns
    FROM AllWeeks
    )
    SELECT @Sql = CONCAT( 'SELECT Item, ',
    (SELECT Columns FROM Projektion),
    ' FROM (SELECT Item, Quantity, WeekStart FROM dbo.Datatable INNER JOIN
    (VALUES', (SELECT Columns FROM RowConstructor),
    ') AS W (Monday) ON Datatable.WeekStart = W.Monday ) AS Data',
    ' PIVOT (SUM(Quantity) FOR WeekStart IN (',
    (SELECT Columns FROM Projektion),
    ')) AS pvt;');

    -- PRINT @Sql;

    EXEC sp_executesql @Sql;

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

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