SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 finding minimum and maximum dates between
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vishal_sql
Posting Yak Master

101 Posts

Posted - 06/16/2012 :  04:02:54  Show Profile  Reply with Quote

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
17681 Posts

Posted - 06/16/2012 :  05:20:31  Show Profile  Reply with Quote

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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/16/2012 :  05:27:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Vishal_sql
Posting Yak Master

101 Posts

Posted - 06/16/2012 :  08:48:03  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/16/2012 :  11:17:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/16/2012 :  11:17:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Vishal_sql
Posting Yak Master

101 Posts

Posted - 06/16/2012 :  14:16:06  Show Profile  Reply with Quote
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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/17/2012 :  03:34:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Vishal_sql
Posting Yak Master

101 Posts

Posted - 06/17/2012 :  03:58:37  Show Profile  Reply with Quote
Hi SwePeso,
Agreed. That works.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000