Using Start and End dates to create a number of months column

Question

Post reply

Using Start and End dates to create a number of months column

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
March 5, 2010 at 5:54 am

#143391

Im trying to simplify a stupidly long winded query. the example is below
CREATE TABLE Test_Registrations
(registration_ID INT,
PERSON_ID Int,
Start_date_Registration DATETIME,
[Registration End Date] DATETIME)
INSERT INTO Test_Registrations
SELECT 1,100,'1997-02-14 00:00:00.000','1997-05-28 00:00:00.000' UNION
SELECT 2,102,'1998-01-22 00:00:00.000','1998-02-01 00:00:00.000' UNION
SELECT 3,104,'1999-06-11 00:00:00.000','1999-12-15 00:00:00.000' UNION
SELECT 3,105,'2000-10-11 00:00:00.000','2001-05-13 00:00:00.000' UNION
SELECT 3,108,'2001-01-08 00:00:00.000','2001-05-26 00:00:00.000'
SELECT Registration_ID, [Person_ID], Start_Date_Registration,3 AS Duration,
CASE WHEN DATEADD(mm,3,Start_Date_Registration)< ISNULL([Registration End Date],GETDATE()) THEN DATEADD(mm,3,Start_Date_Registration)
ELSE NULL END AS [Date Point]
FROM Test_Registrations
WHERE CASE WHEN DATEADD(mm,3,Start_Date_Registration)< ISNULL([Registration End Date],GETDATE()) THEN DATEADD(mm,3,Start_Date_Registration)
ELSE NULL END IS NOT NULL
UNION
SELECT Registration_ID, [Person_ID], Start_Date_Registration,4 AS Duration,
CASE WHEN DATEADD(mm,4,Start_Date_Registration)< ISNULL([Registration End Date],GETDATE()) THEN DATEADD(mm,4,Start_Date_Registration)
ELSE NULL END AS [Date Point]
FROM Test_Registrations
WHERE CASE WHEN DATEADD(mm,4,Start_Date_Registration)< ISNULL([Registration End Date],GETDATE()) THEN DATEADD(mm,4,Start_Date_Registration)
ELSE NULL END IS NOT NULL
UNION
SELECT Registration_ID, [Person_ID], Start_Date_Registration,5 AS Duration,
CASE WHEN DATEADD(mm,5,Start_Date_Registration)< ISNULL([Registration End Date],GETDATE()) THEN DATEADD(mm,5,Start_Date_Registration)
ELSE NULL END AS [Date Point]
FROM Test_Registrations
WHERE CASE WHEN DATEADD(mm,5,Start_Date_Registration)< ISNULL([Registration End Date],GETDATE()) THEN DATEADD(mm,5,Start_Date_Registration)
ELSE NULL END IS NOT NULL
so for example reg 1 person ID 100 is only in the data set once with a duration of 3 months.
Person ID 102 hasnt made it to the dataset because their registration only lastes 2 months.
Person 104 has an entry for 3 months 4 monts and 5 months. their reg does
go on for longer but the SQL only goes up to 5 months.
Person 108 has an entry of 3 months and 4 months because their reg lassts one month.
This table goes on for 140 months. Ewwwwwwwww. there must be an easier way to do this like
a loop or something. Only stop when your longest reg has been catered for
I would be grateful for any ideas
Thanks
Debbie

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

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Do you mean you have 140 almost identical query's UNIONed together? Try this one:

SELECT

Registration_ID, [Person_ID], Start_Date_Registration,

J.Duration, A.[Date Point]

FROM

dbo.Test_Registrations

CROSS JOIN

(

SELECT Number Duration FROM master..spt_values WHERE Type = 'P' AND Number between 3 AND 140

-- SELECT 3 Duration UNION ALL SELECT 4 UNION ALL SELECT 5

) J

CROSS APPLY

(

SELECT

CASE

WHEN DATEADD(mm,J.Duration,Start_Date_Registration)< ISNULL([Registration End Date],GETDATE()) THEN

DATEADD(mm,J.Duration,Start_Date_Registration)

ELSE

NULL

END AS [Date Point]

) A

WHERE

A.[Date Point] IS NOT NULL

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

This would do it:

SELECT r.Registration_ID, r.[Person_ID], r.Start_Date_Registration, iTVF.MonthNum AS Duration,

