Partial Date Range and Datediff

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Partial Date Range and Datediff

Author

Topic

cardgunner

326 Posts

Posted - 2007-06-11 : 14:27:19

I have to date ranges for two objects(AB and CD's) and I want to find and CD's that may have happened in the date range of AB.

So my date range for AB is 3-1-2007 to 3-31-2007.

My date ranges for CD
CD1, 2-15-2207 to 5-27-2007
CD2, 4-2-2007 to 4-30-2007
CD3, 1-1-2007 to 2-5-2007
CD4, 2-15-2007 to 3-15-2007
CD5, 3-17-2007 to 5-2-2007

I would like to get the result
CD1, 2-15-2207 to 5-27-2007
CD4, 2-15-2007 to 3-15-2007
CD5, 3-17-2007 to 5-2-2007

Then I need to know how many days each of the CD range's are part of the AB range
the result of that would be
CD1, 31
CD2, 15
CD3, 14

Do I need to better explain this?

I'm about 4 hours in this problem trying to come up with CASE statements because that is about the depth of my knowledge. Can anyone give me a hand?

Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 14:32:08

Are the date's in separate columns or is "2-15-2207 to 5-27-2007" in one column?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-06-11 : 14:42:25

The dates are in two different columns

Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 14:53:50

[code]
Declare @d1 datetime, @d2 datetime
Select @d1 = '3-1-2007', @d2 = '3-31-2007'

Declare @t table (CD varchar(5), Date1 Datetime, Date2 datetime)
Insert into @t

select 'CD1', '2-15-2007' , '5-27-2007' union all
select 'CD2', '4-2-2007' , '4-30-2007' union all
select 'CD3', '1-1-2007' , '2-5-2007' union all
select 'CD4', '2-15-2007' , '3-15-2007' union all
select 'CD5', '3-17-2007' ,'5-2-2007' union all
select 'CD6', '3-15-2007' ,'3-20-2007'

Select * from @t
Where (Date1 >= @d1 And Date1 <= @d2)
or ( Date2 >= @d1 And Date2 <= @d2 )
Or (Date1 <= @d1 And Date2 >= @d2)
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-11 : 15:19:06

I think this will do it.. The main thing is to think in terms of how the spans intersect or overlap:

DECLARE @ABStart DATETIME
DECLARE @ABEnd DATETIME
DECLARE @CD TABLE (ID VARCHAR(3), StartDate DATETIME, EndDate DATETIME)

INSERT @CD
SELECT 'CD1', '2-15-2007', '5-27-2007'
UNION SELECT 'CD2', '4-2-2007', '4-30-2007'
UNION SELECT 'CD3', '1-1-2007', '2-5-2007'
UNION SELECT 'CD4', '2-15-2007', '3-15-2007'
UNION SELECT 'CD5', '3-17-2007', '5-2-2007'

SET @ABStart = '3-1-2007'
SET @ABEnd = '3-31-2007'

SELECT 
	*,
	CASE
		WHEN cd.StartDate <= @ABStart AND cd.EndDate >= @ABEnd  
			THEN DATEDIFF(DAY, @ABStart, @ABEnd) + 1
		WHEN cd.StartDate >= @ABStart AND cd.EndDate <= @ABEnd 
			THEN DATEDIFF(DAY, cd.StartDate, cd.EndDate) + 1
		WHEN cd.StartDate < @ABStart AND cd.EndDate > @ABStart AND cd.EndDate < @ABEnd
			THEN DATEDIFF(DAY, @ABStart, cd.EndDate) + 1
		WHEN cd.StartDate > @ABStart AND cd.StartDate < @ABEnd AND cd.EndDate > @ABEnd 
			THEN DATEDIFF(DAY, cd.StartDate, @ABEnd)
	END	 AS Span
FROM @CD cd
WHERE
1 = CASE
	WHEN cd.StartDate <= @ABStart AND cd.EndDate >= @ABEnd     THEN 1 -- Spans Over
	WHEN cd.StartDate >= @ABStart AND cd.EndDate <= @ABEnd   THEN 1 -- Between
	WHEN cd.StartDate < @ABStart AND cd.EndDate > @ABStart AND cd.EndDate < @ABEnd THEN 1 -- Lower Overlap
	WHEN cd.StartDate > @ABStart AND cd.StartDate < @ABEnd AND cd.EndDate > @ABEnd   THEN 1 -- Upper Span
END

I Forgot to mention, you could change the WHERE clause to be just ANDs and ORs. But I left it as a CASE so you can see how it relates to the calculations above.

cardgunner

326 Posts

Posted - 2007-06-11 : 15:42:07

Thank you so much dinakar and Lamprey.

After i posted it all came to me and I came up with a third way.


select datediff(d, CDR.Result1, CDR.Result2) as 'DaysofResult'
from	(select case when t_fdat>='3-1-2007' then t_fdat else '3-1-2007' end 
			as 'Result1', 
		case when '3-30-2007'>=t_tdat then t_tdat else '3-30-2007' end 
			as 'Result2'	
	from terext405100
	where (t_fdat<='3-1-2007' or t_fdat<='3-30-2007') and
	(t_tdat>='3-1-2007' or t_tdat>='3-30-2007'))CDR

Not knowing anybetter I think we are all coming up with the same result, i think.

Lamprey,
You added +1 days but got the results. How did that happen? Is there a right or wrong way?

Thanks again so much.

Card Gunner

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-11 : 16:39:22

I added one because when doing the date calculation it was off by one. For example if you subtract the 1st from the 31st, you get 30 days. I would assume that you want the answer to be 31 (inclusive).

cardgunner

326 Posts

Posted - 2007-06-12 : 11:26:03

Lamprey,
In this case it is not. But I get what you where doing. Thanks again.

Card Gunner

cardgunner

326 Posts

Posted - 2007-06-12 : 11:27:28

Is there anything wrong, techniclly with my approach?

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources