August 4, 2022 at 10:28 pm
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'
August 4, 2022 at 10:37 pm
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)
August 4, 2022 at 11:34 pm
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
August 5, 2022 at 4:27 pm
This was removed by the editor as SPAM
August 5, 2022 at 6:01 pm
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
August 5, 2022 at 6:37 pm
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
August 5, 2022 at 7:25 pm
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'?
August 5, 2022 at 7:37 pm
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;
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
August 5, 2022 at 8:23 pm
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?
August 5, 2022 at 8:27 pm
[/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.
August 5, 2022 at 8:30 pm
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.
August 5, 2022 at 8:30 pm
This was removed by the editor as SPAM
August 5, 2022 at 8:38 pm
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
August 5, 2022 at 9:22 pm
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
August 5, 2022 at 11:03 pm
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 46 total)
You must be logged in to reply to this topic. Login to reply