Query to fill in missing dates in a series of rows

Question

Query to fill in missing dates in a series of rows

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

bnordberg (12/18/2008)
The data in this format will allow for contract tracing of MRSA infections. Our statisticians can pipe this through some algorithims in SAS to see if we can't find patterns of infection/spread.

I need to know... are the event dates always going to be whole dates (ie, time = 00:00:00.000).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bnordberg SSCertifiable Points: 5480 More actions · Answer 2

Chris's works for me (with my samlpe table, I have not adapted to my real mess - err tables)

Most will have times associated, some will not.

Thanks for your help!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

bnordberg (12/19/2008)
Chris's works for me (with my samlpe table, I have not adapted to my real mess - err tables)
Most will have times associated, some will not.
Thanks for your help!

"It works, don't fix it" has been the undoing of many an idiot I mean manager.

Most will have times associated, some will not

Thanks - this will probably impact the design and will almost certainly impact the performance.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

I think this will do it very quickly using the ol' "Divide and Conquer" methods. Side benefit it that it doesn't affect the original table... it makes a brand new table that you can copy and drop.

--===== Create artificial rows where all inclusive dates are in place for each Person_ID

SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY aid.Person_ID, aid.Event_Date),0) AS RowNum,

aid.Person_ID,

ISNULL(adt.Event_Type,'Stay') AS Event_Type,

aid.Event_Date,

adt.Location_ID

INTO #Work

FROM dbo.ADT adt

RIGHT OUTER JOIN

(--==== Create ALL inclusive dates for each Person_ID

SELECT mmd.Person_ID,

DATEADD(dd,(t.N-1),mmd.MinDate) AS Event_Date

FROM dbo.Tally t

INNER JOIN

(--==== Find Min and Max dates for each personID

SELECT Person_ID, MIN(Event_Date) AS MinDate, MAX(Event_Date) AS MaxDate

FROM dbo.Adt

GROUP BY Person_ID

) mmd

ON t.N <= DATEDIFF(dd,mmd.MinDate,mmd.MaxDate)+1

) aid

ON adt.Person_ID = aid.Person_ID

AND adt.Event_Date = aid.Event_Date

--===== Add a clustered index in the order we need it

ALTER TABLE #Work

ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Declare local variables to keep track of previous row

DECLARE @PrevLocation_ID INT,

@PrevRowNum INT

--===== Smear the Location_ID downward. Works even when more than 1 is null

UPDATE #Work

SET @PrevLocation_ID = Location_ID = CASE WHEN Location_ID IS NULL THEN @PrevLocation_ID ELSE Location_ID END,

@PrevRowNum = RowNum

FROM #Work WITH(INDEX(0))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Hang on a minute... I'll fix the smiley faces...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Let's try that again...

--===== Create artificial rows where all inclusive dates are in place for each Person_ID

SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY aid.Person_ID, aid.Event_Date),0) AS RowNum,

aid.Person_ID,

ISNULL(adt.Event_Type,'Stay') AS Event_Type,

aid.Event_Date,

adt.Location_ID

INTO #Work

FROM dbo.ADT adt

RIGHT OUTER JOIN

(--==== Create ALL inclusive dates for each Person_ID

SELECT mmd.Person_ID,

DATEADD(dd,(t.N-1),mmd.MinDate) AS Event_Date

FROM dbo.Tally t

INNER JOIN

(--==== Find Min and Max dates for each personID

SELECT Person_ID, MIN(Event_Date) AS MinDate, MAX(Event_Date) AS MaxDate

FROM dbo.Adt

GROUP BY Person_ID

) mmd

ON t.N <= DATEDIFF(dd,mmd.MinDate,mmd.MaxDate)+1

) aid

ON adt.Person_ID = aid.Person_ID

AND adt.Event_Date = aid.Event_Date

--===== Add a clustered index in the order we need it

ALTER TABLE #Work

ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Declare local variables to keep track of previous row

DECLARE @PrevLocation_ID INT,

@PrevRowNum INT

--===== Smear the Location_ID downward. Works even when more than 1 is null

UPDATE #Work

SET @PrevLocation_ID = Location_ID = CASE WHEN Location_ID IS NULL THEN @PrevLocation_ID ELSE Location_ID END,

@PrevRowNum = RowNum

FROM #Work WITH(INDEX(0))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)