Using LAG to return prior non null value

  • Looks like I can wrap around the code as a subquery and apply the criteria to the outer query

    --Microsoft SQL Server 2019 
    --Build a temp calendar table
    DROP TABLE IF EXISTS #Calendar
    CREATE TABLE #Calendar (CalendarDate DATE)

    DECLARE @Start DATE = '2022-07-01'
    DECLARE @End DATE = '2022-07-31'

    WHILE ( @Start < @End )
    BEGIN
    INSERT INTO #Calendar (CalendarDate) VALUES( @Start )
    SELECT @Start = DATEADD(DAY, 1, @Start )
    END

    DROP TABLE IF EXISTS #MyData

    SELECT *
    INTO #MyData
    FROM
    (
    SELECT '2022-07-01' AS ReportDate,100AS ReportValue UNION ALL
    SELECT '2022-07-05' AS ReportDate,101AS ReportValue UNION ALL
    SELECT '2022-07-06' AS ReportDate,102AS ReportValue UNION ALL
    SELECT '2022-07-07' AS ReportDate,103AS ReportValue UNION ALL
    SELECT '2022-07-08' AS ReportDate,104AS ReportValue UNION ALL
    SELECT '2022-07-11' AS ReportDate,105AS ReportValue UNION ALL
    SELECT '2022-07-12' AS ReportDate,106AS ReportValue UNION ALL
    SELECT '2022-07-13' AS ReportDate,107AS ReportValue UNION ALL
    SELECT '2022-07-14' AS ReportDate,108AS ReportValue UNION ALL
    SELECT '2022-07-15' AS ReportDate,109AS ReportValue UNION ALL
    SELECT '2022-07-18' AS ReportDate,110AS ReportValue UNION ALL
    SELECT '2022-07-19' AS ReportDate,111AS ReportValue UNION ALL
    SELECT '2022-07-20' AS ReportDate,112AS ReportValue UNION ALL
    SELECT '2022-07-21' AS ReportDate,113AS ReportValue UNION ALL
    SELECT '2022-07-22' AS ReportDate,114AS ReportValue UNION ALL
    SELECT '2022-07-25' AS ReportDate,115AS ReportValue UNION ALL
    SELECT '2022-07-26' AS ReportDate,116AS ReportValue UNION ALL
    SELECT '2022-07-27' AS ReportDate,117AS ReportValue UNION ALL
    SELECT '2022-07-28' AS ReportDate,118AS ReportValue UNION ALL
    SELECT '2022-07-29' AS ReportDate,119AS ReportValue

    ) D

    SELECT C.CalendarDate
    , D.ReportDate
    , D.ReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    FROM #Calendar C
    LEFT JOIN #MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)
    --WHERE CalendarDate = '2022-07-04'
    ORDER BY C.CalendarDate

    --This is more like the view I am tring to build
    SELECT
    *
    FROM #Calendar C
    --Multiple other left joins here
    LEFT JOIN
    (
    SELECT C.CalendarDate
    , D.ReportDate
    , D.ReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    FROM #Calendar C
    LEFT JOIN #MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)
    ) L
    ON C.CalendarDate = L.LagReportDate
    WHERE C.CalendarDate = '2022-07-04'

    SELECT
    *
    FROM
    (
    SELECT
    L.CalendarDate,
    L.ReportDate,
    L.ReportValue,
    L.LagReportDate,
    L.LagReportValue
    FROM #Calendar C
    --Multiple other left joins here
    LEFT JOIN
    (
    SELECT C.CalendarDate
    , D.ReportDate
    , D.ReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    FROM #Calendar C
    LEFT JOIN #MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)
    ) L
    ON C.CalendarDate = L.LagReportDate
    ) x
    WHERE CalendarDate = '2022-07-04'
  • I am still studying the code. I anticipate I am going to have an additional requirement.

    I want to report on 2022-07-10. There is no data on 2022-07-10 so I get the most recent non -null data from 2022-07-08.

    One of the 2022-07-10 data points I want reports is a day over day calculation so really for 2022-07-10 I want to return 2022-07-08. and 2022-07-07.

    This guess did not work.

    SELECT C.CalendarDate
    , D.ReportDate
    , D.ReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING), 1, 10) AS DATE) AS AdditionalLagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING), 11, 10) AS INT) AS AdditionalLagReportValue
    FROM #Calendar C
    LEFT JOIN #MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)

     

     

     

  • Maybe you could add the LAG columns to #MyData.  Then use a combination of LAG and MAX OVER.  If this were SQL Server 2022 you could use the WINDOW clause

    DROP TABLE IF EXISTS #Calendar
    CREATE TABLE #Calendar (CalendarDate DATE)

    DECLARE @Start DATE = '2022-07-01'
    DECLARE @End DATE = '2022-07-31'

    WHILE ( @Start < @End )
    BEGIN
    INSERT INTO #Calendar (CalendarDate) VALUES( @Start )
    SELECT @Start = DATEADD(DAY, 1, @Start )
    END

    DROP TABLE IF EXISTS #MyData

    SELECT *,
    LAG(ReportDate) OVER (ORDER BY ReportDate) LagReportDate,
    LAG(ReportValue) OVER (ORDER BY ReportDate) LagReportValue
    INTO #MyData
    FROM
    (
    SELECT '2022-07-01' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT '2022-07-05' AS ReportDate,101 AS ReportValue UNION ALL
    SELECT '2022-07-06' AS ReportDate,102 AS ReportValue UNION ALL
    SELECT '2022-07-07' AS ReportDate,103 AS ReportValue UNION ALL
    SELECT '2022-07-08' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT '2022-07-11' AS ReportDate,105 AS ReportValue UNION ALL
    SELECT '2022-07-12' AS ReportDate,106 AS ReportValue UNION ALL
    SELECT '2022-07-13' AS ReportDate,107 AS ReportValue UNION ALL
    SELECT '2022-07-14' AS ReportDate,108 AS ReportValue UNION ALL
    SELECT '2022-07-15' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT '2022-07-18' AS ReportDate,110 AS ReportValue UNION ALL
    SELECT '2022-07-19' AS ReportDate,111 AS ReportValue UNION ALL
    SELECT '2022-07-20' AS ReportDate,112 AS ReportValue UNION ALL
    SELECT '2022-07-21' AS ReportDate,113 AS ReportValue UNION ALL
    SELECT '2022-07-22' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT '2022-07-25' AS ReportDate,115 AS ReportValue UNION ALL
    SELECT '2022-07-26' AS ReportDate,116 AS ReportValue UNION ALL
    SELECT '2022-07-27' AS ReportDate,117 AS ReportValue UNION ALL
    SELECT '2022-07-28' AS ReportDate,118 AS ReportValue UNION ALL
    SELECT '2022-07-29' AS ReportDate,119 AS ReportValue
    ) D;
    --select * from #MyData;

    /* MAX OVER and LAG */
    select c.CalendarDate,
    max(md.ReportDate) over (order by c.CalendarDate) mx_rd,
    max(md.ReportValue) over (order by c.CalendarDate) mx_rv,
    max(md.LagReportDate) over (order by c.CalendarDate) lag_mx_rd,
    max(md.LagReportValue) over (order by c.CalendarDate) lag_mx_rv
    from #Calendar c
    left join #MyData md on c.CalendarDate=md.ReportDate;

    /* SQL Server 2022 using WINDOW clause */
    select c.CalendarDate,
    max(md.ReportDate) over w as mx_rd,
    max(md.ReportValue) over w as mx_rv,
    max(md.LagReportDate) over w as lag_mx_rd,
    max(md.LagReportValue) over w as lag_mx_rv
    from #Calendar c
    left join #MyData md on c.CalendarDate=md.ReportDate
    window w as (order by c.calendardate);

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This was removed by the editor as SPAM

  • Hello Again,

    I have added to my DML setup to more accurately reflect my situation.

    Added a second calendar type but that doesn't see to be an issue.

    Added an ID to my report data. The gaps in the data may vary among ID s. So ID  1 has all data. ID  2 does not have weekends/holidays.

    Now I am returning incorrect lag data am missing records like 2022-07-04 for ID 2

    I thought I could use PARTITION BY ID by no success.

    Any help would be much appreciated.

    --Microsoft SQL Server 2019 
    --Build a temp calendar table
    DROP TABLE IF EXISTS #Calendar
    CREATE TABLE #Calendar (CalendarDate DATE,CalendarType INT)

    DECLARE @Start DATE = '2022-07-01'
    DECLARE @End DATE = '2022-07-31'

    WHILE ( @Start < @End )
    BEGIN
    INSERT INTO #Calendar (CalendarDate,CalendarType) VALUES( @Start ,1)
    SELECT @Start = DATEADD(DAY, 1, @Start )
    END

    INSERT INTO #Calendar (CalendarDate,CalendarType) SELECT CalendarDate, 2 AS CalendarType FROM #Calendar
    --SELECT * FROM #Calendar

    DROP TABLE IF EXISTS #MyData

    SELECT *
    INTO #MyData
    FROM
    (
    SELECT 1 AS ID, '2022-07-01' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-02' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-03' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-04' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-05' AS ReportDate,101 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-06' AS ReportDate,102 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-07' AS ReportDate,103 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-08' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-09' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-10' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-11' AS ReportDate,105 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-12' AS ReportDate,106 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-13' AS ReportDate,107 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-14' AS ReportDate,108 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-15' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-16' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-17' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-18' AS ReportDate,110 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-19' AS ReportDate,111 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-20' AS ReportDate,112 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-21' AS ReportDate,113 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-22' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-23' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-24' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-25' AS ReportDate,115 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-26' AS ReportDate,116 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-27' AS ReportDate,117 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-28' AS ReportDate,118 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-29' AS ReportDate,119 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-30' AS ReportDate,120 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-31' AS ReportDate,121 AS ReportValue UNION ALL

    SELECT 2 AS ID, '2022-07-01' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-02' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-03' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-04' AS ReportDate,200 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-05' AS ReportDate,201 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-06' AS ReportDate,202 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-07' AS ReportDate,203 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-08' AS ReportDate,204 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-09' AS ReportDate,204 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-10' AS ReportDate,204 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-11' AS ReportDate,205 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-12' AS ReportDate,206 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-13' AS ReportDate,207 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-14' AS ReportDate,208 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-15' AS ReportDate,209 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-16' AS ReportDate,209 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-17' AS ReportDate,209 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-18' AS ReportDate,210 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-19' AS ReportDate,211 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-20' AS ReportDate,212 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-21' AS ReportDate,213 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-22' AS ReportDate,214 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-23' AS ReportDate,214 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-24' AS ReportDate,214 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-25' AS ReportDate,215 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-26' AS ReportDate,216 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-27' AS ReportDate,217 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-28' AS ReportDate,218 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-29' AS ReportDate,219 AS ReportValue --UNION ALL
    --SELECT 2 AS ID, '2022-07-30' AS ReportDate,220 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-31' AS ReportDate,221 AS ReportValue

    ) D

    SELECT
    C.CalendarDate
    , CalendarType
    , D.ID
    , D.ReportDate
    , D.ReportValue
    --, CAST(SUBSTRING(MAX(v.val) OVER(PARTITION BY ID ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    ---, CAST(SUBSTRING(MAX(v.val) OVER(PARTITION BY ID ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    FROM #Calendar C
    LEFT JOIN #MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)
    WHERE
    CalendarType = 1
    ORDER BY C.CalendarDate,ID

     

     

  • If you add the LAG columns to #MyData you could try CROSS JOIN'ing the calendar rows with the distinct IDs.  Then LEFT JOIN #MyData on id and date.

    with unq_id_cte(id) as (
    select distinct id
    from #MyData)
    select ui.id, c.CalendarDate,
    max(md.ReportDate) over (partition by ui.id order by c.CalendarDate) mx_rd,
    max(md.ReportValue) over (partition by ui.id order by c.CalendarDate) mx_rv,
    max(md.LagReportDate) over (partition by ui.id order by c.CalendarDate) lag_mx_rd,
    max(md.LagReportValue) over (partition by ui.id order by c.CalendarDate) lag_mx_rv
    from unq_id_cte ui
    cross join #Calendar c
    left join #MyData md on ui.id=md.ID
    and c.CalendarDate=md.ReportDate
    order by ui.id, c.CalendarDate;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    If you add the LAG columns to #MyData you could try CROSS JOIN'ing the calendar rows with the distinct IDs.  Then LEFT JOIN #MyData on id and date.

    I can't use CTEs so I am not sure if this is viable. Can you provide more detail on what you mean when you say 'add the LAG columns to #MyData'?

  • Chrissy321 wrote:

    Can you provide more detail on what you mean when you say 'add the LAG columns to #MyData'?

    /* Add LAG function columns to the query which inserts into #MyData */    
    SELECT *,
    LAG(ReportDate) OVER (ORDER BY ReportDate) LagReportDate,
    LAG(ReportValue) OVER (ORDER BY ReportDate) LagReportValue
    INTO #MyData
    FROM
    (...) D;
    Chrissy321 wrote:

    I can't use CTEs so I am not sure if this is viable.

    /* Use subquery instead of CTE */     
    select ui.id, c.CalendarDate,
    max(md.ReportDate) over (partition by ui.id order by c.CalendarDate) mx_rd,
    max(md.ReportValue) over (partition by ui.id order by c.CalendarDate) mx_rv,
    max(md.LagReportDate) over (partition by ui.id order by c.CalendarDate) lag_mx_rd,
    max(md.LagReportValue) over (partition by ui.id order by c.CalendarDate) lag_mx_rv
    from (select distinct id
    from #MyData) ui
    cross join #Calendar c
    left join #MyData md on ui.id=md.ID
    and c.CalendarDate=md.ReportDate
    order by ui.id, c.CalendarDate;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Chrissy321 wrote:

    Steve Collins wrote:

    If you add the LAG columns to #MyData you could try CROSS JOIN'ing the calendar rows with the distinct IDs.  Then LEFT JOIN #MyData on id and date.

    I can't use CTEs so I am not sure if this is viable. Can you provide more detail on what you mean when you say 'add the LAG columns to #MyData'?

    Why can't you use CTEs?

  • [/quote]Why can't you use CTEs?[/quote]

    I want to compile a view into a third party system that does not support CTEs. If it's not possible to accomplish without CTEs I may be able to get that eliminate the restriction, so preferably no CTEs.

  • Chrissy321 wrote:

    Why can't you use CTEs?[/quote]

    I want to compile a view into a third party system that does not support CTEs. If it's not possible to accomplish without CTEs I may be able to get that eliminate the restriction, so preferably no CTEs.[/quote]

    Unless you are making multiple references to a CTE you can just put the query into a sub-query in the main query.

  • This was removed by the editor as SPAM

  • Got it but still returning incorrect data, lag value for ID2 on 2022-07-04 should be 200

    --Microsoft SQL Server 2019 
    --Build a temp calendar table
    DROP TABLE IF EXISTS #Calendar
    CREATE TABLE #Calendar (CalendarDate DATE,CalendarType INT)

    DECLARE @Start DATE = '2022-07-01'
    DECLARE @End DATE = '2022-07-31'

    WHILE ( @Start < @End )
    BEGIN
    INSERT INTO #Calendar (CalendarDate,CalendarType) VALUES( @Start ,1)
    SELECT @Start = DATEADD(DAY, 1, @Start )
    END

    INSERT INTO #Calendar (CalendarDate,CalendarType) SELECT CalendarDate, 2 AS CalendarType FROM #Calendar
    --SELECT * FROM #Calendar

    DROP TABLE IF EXISTS #MyData

    SELECT
    *,
    LAG(ReportDate) OVER (ORDER BY ReportDate) LagReportDate,
    LAG(ReportValue) OVER (ORDER BY ReportDate) LagReportValue
    INTO #MyData
    FROM
    (
    SELECT 1 AS ID, '2022-07-01' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-02' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-03' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-04' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-05' AS ReportDate,101 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-06' AS ReportDate,102 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-07' AS ReportDate,103 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-08' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-09' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-10' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-11' AS ReportDate,105 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-12' AS ReportDate,106 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-13' AS ReportDate,107 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-14' AS ReportDate,108 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-15' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-16' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-17' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-18' AS ReportDate,110 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-19' AS ReportDate,111 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-20' AS ReportDate,112 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-21' AS ReportDate,113 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-22' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-23' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-24' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-25' AS ReportDate,115 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-26' AS ReportDate,116 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-27' AS ReportDate,117 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-28' AS ReportDate,118 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-29' AS ReportDate,119 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-30' AS ReportDate,120 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-31' AS ReportDate,121 AS ReportValue UNION ALL

    SELECT 2 AS ID, '2022-07-01' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-02' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-03' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-04' AS ReportDate,200 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-05' AS ReportDate,201 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-06' AS ReportDate,202 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-07' AS ReportDate,203 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-08' AS ReportDate,204 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-09' AS ReportDate,204 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-10' AS ReportDate,204 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-11' AS ReportDate,205 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-12' AS ReportDate,206 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-13' AS ReportDate,207 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-14' AS ReportDate,208 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-15' AS ReportDate,209 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-16' AS ReportDate,209 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-17' AS ReportDate,209 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-18' AS ReportDate,210 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-19' AS ReportDate,211 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-20' AS ReportDate,212 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-21' AS ReportDate,213 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-22' AS ReportDate,214 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-23' AS ReportDate,214 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-24' AS ReportDate,214 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-25' AS ReportDate,215 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-26' AS ReportDate,216 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-27' AS ReportDate,217 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-28' AS ReportDate,218 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-29' AS ReportDate,219 AS ReportValue --UNION ALL
    --SELECT 2 AS ID, '2022-07-30' AS ReportDate,220 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-31' AS ReportDate,221 AS ReportValue

    ) D
    /*
    SELECT
    C.CalendarDate
    , CalendarType
    , D.ID
    , D.ReportDate
    , D.ReportValue
    --, CAST(SUBSTRING(MAX(v.val) OVER(PARTITION BY ID ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    ---, CAST(SUBSTRING(MAX(v.val) OVER(PARTITION BY ID ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER(ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    FROM #Calendar C
    LEFT JOIN #MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)
    WHERE
    CalendarType = 1
    ORDER BY C.CalendarDate,ID
    */

    /*
    ;WITH unq_id_cte(id) as (
    select distinct id
    from #MyData)
    */
    SELECT ui.ID, c.CalendarDate,
    MAX(md.ReportDate) OVER (PARTITION BY ui.ID ORDER BY c.CalendarDate) mx_rd,
    MAX(md.ReportValue) OVER (PARTITION BY ui.ID ORDER BY c.CalendarDate) mx_rv,
    MAX(md.LagReportDate) OVER (PARTITION BY ui.ID ORDER BY c.CalendarDate) lag_mx_rd,
    MAX(md.LagReportValue) OVER (PARTITION BY ui.ID ORDER BY c.CalendarDate) lag_mx_rv
    FROM(SELECT DISTINCT ID FROM #MyData) ui
    CROSS JOIN #Calendar c
    LEFT JOIN #MyData md ON ui.ID=md.ID AND c.CalendarDate=md.ReportDate
    WHERE
    CalendarType = 1
    ORDER BY c.CalendarDate,ui.ID
  • Also add PARTITION BY ID to the SELECT INTO

    SELECT *, 
    LAG(ReportDate) OVER (partition by id ORDER BY ReportDate) LagReportDate,
    LAG(ReportValue) OVER (partition by id ORDER BY ReportDate) LagReportValue
    INTO #MyData
    FROM ...

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Adding PARTITION BY ID to the SELECT INTO works with data I provided. In my real data I am getting the first value rather than the previous. Larger issue is the query is taking 3+ minutes to return. I am going to look at fixing the data issue, maybe that will also fix the performance issue but I suspect this method will be a no go based on performance. Appreciate you efforts here.

    I'll restate the issue with the below code since it seems to be the closet. I am using permanent tables and a view since that most closely mirrors my situation.

    This near solution below with my real data is quite performant. I'm hoping it can be altered to return my hoped for results. Thanks again to all of you.

    --Microsoft SQL Server 2019 
    --Build a temp calendar table
    DROP TABLE IF EXISTS Calendar
    CREATE TABLE Calendar (CalendarDate DATE,CalendarType INT)

    DECLARE @Start DATE = '2022-07-01'
    DECLARE @End DATE = '2022-07-31'

    WHILE ( @Start < @End )
    BEGIN
    INSERT INTO Calendar (CalendarDate,CalendarType) VALUES( @Start ,1)
    SELECT @Start = DATEADD(DAY, 1, @Start )
    END

    INSERT INTO Calendar (CalendarDate,CalendarType) SELECT CalendarDate, 2 AS CalendarType FROM Calendar
    --SELECT * FROM Calendar

    DROP TABLE IF EXISTS MyData

    SELECT
    *
    INTO MyData
    FROM
    (
    SELECT 1 AS ID, '2022-07-01' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-02' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-03' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-04' AS ReportDate,100 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-05' AS ReportDate,101 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-06' AS ReportDate,102 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-07' AS ReportDate,103 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-08' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-09' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-10' AS ReportDate,104 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-11' AS ReportDate,105 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-12' AS ReportDate,106 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-13' AS ReportDate,107 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-14' AS ReportDate,108 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-15' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-16' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-17' AS ReportDate,109 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-18' AS ReportDate,110 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-19' AS ReportDate,111 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-20' AS ReportDate,112 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-21' AS ReportDate,113 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-22' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-23' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-24' AS ReportDate,114 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-25' AS ReportDate,115 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-26' AS ReportDate,116 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-27' AS ReportDate,117 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-28' AS ReportDate,118 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-29' AS ReportDate,119 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-30' AS ReportDate,120 AS ReportValue UNION ALL
    SELECT 1 AS ID, '2022-07-31' AS ReportDate,121 AS ReportValue UNION ALL

    SELECT 2 AS ID, '2022-07-01' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-02' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-03' AS ReportDate,200 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-04' AS ReportDate,200 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-05' AS ReportDate,201 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-06' AS ReportDate,202 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-07' AS ReportDate,203 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-08' AS ReportDate,204 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-09' AS ReportDate,204 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-10' AS ReportDate,204 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-11' AS ReportDate,205 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-12' AS ReportDate,206 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-13' AS ReportDate,207 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-14' AS ReportDate,208 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-15' AS ReportDate,209 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-16' AS ReportDate,209 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-17' AS ReportDate,209 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-18' AS ReportDate,210 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-19' AS ReportDate,211 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-20' AS ReportDate,212 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-21' AS ReportDate,213 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-22' AS ReportDate,214 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-23' AS ReportDate,214 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-24' AS ReportDate,214 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-25' AS ReportDate,215 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-26' AS ReportDate,216 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-27' AS ReportDate,217 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-28' AS ReportDate,218 AS ReportValue UNION ALL
    SELECT 2 AS ID, '2022-07-29' AS ReportDate,219 AS ReportValue --UNION ALL
    --SELECT 2 AS ID, '2022-07-30' AS ReportDate,220 AS ReportValue UNION ALL
    --SELECT 2 AS ID, '2022-07-31' AS ReportDate,221 AS ReportValue

    ) D

    --SELECT * FROM MyData

    SELECT
    C.CalendarDate
    , C.CalendarType
    , ID
    , D.ReportDate
    , D.ReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER( ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER( ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    FROM Calendar C
    LEFT JOIN MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)
    WHERE
    CalendarType = 1
    ORDER BY
    CalendarDate,ID
    GO
    CREATE OR ALTER VIEW Test
    AS
    SELECT
    C.CalendarDate
    , C.CalendarType
    , ID
    , D.ReportDate
    , D.ReportValue
    , CAST(SUBSTRING(MAX(v.val) OVER( ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 1, 10) AS DATE) AS LagReportDate
    , CAST(SUBSTRING(MAX(v.val) OVER( ORDER BY C.CalendarDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 11, 10) AS INT) AS LagReportValue
    FROM Calendar C
    LEFT JOIN MyData D
    ON C.CalendarDate = D.ReportDate
    CROSS APPLY (VALUES(CAST(C.CalendarDate AS CHAR(10)) + CAST(D.ReportValue AS CHAR(10)))) v(val)
    WHERE
    CalendarType = 1
    GO
    SELECT * FROM Test
    WHERE
    CalendarDate IN ('2022-07-08','2022-07-09')--ID 2 is not returning for 2022-07-09

    --DROP TABLE IF EXISTS Calendar
    --DROP TABLE IF EXISTS MyData

     

     

Viewing 15 posts - 16 through 30 (of 30 total)

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