MS SQL

SELECT only the latest date yet within the defined period

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: May 2008

Posts: 4

Reputation: stonyheng is an unknown quantity at this point

Solved Threads: 0

stonyheng

Offline

Newbie Poster

SELECT only the latest date yet within the defined period

May 12th, 2008

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

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: SELECT only the latest date yet within the defined period

May 12th, 2008

hi stonyheng,

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM date_table x
   WHERE out_date = 
       (SELECT max(out_date) FROM date_table y WHERE x.name = y.name)
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

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: SELECT only the latest date yet within the defined period

May 12th, 2008

hi again,

this achieves it:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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 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

•

Join Date: May 2008

Posts: 4

Reputation: stonyheng is an unknown quantity at this point

Solved Threads: 0

stonyheng

Offline

Newbie Poster

Re: SELECT only the latest date yet within the defined period

May 13th, 2008

•

Originally Posted by tesuji

hi again,

this achieves it:
Help with Code Tags

sql Syntax (Toggle Plain Text)
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 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