hello,
I have a dataset which contains data for different year, month, day columns
I would like to get the changed data since one date to another
example of data:
company code state planned moisture year month day
--------------------------------------------------------------------------------------------------------------------
94925 code1 complete 22.9 11.999 2023 05 20
94925 code1 complete 22.9 12 2023 05 21
94925 code1 complete 22.9 12 2023 05 22
94925 code1 delete 22.9 12 2023 05 23
...
87654 code2 complete 18 7 2023 05 20
87654 code2 complete 18 9 2023 05 21
87654 code2 planned 18 9 2023 05 22
87654 code2 planned 18 9 2023 05 23
...
The unique columns are company and code
There are thousands of rows which are captured into each year, month and day as you see above...
I would like to find-out which rows have changed (Including what has changed) say from 2023-05-20 to 2023-05-25
Hope you see what I mean.
Thank you
If you only want the rows for a specific year, month and range of days:
WHERE year = 2023
AND month = 5
AND day BETWEEN 20 AND 25
If you want to be able to query a from date and thru date - then you need to combine the separate columns into a date, and then you can query based on that date. Ideally you would create a persisted computed column and an index on that column - but you could do something like this:
SELECT ...
FROM yourTable yt
CROSS APPLY (VALUES (DATEFROMPARTS(yt.year, yt.month, yt.day))) AS dt(somedate)
WHERE dt.somedate BETWEEN '2023-05-20' AND '2023-05-25'
It probably won't perform as well as it should - but is workable. Note: normally we would not recommend using BETWEEN with dates, but since we can control the data type - and DATEFROMPARTS returns a DATE data type then we can use BETWEEN safely.
To build on @jeffw8713 's suggestion:
WITH CTE AS(SELECT company,code,state,planned,moisture,year,month,day
,CASE WHEN MIN(state) OVER (PARTITION BY company,code) < MAX(state) OVER (PARTITION BY company,code) THEN 1 ELSE 0 END state_diff
,CASE WHEN MIN(planned) OVER (PARTITION BY company,code) < MAX(planned) OVER (PARTITION BY company,code) THEN 1 ELSE 0 END planned_diff
,CASE WHEN MIN(moisture) OVER (PARTITION BY company,code) < MAX(moisture) OVER (PARTITION BY company,code) THEN 1 ELSE 0 END moisture_diff
FROM myTable
WHERE DATEFROMPARTS(year,month,day) BETWEEN '20230520' AND '20230525')
SELECT company,code,state,planned,moisture,year,month,DAY
FROM CTE
WHERE state_diff=1 OR planned_diff=1 OR moisture_diff=1