finding minimum and maximum dates between

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

General SQL Server Forums

New to SQL Server Programming

finding minimum and maximum dates between

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-06-16 : 04:02:54

Hi friends,

I need a help in finding minimum and maximum dates between @startdate and @enddate

Given table has below rows,

Resid orgid orgID_effective_date orgID_expiration_date
Reso1 101 2010-01-01 2010-05-01
Reso1 102 2010-05-02 2010-06-04
Reso1 103 2010-06-05 2100-01-01

Now if @startdate='2010-05-06'
and @enddate='2010-06-30'

expected result should be
( not necessary to have orgid in result we need to get new effective_date and expiration_date from below result)

Resourceid orgID_effective_date orgID_expiration_date
Reso1 2010-05-02 2100-01-01

because
2010-05-02 < '2010-05-06' (@startdate)
and 2100-01-01 > '2010-06-30'(@enddate)

I tried it by subquery and min caluse but didnt work for all scenario.
help will be highly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-16 : 05:20:31

[code]
select distinct Resourceid,
(select max(effective_date) from tbl x where x.Resourceid = t.Resourceid and x.effective_date < @startdate),
(select min(expiration_date) from tbl x where x.Resourceid = t.Resourceid and x.effective_date < @enddate)
from tbl t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-16 : 05:27:36

SELECT ResourceID, MIN(CASE WHEN Effective_Date < @StartDate THEN Effective_Date ELSE NULL END),
MAX(CASE WHEN Expiration_Date > @EndDate THEN Expiration_Date ELSE NULL END)
FROM dbo.Table1
GROUP BY ResourceID

N 56°04'39.26"
E 12°55'05.63"

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-06-16 : 08:48:03

Hi friends,
Thanks Khtan and Swepeso both for your reply.It helped me to move to get the expected output.

I made a small change in khtan's query to get the expected result

select distinct Resourceid,
(select max(orgID_effective_date) from tbl x where x.Resourceid = t.Resourceid and x.orgID_effective_date < @startdate),
(select min(orgID_expiration_date) from tbl x where x.Resourceid = t.Resourceid and x.orgID_expiration_date > @enddate)
from tbl t

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-16 : 11:17:28

SELECT ResourceID, MAX(CASE WHEN Effective_Date < @StartDate THEN Effective_Date ELSE NULL END), MIN(CASE WHEN Expiration_Date > @EndDate THEN Expiration_Date ELSE NULL END)FROM dbo.Table1GROUP BY ResourceID

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-16 : 11:17:41

N 56°04'39.26"
E 12°55'05.63"

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-06-16 : 14:16:06

Hi SwePeso,
Thanks, Appreciate your reply .
I added one more condition in CASE to get exact output if @startdate and @enddate matches the orgID_effective_date and orgID_expiration_date

SELECT ResourceID
, MAX(CASE WHEN orgID_effective_date < @StartDate THEN orgID_effective_date WHEN orgID_effective_date = @StartDate THEN @StartDate ELSE NULL END)
, MIN(CASE WHEN orgID_expiration_date > @EndDate THEN orgID_expiration_date WHEN orgID_expiration_date = @EndDate THEN @EndDate ELSE NULL END)
FROM Table1 GROUP BY ResourceID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-17 : 03:34:08

Why?
You are doing thing the most complicated way I can think of.

SELECT ResourceID
, MAX(CASE WHEN orgID_effective_date <= @StartDate THEN orgID_effective_date ELSE NULL END)
, MIN(CASE WHEN orgID_expiration_date >= @EndDate THEN orgID_expiration_date ELSE NULL END)
FROM Table1
GROUP BY ResourceID

N 56°04'39.26"
E 12°55'05.63"

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-06-17 : 03:58:37

Hi SwePeso,
Agreed. That works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources