Recursive data but increasing date by one day

Question

Recursive data but increasing date by one day

richardmgreen1

SSChampion

Points: 10305
More actions
January 19, 2015 at 3:12 am

#313760

Hi all
I've run into a bit of a snag with dates.
I've got some data with start and end dates in it, but I want to increase the start date by 1 day between two user-defind dates (if that makes sense).
For example:-
User-entered dates - 29th September 2014 and 18th January 2015 (start and end respectively)
Recorded dates - 7th July 2014 and 3rd November 2014 (start and end respectively)
What I want is this record repeated for each of the user-entered set of dates (or until the recorded end date is reached, whichever comes first). This is so that I can get the number of days between the recorded start date and the user entered start date (which is increased by 1 for each loop).
I've attached a file which (hopefully) explains this a bit better.
I can't post actual data due to confidentiality but I can get the rest of the bits I need quite easily.
This probably isn't the clearest request ever made so feel free to ask for more information.
Here's the script I've got so far:-
set dateformat dmy
----------------------------------
-- Sort out start and end dates --
----------------------------------
declare
@start datetime
,@end datetime
,@loopdate datetime
set @start=cast(dateadd(month,-3,getdate()-datepart(d,getdate())) as date)
set @start=@start-datepart(dw,@start)+1
set @end=cast(getdate()-datepart(dw,getdate()) as date)
set @loopdate=@start
select @start
----------------
-- LOS by Day --
----------------
;with cte as (
select
abs1.[UnitNumber]
,abs1.AccountNumber
,abs1.AdmitDateTime
,abs1.DischargeDateTime
,abs1.SecondLatestWardIDInSpell
,abs1.LatestWardLocationInSpell
FROM
[RFT_RS_REPORTING].[dbo].[RFT_DR_ALL_ABS] abs1
where
cast(abs1.AdmitDateTime as date) < @start
and (cast(abs1.DischargeDateTime as date) > @start
or abs1.DischargeDateTime is null)
and abs1.PtStatus='IN'
and datediff(d,abs1.admitdatetime,@start)<=365
)
select
@loopdate
,a.*
from
cte a
I'm not certain this is possible in a CTE but it seems teh fastest way to do it rather than using a WHILE loop. Having said that, I'm open to suggestions.

Viewing 15 posts - 1 through 15 (of 18 total)

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

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

Your spec is a little ambiguous and quite tricky to follow. Hopefully this will give you some ideas. iTally is an IBG-style inline tally table which will generate just as many rows as you need, determined by the TOP() expression. Plug in the start and end date and the CROSS APPLY generates rows with incrementing days, from the start date to the end date.

;WITH

L1(n) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

L2(n) AS (SELECT 0 FROM L1, L1 b),

L4(n) AS (SELECT 0 FROM L2, L2 b),

iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM L4)

SELECT

[Recorded Start Date],

[Recorded End Date],

x.n,

[LoopDate] = DATEADD(DAY,x.n-1,[Recorded Start Date])

FROM ( -- this derived table is a simulation of one row of your data

SELECT

[Recorded Start Date] = CAST('20140707' AS DATE),

[Recorded End Date] = CAST('20141103' AS DATE)

) MyData

CROSS APPLY (SELECT TOP (1+DATEDIFF(DAY,[Recorded Start Date],[Recorded End Date])) n FROM iTally) x