CASE WHEN iTVF.[Date Point] < ISNULL(r.[Registration End Date], GETDATE())

THEN iTVF.[Date Point] ELSE NULL END

FROM Test_Registrations r

CROSS APPLY(

SELECT MonthNum, [Date Point] = DATEADD(mm, mn.MonthNum, r.Start_Date_Registration)

FROM (SELECT 3 AS MonthNum union all select 4 union all select 5) mn

) iTVF

WHERE iTVF.[Date Point] < ISNULL(r.[Registration End Date], GETDATE())

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

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

Peter Brinkhaus (3/5/2010)
Do you mean you have 140 almost identical query's UNIONed together? Try this one:

Oops...sorry Peter.

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

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Ooooh the first one brings bacl 12 rows and the next one brings back 9 rows.

Im going to have a look at both of them. I need to find out about some of the things going on like CROSS Apply and master..spt_values.

And yes I did have 140 union queries. The worst solution to a problem ever.:blush:

Ill have a look and come back. Brilliant!

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

Debbie Edwards (3/5/2010)
Ooooh the first one brings bacl 12 rows and the next one brings back 9 rows.
Im going to have a look at both of them. I need to find out about some of the things going on like CROSS Apply and master..spt_values.
And yes I did have 140 union queries. The worst solution to a problem ever.:blush:
Ill have a look and come back. Brilliant!

Peter's solution brings back more rows because his row generator doesn't stop at 5, like my hard-coded version. You're almost certainly better off with his, go for it.

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

Chris' solution returns 9 rows because it only take 3 date points into account, the same as your original query. My solution returns 12 rows because it take 138 (3 to 140) date points into account. Take a look at the duration column.

About master..spt_values, it's a table with all kind of constants. The rows with type is 'P' contain the numbers 0 to 2047. It's used here as a Tally or Numbers table.

HTH,

Peter

@chris-2: Good to see we were on the same track (edit: and it looks we are running almost synchronous on this one)

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 7

Thanks all.

I have just used the query in my original SP instead of the ridiculously long one and it works a treat.

I hope at some point I will be able to use CROSS join without needing help. It looks like it could be really useful!

Debbie

matzer SSCrazy Eights Points: 9727 More actions · Answer 8

matzer

SSCrazy Eights

Points: 9727

March 5, 2010 at 7:19 am

#1129156

Glad we could help. Thanks for the feedback.

Peter

Garadin One Orange Chip Points: 29613 More actions · Answer 9

Debbie Edwards (3/5/2010)
Thanks all.
I have just used the query in my original SP instead of the ridiculously long one and it works a treat.
I hope at some point I will be able to use CROSS join without needing help. It looks like it could be really useful!
Debbie

As a side note (mainly in case you don't get around to working with it for a while and don't have the code at hand to reference), what they used wasn't a cross JOIN, it was cross APPLY. Cross Joins also exist, but aren't quite the same.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 10

ahhhhh come on. I knew that I just wasnt thinking and put cross join instead of cross apply:-)

Garadin One Orange Chip Points: 29613 More actions · Answer 11

Wasn't trying to be picky, if cross joins weren't also a real thing I wouldn't have said a word... I just know that my memory plays tricks on me sometimes with things like that so was trying to potentially save you the time of sitting there for a while at some future date wondering 'How the heck did they use a cross join to do that...'

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 12

Im wondering if there are any useful documents on line that can give really good information on what CROSS APPLY is and how it can be used. Ive had a look at a few documents and its quite difficult to get your head round it.

Its quite exciting to find out about a new bit of SQL 😀

Debbie

Garadin One Orange Chip Points: 29613 More actions · Answer 13

Debbie Edwards (3/5/2010)
Its quite exciting to find out about a new bit of SQL 😀
Debbie

I'd agree with that completely. Had some fun playing with "GROUPING SETS" this morning myself thanks to the article in this morning's newsletter.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 14

Garadin (3/5/2010)
Debbie Edwards (3/5/2010)
Its quite exciting to find out about a new bit of SQL 😀
Debbie
I'd agree with that completely. Had some fun playing with "GROUPING SETS" this morning myself thanks to the article in this morning's newsletter.

I don't have 2k8 to play with but doesn't that work out to be the same thing as WITH ROLLUP?

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