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
 filter based on max(fromdate)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gagani
Yak Posting Veteran

94 Posts

Posted - 07/15/2013 :  09:54:29  Show Profile  Reply with Quote
for the following data,

uni_id person_id addr_id fromdate todate
1213289 582418 867308 2004-10-29 11:35:49.430 NULL
1668877 582418 562498 2007-12-10 00:00:00.000 NULL

1213285 582419 867318 2004-10-29 11:35:49.430 NULL
1668874 582419 562497 2008-12-10 00:00:00.000 NULL

1213286 582420 867376 2004-10-29 11:35:49.430 NULL
1668878 582420 562496 2006-12-10 00:00:00.000 NULL

1213280 582417 867365 2004-10-29 11:35:49.430 NULL
1668872 582417 562491 2004-12-10 00:00:00.000 NULL

1668811 582416 562411 2002-12-10 00:00:00.000 NULL

i need the get the ouput as

1668877 582418 562498 2007-12-10 00:00:00.000 NULL
1668874 582419 562497 2008-12-10 00:00:00.000 NULL
1668878 582420 562496 2006-12-10 00:00:00.000 NULL
1668872 582417 562491 2004-12-10 00:00:00.000 NULL
1668811 582416 562411 2002-12-10 00:00:00.000 NULL

I need to filter the rows based on todate=null and max(fromdate). could anyone please write the query for it.

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 07/15/2013 :  09:58:52  Show Profile  Reply with Quote

select *
from   yourtable
where  todate is null 
and    fromdate = (select max(fromdate) from yourtable)



KH
Time is always against us

Go to Top of Page

gagani
Yak Posting Veteran

94 Posts

Posted - 07/15/2013 :  10:10:06  Show Profile  Reply with Quote
My apologies. I did not put the question right I think.

If there are more than one row for any person_id, I need to filter the rows based on todate=null and max(fromdate).

from the table data given, person_id with 582418 is repeated twice, i need the get the row with todate is null and max(fromdate) for person_id.

similar is the case for other person_id's if there are any duplicates.

my required output is
1668877 582418 562498 2007-12-10 00:00:00.000 NULL
1668874 582419 562497 2008-12-10 00:00:00.000 NULL
1668878 582420 562496 2006-12-10 00:00:00.000 NULL
1668872 582417 562491 2004-12-10 00:00:00.000 NULL
1668811 582416 562411 2002-12-10 00:00:00.000 NULL


Howerver, your query returns one row with max(fromdate) and todate is null.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 07/15/2013 :  10:13:30  Show Profile  Reply with Quote

select *
from
(
    select *, rn = dense_rank() over(partition by person_id order by fromdate desc)
    from   yourtable
) d
where d.rn = 1
and   d.todate is null



KH
Time is always against us

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