^{“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

richardmgreen1 SSChampion Points: 10305 More actions · Answer 2

Thanks for that Chris.

I might be back for some extra help when I start changing it slightly to add in all my other fields (and quite possibly for an explanation of some of the concepts I've never seen before).

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

richardmgreen1 (1/19/2015)
Thanks for that Chris.
I might be back for some extra help when I start changing it slightly to add in all my other fields (and quite possibly for an explanation of some of the concepts I've never seen before).

You're welcome.

^{“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: 1003872 More actions · Answer 4

Although Chris' code does what you want, I'm not sure it does what you actually need. Yes, it works correctly (haven't run it but looks correct). If I'm reading the code correctly (working on my first cup o' Joe) and in reference to the following part of your original post...

What I want is this record repeated for each of the user-entered set of dates (or until the recorded end date is reached, whichever comes first). This is so that I can get the number of days between the recorded start date and the user entered start date (which is increased by 1 for each loop).

... you can easily just do a DATEDIFF between the two dates to get the count of days for each row with a SUM(...) OVER (PARTITION BY...) to get the count instead of the relatively very expensive explosion of dates. The code will be much simpler and likely much faster.

Also, rCTEs (Recursive CTE's) that count (increment) for each iteration are horrible for performance and terrible resource hogs. Instead, use something like what Chris used. See the following article for why their performance is so horrible.

http://www.sqlservercentral.com/articles/T-SQL/74118/

--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)

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

Jeff Moden (1/19/2015)
Although Chris' code does what you want, I'm not sure it does what you actually need. Yes, it works correctly (haven't run it but looks correct). If I'm reading the code correctly (working on my first cup o' Joe) and in reference to the following part of your original post...
What I want is this record repeated for each of the user-entered set of dates (or until the recorded end date is reached, whichever comes first). This is so that I can get the number of days between the recorded start date and the user entered start date (which is increased by 1 for each loop).
... you can easily just do a DATEDIFF between the two dates to get the count of days for each row with a SUM(...) OVER (PARTITION BY...) to get the count instead of the relatively very expensive explosion of dates. The code will be much simpler and likely much faster.
Also, rCTEs (Recursive CTE's) that count (increment) for each iteration are horrible for performance and terrible resource hogs. Instead, use something like what Chris used. See the following article for why their performance is so horrible.
http://www.sqlservercentral.com/articles/T-SQL/74118/

This is my first impression too, but there's insufficient detail as yet to confirm. Posting up the final (successful) query often offers folks the opportunity to fully understand the OP's requirements and hence rewrite the code in a cleaner, faster fashion.

^{“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: 1003872 More actions · Answer 6

@chris-2,

Agreed. You're going with what was given and I'm trying to read minds. 😛

--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)

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

Jeff Moden (1/19/2015)
@Chris,
Agreed. You're going with what was given and I'm trying to read minds. 😛

You've been around here for a little longer than I have 😉

^{“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

richardmgreen1 SSChampion Points: 10305 More actions · Answer 8

To be honest, I didn't think my initial post made too much sense, I was hoping the spreadsheet would explain better (obviously not:blush:)

This is my original code:-

select distinct

abs1.[UnitNumber]

,abs1.AccountNumber

,abs1.AdmitDateTime

,abs1.DischargeDateTime

,datediff(d,abs1.admitdatetime,abs1.dischargedatetime) as Total_LOS

,datediff(d,abs1.admitdatetime,@start) as Current_LOS

,abs1.SecondLatestWardIDInSpell

,abs1.LatestWardLocationInSpell

FROM

[RFT_RS_REPORTING].[dbo].[RFT_DR_ALL_ABS] abs1

where

cast(abs1.AdmitDateTime as date) < @start

and (cast(abs1.DischargeDateTime as date) > @start

or abs1.DischargeDateTime is null)

and abs1.PtStatus='IN'

and datediff(d,abs1.admitdatetime,@start)<=365

order by

abs1.LatestWardLocationInSpell

,datediff(d,abs1.admitdatetime,@start) desc

What I want to be able to do is, for the field called Current_LOS, get a running count of the days this patient has been in hospital.

For example:-

Patient is admitted on 1st July 2014

I run my data from 7th July 2014 to 13th of July 2014 (this date range will be bigger, but this will do for demo purposes).

Therefore, I need to see that for any given day, I can their LOS (length of stay).

So on the 7th of July it will be 6 days, on the 8th of July it will be 7 days, etc.

This is going to be put into a report where managers can pick a day/range of days and see which patients have been admitted and for how long.

The only other bit I'll need is that, if the patient was discharged on 9th July, their LOS will drop to 0 (they're no longer admitted).

Hopefully that makes a bit more sense.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

If the user enters a date range, how many rows should the report display per patient stay - one, same as the source table, or more than one, depending on the details of the stay? We need to see what your output should look like for a given set of inputs.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 10

richardmgreen1 (1/19/2015)
To be honest, I didn't think my initial post made too much sense, I was hoping the spreadsheet would explain better (obviously not:blush:)
This is my original code:-
select distinct
abs1.[UnitNumber]
,abs1.AccountNumber
,abs1.AdmitDateTime
,abs1.DischargeDateTime
,datediff(d,abs1.admitdatetime,abs1.dischargedatetime) as Total_LOS
,datediff(d,abs1.admitdatetime,@start) as Current_LOS
,abs1.SecondLatestWardIDInSpell
,abs1.LatestWardLocationInSpell
FROM
[RFT_RS_REPORTING].[dbo].[RFT_DR_ALL_ABS] abs1
where
cast(abs1.AdmitDateTime as date) < @start
and (cast(abs1.DischargeDateTime as date) > @start
or abs1.DischargeDateTime is null)
and abs1.PtStatus='IN'
and datediff(d,abs1.admitdatetime,@start)<=365
order by
abs1.LatestWardLocationInSpell
,datediff(d,abs1.admitdatetime,@start) desc
What I want to be able to do is, for the field called Current_LOS, get a running count of the days this patient has been in hospital.
For example:-
Patient is admitted on 1st July 2014
I run my data from 7th July 2014 to 13th of July 2014 (this date range will be bigger, but this will do for demo purposes).
Therefore, I need to see that for any given day, I can their LOS (length of stay).
So on the 7th of July it will be 6 days, on the 8th of July it will be 7 days, etc.
This is going to be put into a report where managers can pick a day/range of days and see which patients have been admitted and for how long.
The only other bit I'll need is that, if the patient was discharged on 9th July, their LOS will drop to 0 (they're no longer admitted).
Hopefully that makes a bit more sense.

To summarize, are you simply looking for the number of people still in the hospital on each day for a given range of dates?

--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)

richardmgreen1 SSChampion Points: 10305 More actions · Answer 11

Hi Jeff

I think that just about covers it.

I'll also need the dates so I can do a DATEDIFF to find the number of days.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 12

richardmgreen1 (1/20/2015)
Hi Jeff
I think that just about covers it.
I'll also need the dates so I can do a DATEDIFF to find the number of days.

K. Thanks, Richard. I'll try to get to this tonight if someone doesn't beat me to it.

In the meantime, you might want to take a look at the following article, which contains the basis of the method I'll likely use for this.

http://www.sqlservercentral.com/articles/T-SQL/105968/

--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)

richardmgreen1 SSChampion Points: 10305 More actions · Answer 13

Thanks Jeff

I'll have a look at the article in a few minutes.

steve.tarry SSC Enthusiast Points: 132 More actions · Answer 14

Recursive Date

DECLARE @STARTDATE DATE = getdate();

DECLARE @ENDDATE DATE = '2015-03-21';

WITH DateCTE AS (

SELECT@startdate AS myDate -- can be parameter supplied

UNION ALL

SELECTDATEADD(Day,1,myDate) myDate1

FROMDateCTE

WHEREDATEADD(Day,1,myDate) <= @Enddate -- set the to date

)

Select * From DateCTE

OPTION (MAXRECURSION 32767)