Adding records between records
-
Hi,
Trying to figure out how to approach this...
I will have data where these is an ID and a date.
I want to add records between the two dates Max up to 6 days between the actual dates.
example
DATA:
ID, SomeDate, Flag
3642570, 2023-09-01 00:00:00.000, X
3642570, 2023-09-06 00:00:00.000, X
3642570, 2023-09-13 00:00:00.000, X
3642570, 2023-09-19 00:00:00.000, X
4051794, 2023-09-07 00:00:00.000, X
I'd like Output to look like:
3642570,2023-09-01 00:00:00.000,X
3642570,2023-09-02 00:00:00.000,null
3642570,2023-09-03 00:00:00.000,null
3642570,2023-09-04 00:00:00.000,null
3642570,2023-09-05 00:00:00.000,null<-- 5 inserted
3642570,2023-09-06 00:00:00.000,X
3642570,2023-09-07 00:00:00.000,null
3642570,2023-09-08 00:00:00.000,null
3642570,2023-09-09 00:00:00.000,null
3642570,2023-09-10 00:00:00.000,null
3642570,2023-09-11 00:00:00.000,null
3642570,2023-09-12 00:00:00.000,null<-- 6 inserted
3642570,2023-09-13 00:00:00.000,X
3642570,2023-09-14 00:00:00.000,null
3642570,2023-09-15 00:00:00.000,null
3642570,2023-09-16 00:00:00.000,null
3642570,2023-09-17 00:00:00.000,null
3642570,2023-09-18 00:00:00.000,null<-- 5 inserted
3642570,2023-09-19 00:00:00.000,X
3642570,2023-09-20 00:00:00.000,null
3642570,2023-09-21 00:00:00.000,null
3642570,2023-09-22 00:00:00.000,null
3642570,2023-09-23 00:00:00.000,null
3642570,2023-09-24 00:00:00.000,null
3642570,2023-09-26 00:00:00.000,null <-- 6 inserted
4051794,2023-09-07 00:00:00.000,X
4051794,2023-09-08 00:00:00.000,null
4051794,2023-09-09 00:00:00.000,null
4051794,2023-09-10 00:00:00.000,null
4051794,2023-09-11 00:00:00.000,null
4051794,2023-09-12 00:00:00.000,null
4051794,2023-09-13 00:00:00.000,null<-- 6 inserted
-
Consumable test data:
CREATE TABLE #t
(
ID int NOT NULL
,SomeDate datetime NOT NULL
,Flag char(1) NULL
,PRIMARY KEY (ID, SomeDate)
);
INSERT INTO #t
VALUES (3642570, '20230901', 'X')
,(3642570, '20230906', 'X')
,(3642570, '20230913', 'X')
,(3642570, '20230919', 'X')
,(4051794, '20230907', 'X');Maybe something like:
WITH Nums
AS
(
SELECT Num
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) V (Num)
)
,NextDates
AS
(
SELECT ID, SomeDate, Flag
,LEAD(SomeDate) OVER (PARTITION BY ID ORDER BY SomeDate) AS NextDate
FROM #t
)
SELECT D.ID
,DATEADD(day, N.Num, D.SomeDate) AS SomeDate
,CASE WHEN N.Num = 0 THEN D.Flag END AS Flag
FROM NextDates D
CROSS APPLY (VALUES (COALESCE(DATEDIFF(day, D.SomeDate, D.NextDate), 7)) ) X (ToAdd)
JOIN Nums N
ON N.Num < X.ToAdd; -
Thanks Kevin,
Not totally sure on what's going on it the code, but I'll teach myself....lol
Let me apply it to a real life situation and I'll get let you know....
Thanks Again...
Joe
-
Keep in mind, to retrieve data in a certain order, you need to add an order by clause to your select statement !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes! thank you....
I've been a bit busy with other things, but dying to start playing with this!! lol
I'll report back...
-
Well, I started testing with real data and I think this will work...
I wrote the data to a temp table, applied your logic and getting the results I think I need
Thank you and I'll be playing thru the day....
-
There should be no need to copy data to a temp table; just replace #t with the name of your table/view and adjust the column names accordingly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply