Transactional data fun...

17 Dec 2008 7:05 AM

GrandNagel

I have a series of records that relate to various people in another
table. These records are a compilation of an individuals status over
time. Whats the sql to obtain the most recent status given the
following criteria..

It has to have the most recent StatusDate AND TransDate and must be of
type enrolled or dis-enrolled if one exists for that ID.

ID status statusDate Transdate

x111 X 1/4/2008 1/20/2008 3:30:34.456
x111 D 1/3/2008 1/20/2008 3:30:34.456
x111 E 1/2/2008 1/10/2008 6:30:34.456
x111 D 1/1/2008 1/10/2008 6:30:34.456
x222 D 1/2/2008 1/20/2008 7:30:34.999
x222 E 1/1/2008 1/20/2008 7:30:34.999
x333 X 1/1/2008 1/20/2008 8:30:34.999

result
x111 D 1/3/2008 1/20/2008 3:30:34.456
x222 D 1/2/2008 1/20/2008 3:30:34.999
x333 X 1/1/2008 1/20/2008 3:30:34.999

I hope thats enough of an example to express the idea...

I've been trying to write a stmt for about 2 days and no luck...

I've seen the patterns for dealing with one date as criteria for the
max(statusdate) but adding another date and then on top of that
selecting for E or D first over any other value has got me looped...

I'll be greatful for any help...

thx.
D.

17 Dec 2008 7:39 AM

Plamen Ratchev

Replied in 'microsoft.public.sqlserver.programming', copy/paste here:

It is not clear from your example what are the rules for the two dates.
Assuming you consider the most recent from the two dates will be used to
determine the latest, on SQL Server 2005/2008 you can use the following
query. Also, in your result set you have status 'X', and the
requirements state it should be only 'E' or 'D'.

SELECT CustID, Status, StatusDate, transDate
FROM (SELECT CustID, Status, StatusDate, transDate,
              ROW_NUMBER() OVER(PARTITION BY CustID
                                ORDER BY CASE WHEN StatusDate > transDate
                                              THEN StatusDate
                                              ELSE transDate
                                         END DESC) AS rk

       FROM trans
       WHERE Status IN ('D', 'E')) AS T
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com