| Author |
 Topic  |
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
 Posted - 06/16/2012 : 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)
Singapore
16746 Posts |
Posted - 06/16/2012 : 05:20:31
|
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
KH
Time is always against us
|
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/16/2012 : 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" |
Edited by - SwePeso on 06/16/2012 05:29:07 |
|
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
Posted - 06/16/2012 : 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
Sweden
29138 Posts |
Posted - 06/16/2012 : 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
Sweden
29138 Posts |
Posted - 06/16/2012 : 11:17:41
|
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" |
|
|
|
Vishal_sql
Yak Posting Veteran
88 Posts |
Posted - 06/16/2012 : 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
Sweden
29138 Posts |
Posted - 06/17/2012 : 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
Yak Posting Veteran
88 Posts |
Posted - 06/17/2012 : 03:58:37
|
Hi SwePeso,
Agreed. That works. |
|
|
| |
Topic |
|
finding minimum and maximum dates between
