Splitting a Date range

Question

Splitting a Date range

MathewK

SSC Veteran

Points: 234
More actions
December 1, 2011 at 4:47 pm

#247734

Hi,
facing a challenge in splitting a date range. Any help is appreciated
/* Example Data */
CREATE TABLE #Exp_date
(
Group_id INT,
effective date DATE,
termination_date DATE,
Sa_Count INT
)
INSERT INTO #Exp_date
VALUES
(4,'07-01-2006' ,'06-30-2009',1)
INSERT INTO #Exp_date
VALUES
(4,'07-01-2009' ,'12-31-2009',6)
Result:
Group_ideffective_datetermination_dateSa_Count
42006-07-01 2009-06-30 1
42009-07-01 2010-06-30 6
Each row needs to be expanded based on the Sa_Count. If 1 then increment by one year, If any other value increment by months, in the example, increment by 6 months.
Final result:
Class_ideffective_datetermination_dateSa_Count
42006-07-012007-06-301
42007-07-012008-06-301
42008-07-012009-06-301
42009-07-012009-12-316
42010-01-012010-06-306
Would CTE's help?

Viewing 9 posts - 1 through 9 (of 9 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Ugh, nasty. You need a row-repeater. Which means... To the TALLY!

Check out the link in my sig on tally tables if you're unfamiliar. They're nothing more then a table incrementing by 1 so you can use them for all sorts of places you'd usually create an incrementor loop.

FYI, your data_builder didn't work, effective date needs to be effective_date else you repeat the DATE type twice.

How does this deal with data when it's not hard years for a 1. Example: start 01/01/2010 term 06/30/2013. You'll want it to be 01/01/2010 - 12/31/2010, 01/01/2011 - 12/31/2011... 01/01/2013 - 06/30/2013, right?

Your monthly example is all in 2009, and never reaches 2010. How did you come up with the results you have for that value? Also, if he's ending at 12/31, why does it suddenly stop at 06/30?

There's values here that don't make sense. You might expand on the code I've written so far if you're so inclined and then return with a data-set and results that fit the business rules, but as it stands I'm either misunderstanding the rules or the data. Also for the monthly and yearly counts, you'll have to tell us what you want to do with values that don't expand over the range (for example, 07/01 - 12/31 in the same year is 5 months, 30 days, not 6 months, that'd be 07/01 - 01/01).

Code so far:

IF OBJECT_ID( 'tempdb..#Exp_date') is not null

DROP TABLE #Exp_Date

/* Example Data */

CREATE TABLE #Exp_date

(

edIDINT IDENTITY( 1, 1) NOT NULL,

Group_idINT,

effective_dateDATETIME,

termination_dateDATETIME,

Sa_CountINT

)

INSERT INTO #Exp_date

VALUES

(4,'07-01-2006' ,'06-30-2009',1)

INSERT INTO #Exp_date

VALUES

(4,'03-05-2008' ,'09-15-2012',1)

INSERT INTO #Exp_date

VALUES

(4,'09-05-2008' ,'03-15-2012',1)

INSERT INTO #Exp_date

VALUES

(4,'07-01-2009' ,'12-31-2009',6)

select

*,

DATEDIFF( mm, effective_date, termination_date) / sa_count

FROM

#exp_date

SELECT

e.edID,

e.Group_ID,

CASE sa_count

WHEN 1

THEN dateadd( yy, n-1, e.Effective_date)

ELSE dateadd( mm, n-sa_count, e.effective_date)

END AS effdt,

CASE sa_count

WHEN 1

THEN CASE WHEN datediff( yy, effective_date, termination_date) = t.n

THEN termination_date

ELSE dateadd( dd, -1, dateadd( yy, n, e.Effective_date))

END

ELSE

dateadd( mm, n, e.effective_date)

END as termdt

FROM

#exp_date AS e

CROSS JOIN

tempdb..Tally AS t

WHERE

(sa_count = 1

AND t.n <= datediff( yy, effective_date, termination_date))

OR (sa_count <> 1

AND t.n <= DATEDIFF( mm, effective_date, termination_date) / sa_count)

ORDER BY

e.edID,

t.n

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

MathewK SSC Veteran Points: 234 More actions · Answer 2

Kraig,

That was awesome!!

i was running around in circles trying to use the While loops and getting no where. The Tally helps, with a little tweaking to the code you gave, it works purrfect.

Thanks a bunch. Had heard about TAlly before, but never had used it. Its neat.

-Mathew

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You might find the function on the link below useful for what you are doing.

Finding the midpoint between two datetimes

The function in this script finds the midpoint in time between two datetimes.

Datetime Range Midpoint Function

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68806

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

Evil Kraig F (12/1/2011)
Ugh, nasty. You need a row-repeater. Which means... To the TALLY!

BWAAA-HAAA!!!! Beer popsicles... Dust Bunnies... nicely done Tally Table solutions... and a sense of humor to boot! 🙂 This forum is in good hands... I should retire. 😛

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

MathewK SSC Veteran Points: 234 More actions · Answer 5

MathewK

SSC Veteran

Points: 234

December 5, 2011 at 2:15 am

#1416518

Thanks Michael.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Jeff Moden (12/3/2011)
Evil Kraig F (12/1/2011)
Ugh, nasty. You need a row-repeater. Which means... To the TALLY!
BWAAA-HAAA!!!! Beer popsicles... Dust Bunnies... nicely done Tally Table solutions... and a sense of humor to boot! 🙂 This forum is in good hands... I should retire. 😛

Don't you DARE! I'm not done picking your brain yet!

Now, where'd I leave that pickaxe, need to speed this up apparently! 😛 😀

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

MathewK (12/2/2011)
Kraig,
That was awesome!!
i was running around in circles trying to use the While loops and getting no where. The Tally helps, with a little tweaking to the code you gave, it works purrfect.
Thanks a bunch. Had heard about TAlly before, but never had used it. Its neat.
-Mathew

My pleasure Matthew. Glad what I had written gave you enough of an idea of the process to tweak it to your own needs. As I mentioned, I just couldn't nail down what you meant for the different pieces. I'm glad you got it to work so well. See ya around. 😎

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 8

Evil Kraig F (12/5/2011)
Jeff Moden (12/3/2011)
Evil Kraig F (12/1/2011)
Ugh, nasty. You need a row-repeater. Which means... To the TALLY!
BWAAA-HAAA!!!! Beer popsicles... Dust Bunnies... nicely done Tally Table solutions... and a sense of humor to boot! 🙂 This forum is in good hands... I should retire. 😛
Don't you DARE! I'm not done picking your brain yet!
Now, where'd I leave that pickaxe, need to speed this up apparently! 😛 😀

I've been accused of being hard-headed so a pickaxe might not do it. 🙂

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