SELECT only the latest date yet within the defined period

[stonyheng]

Hi all experts,

Currently i have a problem coding in sql query and hope someone expert can answer me.

I have a table with the following values (simplified)

Name in_date out_date
able 12/03/2008 14/03/2008
bravo 10/02/2008 17/03/2008
able 18/03/2008 null

Ok so my result if i want to obtain results for a period from 01/03/2008 to 31/03/2008

able 18/03/2008
bravo 10/02/2008 17/03/2008

SO how do we code for this? thanx

[tesuji]

hi stonyheng,

a simple way to archive a result is a correlated select like this:

select * from date_table x
   where out_date = 
       (select max(out_date) from date_table y where x.name = y.name)

Unfortunately this does not work properly if you have periods where out_date is not set, that is where out_date is NULL. I will think it over how to factor in NULL values, maybe there is a solution with coalesce(out_date, NOW(*), out_date).

krs,
tesu

[tesuji]

hi again,

this achieves it:

with temp_table ( name, in_date, out_date )
  as ( select name, in_date, coalesce(out_date, '9090-09-09') 
     as out_date from date_table)
        select * from temp_table x
           where out_date = (select max(out_date) from temp_table y 
              where x.name = y.name)

With WITH clause table date_table will be duplicated and all null values of out_date replaced by funny date 9th September, 9090. Replacement is necessary because max() ignores NULL values. I didn't test it with MS SQL Server, but I know SQL Server 2005 also features WITH clause and coalesce function. Possibly date constant must be rewritten, or replace it by getdate().

You can give me a reply, if this also works on MS SQL Server 2005 properly.

krs,
tesu

[stonyheng]

hi again,

this achieves it:

with temp_table ( name, in_date, out_date )
  as ( select name, in_date, coalesce(out_date, '9090-09-09') 
     as out_date from date_table)
        select * from temp_table x
           where out_date = (select max(out_date) from temp_table y 
              where x.name = y.name)

With WITH clause table date_table will be duplicated and all null values of out_date replaced by funny date 9th September, 9090. Replacement is necessary because max() ignores NULL values. I didn't test it with MS SQL Server, but I know SQL Server 2005 also features WITH clause and coalesce function. Possibly date constant must be rewritten, or replace it by getdate().

You can give me a reply, if this also works on MS SQL Server 2005 properly.

krs, tesu

Hey tesu, thanx alot for the suggestion. Really appreciate that. Thank you. :)