1

I have a slowly changing table,a new row is created each time any of the source fields are changed. Some metadata is added to show when that version was valid. This is a simplified example(dates are dd/mm/yyyy format) that doesn't show the fields which have changed.

Startdate Enddate Currentrecord unique id serial_number
15/12/2020 31/12/2020 0 1 2345
15/12/2020 8/3/2021 0 2 1234
19/9/2020 15/2/2021 0 3 2345
15/12/2020 8/3/2021 0 4 3456
9/3/2021 10/3/2021 0 5 3456
16/2/2021 10/3/2021 0 6 2345
9/3/2021 26/3/2021 0 7 1234
27/3/2021 2/5/2021 0 8 1234
11/3/2021 17/5/2021 0 9 3456
3/3/2021 27/4/2021 0 10 4567
20/1/2021 7/4/2021 0 11 5678
3/5/2021 30/6/2021 1 12 1234
25/5/2021 31/5/2021 0 13 2345
8/4/2021 22/5/2021 0 14 5678
1/6/2021 26/6/2021 0 15 2345
18/5/2021 3/6/2021 0 16 3456
27/6/2021 2/8/2021 0 17 2345
28/4/2021 28/6/2021 0 18 4567
23/5/2021 6/9/2021 0 19 5678
4/6/2021 28/6/2021 0 20 3456
29/6/2021 25/7/2021 0 21 3456
3/8/2021 31/12/9999 1 22 2345
26/7/2021 31/12/9999 1 23 3456
15/10/2021 31/12/9999 1 24 4567
7/9/2021 1/11/2021 0 25 5678
22/9/2021 10/11/2021 0 26 6789
2/11/2021 16/11/2021 0 27 5678
17/11/2021 21/11/2021 0 28 5678
15/7/2021 31/12/9999 1 29 7891
22/11/2021 31/12/9999 1 30 5678
26/11/2021 31/12/9999 1 31 6789
15/6/2021 31/12/9999 1 32 8912

There is only one record for each serial_number for any given point in time (i.e. the dates ranges will not overlap for identical serial_numbers) but there might be gaps between episodes for a some serial_numbers (representing something leaving and returning after a gap in service).

I want to supply an arbitrary list of datetimes, say midnight on 01/01/2021, 15/03/2021, 27/05/2021. 23/10/2021. I want to return a set of records, containing every record which was in effect on each of the dates, with each row labelled with the date it was selected by. So the above example should return this.

date unique id serial_number
1/1/2021 2 1234
1/1/2021 3 2345
1/1/2021 4 3456
15/3/2021 7 1234
15/3/2021 9 3456
15/3/2021 10 4567
15/3/2021 11 5678
27/5/2021 12 1234
27/5/2021 13 2345
27/5/2021 16 3456
27/5/2021 18 4567
27/5/2021 19 5678
23/10/2021 22 2345
23/10/2021 23 3456
23/10/2021 24 4567
23/10/2021 25 5678
23/10/2021 26 6789
23/10/2021 29 7891
23/10/2021 32 8912

I can see how to do this with a cursor, stepping through each date putting them into a variable and using something like

select @date, [unique id], serial_number
from example
where @date between start_date and end_date

to get the rows.

I can’t work out a pattern that would do it in a set based approach. My preferred SQL version is TSQL. Sorry as this is almost certainly a repeat, but I can't find a form of words that hits a worked example.

Krawling Khaos
  • 11
  • 2

2 Answers2

2

You can use a temporary table to accomplish this.

CREATE TABLE #RequestedDates([Date] DATE)

You insert your dates you want into a temporary table.

INSERT INTO #RequestedDates([Date])
VALUES ('2021-01-01'), ('2021-03-15') /*Other dates*/

And then you join with the temporary table and use the between clause to get the valid results.

SELECT rd.[Date]
  , t.UniqueId
  , t.SerialNumber
FROM MyTable t
INNER JOIN #RequestedDates rd on rd.[Date] BETWEEN t.StartDate AND t.EndDate
ORDER BY rd.[Date]
  , t.UniqueId
  , t.SerialNumber
Preben Huybrechts
  • 5,853
  • 2
  • 27
  • 63
1

You can join to VALUES with the dates you need.
Then join the datetimes on the range.

SELECT 
  datetimes.dt as [date] 
, t.[unique id] 
, t.serial_number
FROM example t
JOIN (VALUES
 (cast('2021-01-01 00:00:00' as datetime)), 
 ('2021-03-15 00:00:00'), 
 ('2021-05-27 00:00:00'), 
 ('2021-10-23 00:00:00')
) datetimes(dt) 
  ON datetimes.dt >= t.start_date
 AND datetimes.dt <= t.end_date
ORDER BY datetimes.dt, t.[unique id], t.serial_number
LukStorms
  • 28,916
  • 5
  • 31
  • 45