[RESOLVED] Null date or max end date in query

[Tom Sawyer]

I have a feeling that this should be a simple query, but I'm having a brain fart or something and can't figure out how to do it.

I have a table called movement, which has two fields called mov_end_dt and mov_org_cd. The mov_org_cd has multiple records in the table and I want to pick out the one that either has a null value in the mov_end_dt or has the latest date in that field.

For instance, say the data in the table is:

mov_org_cd    mov_org_dt
A00001        1/1/2008
A00001        2/1/2008
A00001        3/1/2008

A00002        4/1/2008
A00002        5/1/2008
A00002        NULL

The query should return 3/1/2008 for A00001 and NULL for A00002.

Can anyone give me a hand on how to pull that out? If a subquery is needed, each record does have an id field as a primary key as well.

[FunkyDexter]

I think this will give you what you want. I haven't syntax checked it but the principle are sound:-
select distinct mov_org_cd,
(Select top 1 mov_end_dt
From Movement as innerMovement
Where innerMovement.mov_org_cd = Movement.mov_org_cd
Order By case when innerMovement mov_end_dt is null then 0 else 1 end,
innerMovement.mov_end_dt desc)
From Movement

Basically, the case statement on the order by of the inner query is just there to shove nulls to the top and the inner query itself will then select the topmost date value for each given mov_org_cd.

I've used the distinct keyword as a quick and dirty way of eliminating duplicate but yuou should probably use group by mov_org_cd in the outer query instead.

[Tom Sawyer]

Thanks for the help. I actually ended up getting it working using a similar idea:

SELECT mov.*
FROM movement mov
    WHERE mov.mov_org_cd = @agt_code
        AND ISNULL(mov.mov_end_dt, '12/31/9999') = 
            (SELECT MAX(ISNULL(mov_end_dt, '12/31/9999'))
                FROM movement
                WHERE mov_org_cd = mov.mov_org_cd)

I'm not really sure if that's efficient or not, but it's getting me the data I need.