filter based on max(fromdate) - SQL Server Forums

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

filter based on max(fromdate)

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2013-07-15 : 09:54:29

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)

17689 Posts

Posted - 2013-07-15 : 09:58:52

[code]
select *
from yourtable
where todate is null
and fromdate = (select max(fromdate) from yourtable)
[/code]

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

gagani
Posting Yak Master

112 Posts

Posted - 2013-07-15 : 10:10:06

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-15 : 10:13:30

[code]
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
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources