Comparing previous record for specific person

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing previous record for specific person

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-08 : 15:16:43

I have a table that tracks modifications to a person's contract.

tblContracts
record, memberID, contractID, createdDate, cancelDate, cancelReasonID
1, 0001, 30001, 3/1/2009, 5/8/2009, 1
2, 0002, 30023, 5/8/2009, null, null
3, 0001, 30025, 5/8/2009, null, null

In the above example you can see there two people.
Person 0001 has 2 different contracts: 30001 and 30025.
Person 0002 has 1 contract: 30023.

Both started today however the first had a previous contract.

I'd like to find the people who started on 5/8/2009 but whose previous contract (if available) does not have a CancelReasonID = 1.

In the example above Person 0001 would not show up but Person 0002 would.

The contractID is not incremented by one for each person so it's not like you can say "Compare Contract# to (Contract#-1)".

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-08 : 15:33:10

declare @table table (record int, memberID char(4), contractID int
, createdDate datetime, cancelDate datetime, cancelReasonID int)

INSERT INTO @table
SELECT 1, '0001', '30001', '3/1/2009', '5/8/2009', 1 UNION ALL
SELECT 2, '0002', '30023', '5/8/2009', null, null UNION ALL
SELECT 3, '0001', '30025', '5/8/2009', null, null

SELECT t2.*
FROM
(
select memberid,[CXLD] = max(canceldate)
from @table
group by memberid
having max(canceldate) is null
) t1
INNER JOIN
@table t2
ON
t1.memberid = t2.memberid

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:27:06

quote:
Originally posted by swims01

I have a table that tracks modifications to a person's contract.

tblContracts
record, memberID, contractID, createdDate, cancelDate, cancelReasonID
1, 0001, 30001, 3/1/2009, 5/8/2009, 1
2, 0002, 30023, 5/8/2009, null, null
3, 0001, 30025, 5/8/2009, null, null

In the above example you can see there two people.
Person 0001 has 2 different contracts: 30001 and 30025.
Person 0002 has 1 contract: 30023.

Both started today however the first had a previous contract.

I'd like to find the people who started on 5/8/2009 but whose previous contract (if available) does not have a CancelReasonID = 1.

In the example above Person 0001 would not show up but Person 0002 would.

The contractID is not incremented by one for each person so it's not like you can say "Compare Contract# to (Contract#-1)".

if sql 2005, use below


SELECT c.*
FROM tblContracts c
OUTER APPLY (SELECT TOP 1 contractID
             FROM tblContracts
             WHERE memberID=c.memberID
             AND createdDate < c.createdDate
             AND cancelReasonID IS NOT NULL
             ORDER BY createdDate DESC) c1
WHERE c1.contractID IS NULL


if earlier versions use this

SELECT t.*
FROM
(
SELECT c.*,(SELECT TOP 1 contractID
             FROM tblContracts
             WHERE memberID=c.memberID
             AND createdDate < c.createdDate
             AND cancelReasonID IS NOT NULL
             ORDER BY createdDate DESC) AS PrevContract
FROM tblContracts c
)t
WHERE PrevContract IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources