Recursive data but increasing date by one day

• steve.tarry

  SSC Enthusiast

  Points: 132

  More actions

  January 21, 2015 at 9:10 am

  #1771881

  Or something like this

  DECLARE @PatientDATE = '2015-01-04';

  DECLARE @Discharged DATE = '2015-01-19';

  DECLARE @STARTDATEDATE = getdate();

  DECLARE @ENDDATEDATE = getdate()+7;

  WITH DateCTE AS (

  SELECT@startdate AS myDate -- add half the recursive to the day dot SQL date 1900-01-01

  ,datediff(d, @patient, @startdate) as days

  UNION ALL

  SELECTDATEADD(Day,1,myDate) myDate1

  , 0

  FROMDateCTE

  WHEREDATEADD(Day,1,myDate) <= @Enddate -- loop through the max recursion

  )

  Select d.*

  , @Patient

  , @Discharged

  , datediff(d, @Patient, Case When myDate < @Discharged Then myDate else @Discharged end

  ) DaysInHospital

  , datediff(d, @Patient, myDate) DaysInHospital

  , case when @discharged > @startdate then

  datediff(d, @Patient, myDate) else 0

  end Inhospital

  From DateCTE d

  OPTION (MAXRECURSION 32767)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 21, 2015 at 9:17 am

  #1771884

  steve.tarry (1/21/2015)

  ---

  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)

  rCTE is an expensive way to generate dates. Try this method - it's a kinda standard around here because it's a) cheap and b) easy to code.

  Check BOL for OPTION (MAXRECURSION 0)

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

• steve.tarry

  SSC Enthusiast

  Points: 132

  More actions

  January 21, 2015 at 9:47 am

  #1771899

  Thanks for the piece of code. Very nice. In the grand scheme of things the CTE query executes very quickly but happy to use a better solution.

  Great stuff.

  Have a good day.

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  February 14, 2016 at 2:35 pm

  #1858682

  steve.tarry (1/21/2015)

  ---

  Thanks for the piece of code. Very nice. In the grand scheme of things the CTE query executes very quickly but happy to use a better solution.

  Great stuff.

  Have a good day.

  CTEs are great. It's the recursive ones that increment that are both a performance and resource usage problem.

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