Create start of week column based on dates

Question

Post reply

Create start of week column based on dates

boehnc

Ten Centuries

Points: 1003
More actions
November 2, 2022 at 11:47 pm

#4107957

I have a table of students, with an enrollment start date and end date. Some have no end dates. I'd like to create a column that will list every Monday (start of week) from the enrollment start date to the end date. If no end date, then it will be getdate(). Thanks in advance
15, 2022-01-03, 2022-02-07, 2022-01-03
15, 2022-01-03, 2022-02-07, 2022-01-10
..and so on until
15, 2022-01-03, 2022-02-07, 2022-02-07

12, 2022-05-03, 2022-06-07, 2022-05-02
12, 2022-05-03, 2022-06-07, 2022-05-09
..and so on until
12, 2022-05-03, 2022-06-07, 2022-06-06
IF OBJECT_ID('TempDB..#value','U') IS NOT NULL
DROP TABLE #value
create table #value
(PatID varchar(10)
,StartDate date
,EndDate date)
Insert into #value
values
(15,'2022-01-03' ,'2022-02-07')
,(12, '2022-05-03','2022-06-07')
,(10, '2022-06-06', null )

Select
p.PatID
,p.StartDate
,p.EndDate
from #value p

Viewing 5 posts - 1 through 4 (of 4 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Did you see this?

Using Recursion and Date Tables to Simplify Date Logic

then you'd just filter it down (inside the CTE) so that it only returns Mondays.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

For some reason (mostly avoiding lots of "Logical READS"), I take great relish in avoiding Calendar tables, especially for things like this.

Here's the code I used...

--===== Solve the problem with fnTally and a couple of date formulae
 SELECT  v.PatID
        ,v.StartDate
        ,EndDate = ISNULL(v.EndDate,GETDATE())
        ,Monday  = CONVERT(DATE,dw.Monday)
   FROM #Value v
  CROSS APPLY dbo.fnTally(0,DATEDIFF(dd,v.StartDate,ISNULL(v.EndDate,GETDATE()))/7)t
  CROSS APPLY (VALUES(DATEADD(dd,t.N*7,v.StartDate)))wk(GenDate)
  CROSS APPLY (VALUES(DATEADD(dd,DATEDIFF(dd,0,wk.GenDate)/7*7,0)))dw(Monday)
;

Here are the first 20 rows of the results...

And here's the link to get the fnTally() function, which replaces loops of all sorts...

https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

If you don't understand the principle that fnTally() relies on, please read the first link in the article at the link above.

--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: 1004356 More actions · Answer 3

p.s. If you want to add a week number just add the following to the SELECT list...

,Wk = t.N+1

And thanks for the test data and the examples of the expected output. That makes a things a whole lot easier and saves on lots of questions.

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 4

"I'd like to create a column that will list every Monday (start of week) from the enrollment start date to the end date. If no end date, then it will be getdate()."

Let me see if I understand, you want to put all those dates in a (one) column ? Or a column for each date? If so then realise that here you will end up with a result that does not have a equal amount of columns if you are looking at more than one student.

I created a post to a similar question recently that you may find useful . A calendar table can serve as your base for attaining the week start dates.

https://www.sqlservercentral.com/forums/topic/table-header-with-dynamic-dates#post-4107040

----------------------------------------------------