I have the following table
| Date | SKUs |
|---|---|
| 2022-02-01 | A, B, C |
| 2022-02-02 | B, C, D |
| 2022-02-03 | C, D, E |
| 2022-02-04 | C, D |
| 2022-02-05 | G, H |
Now I need to find the difference b/w SKUs of current date with previous date (something like lag function in Postgres)
| Date | SKUs | SKU Diff |
|---|---|---|
| 2022-02-01 | A, B, C | |
| 2022-02-02 | B, C, D | D |
| 2022-02-03 | C, D, E | E |
| 2022-02-04 | C, D | |
| 2022-02-05 | G, H | G, H |
SKUs column is of array type
with SKU as
(
select Dates,trim(a.value) as SKUs from SKUs s
cross apply string_split(s.SKUs,',') a
),
SKU_Diff as
(
SELECT dates, SKU_Diff FROM (SELECT Dates,STRING_AGG(SKUs,', ') as SKU_Diff, ROW_NUMBER() OVER(ORDER BY dates) AS rn
FROM SKU s
WHERE s.SKUs not in (SELECT s1.skus FROM SKU s1 WHERE s.dates = dateadd(day,1,s1.dates))
GROUP BY dates) as a WHERE rn > 1
)
SELECT s.Dates,SKUs,isnull(SKU_Diff,'') as SKU_Diff
FROM SKUs s
left join SKU_Diff sd on s.dates = sd.dates;
