Select based on various conditions 1

[dbomrrsm]

I have a table that has the following fields:

LINE - example data AB123 - varchar
DEPT - example data 2 - integer
PR - example data 125 - integer
ET - example data 03/12/2005 17:34:46 - datetime

There can be mulitiple distinct LINE,DEPT,PR but only one of these will have a null ET (end timestamp).

I need to do a select from the table based on a number of conditions:

The latest record for each distinct LINE,DEPT,PR

Where ET is null then I need that record

If ET is not null then I need the latest (max) ET date record out of a possible 2 or 3 for the distinct LINE,DEPT,PR.

Examples are:

LINE DEPT PR ET
1 AA104 2 314
2 AA104 2 314 12/03/2005 17:34:46
3 AA104 2 314 12/03/2005 17:34:47

in the above example record 1 is needed

LINE DEPT PR ET
1 AA158 2 314 03/12/2005 17:34:47
2 AA158 2 314 15/12/2005 14:21:03

in the case of the above record 2 is required (i.e. the latest ET)

Any help much appreciated.

TIA




[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[vongrunt]

How about replacing NULL value with 'big enough' value back and forth? For example:

select line, dept, pr, nullif(max(isnull(et, '01/01/2100')), '01/01/2100')
from blah
group by line, dept, pr

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[AtomicWedgie]

Hi,

maybe a bit dirty but I think this should also work

select line, dept, pr, et
from table
where et is null
union
select line, dept, pr, max(et)
from table
where line & dept & pr (if you have id, replace with id) not in (select distinct line & dept & pr
from table
where et is null)
group by line, dept, pr

Regards,

Atomic Wedgie

 

[dbomrrsm]

Thanks both

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]