July 26, 2011 at 9:21 am
Hi All,
I have table below with three columns.
ClaimIDClaimStatusCodeStatusDate
11,Closed,2010-05-10 12:19:00.000
11,Open,2010-05-25 09:30:00.000
11,Closed,2011-06-01 00:00:00.000
11,Open,2011-06-10 00:00:00.000
22,Closed,2011-03-14 00:00:00.000
22,Open,2011-05-04 00:00:00.000
33,Closed,2007-12-19 17:19:00.000
33,Open,2007-12-24 12:07:00.000
33,Open,2008-09-08 15:36:00.000
44,Closed,2008-11-19 17:19:00.000
44,Open,2008-12-24 12:07:00.000
44,Closed,2009-07-28 15:36:00.000
For any ClaimID, if Open claim status is follwed by open, then second Open status row is not required. That is every time Closed is follwed by Open. Here Last row for ClaimID 33 with StatusDate 2008-09-08 15:36:00.000 is not required(highlighted bold) I tried using rownumber function, but no luck.
Answer should be
ClaimID ClaimStatusCode StatusDate
11, Closed, 2010-05-10 12:19:00.000
11, Open, 2010-05-25 09:30:00.000
11, Closed, 2011-06-01 00:00:00.000
11, Open, 2011-06-10 00:00:00.000
22, Closed, 2011-03-14 00:00:00.000
22, Open, 2011-05-04 00:00:00.000
33, Closed, 2007-12-19 17:19:00.000
33, Open, 2007-12-24 12:07:00.000
44,Closed,2008-11-19 17:19:00.000
44,Open,2008-12-24 12:07:00.000
44,Closed,2009-07-28 15:36:00.000
Any help is Appreciated,
Thanks in Advance.
Punia
July 26, 2011 at 9:41 am
Something like this?
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
GO
USE ProofOfConcept ;
GO
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
ClaimID TINYINT,
ClaimStatusCode CHAR(6),
StatusDate DATETIME) ;
INSERT INTO #T
(ClaimID,
ClaimStatusCode,
StatusDate)
VALUES (1,
'Open',
'1/1/11') ;
INSERT INTO #T
(ClaimID,
ClaimStatusCode,
StatusDate)
VALUES (1,
'Open',
'1/1/11 1 am') ;
INSERT INTO #T
(ClaimID,
ClaimStatusCode,
StatusDate)
VALUES (1,
'Closed',
'1/1/11 2 am') ;
INSERT INTO #T
(ClaimID,
ClaimStatusCode,
StatusDate)
VALUES (1,
'Open',
'1/1/11 3 am') ;
INSERT INTO #T
(ClaimID,
ClaimStatusCode,
StatusDate)
VALUES (1,
'Open',
'1/1/11 4 am') ;
DELETE FROM #T
WHERE ID IN (
SELECT T1.ID
FROM #T AS T1
CROSS APPLY (SELECT MIN(T2.StatusDate) AS NextStatusDate
FROM #T AS T2
WHERE T2.ClaimID = T1.ClaimID
AND T2.StatusDate > T1.StatusDate) AS NextRecord
INNER JOIN #T AS T3
ON T1.ClaimID = T3.ClaimID
AND NextRecord.NextStatusDate = T3.StatusDate
AND T1.ClaimStatusCode = T3.ClaimStatusCode) ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 12:03 pm
Keep in mind that your use of the word "last" in your problem description could be problematic. Look at the sample - how would one know which is the "last" record? If you are hoping for some natural order in the table, SQL honors no such thing, so you would need to have some formal column to help you determine what was entered "first" or "last".
You may need something like an auto-numbered (or identity as it is called in SQL) column to trace the order in which things were saved to the DB. Even then - if you ever do batch or bulk inserts, you may need to be careful to order if you want the identity assignment to be correct.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 26, 2011 at 12:45 pm
There's a StatusDate column that gives the sequence, Matt. That should be adequate for that need, without an identity column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 1:51 pm
Similar to what GSquared posted - I had the exact same situation recently with relation to Claim Data. I dumped data from multiple sources into a table variable with an identity column on it and then deleted the rows with a self join based on the rowid + 1 = rowid, the ClaimID = ClaimID and the claim status = claim status. Worked like a charm.
July 26, 2011 at 2:19 pm
GSquared (7/26/2011)
There's a StatusDate column that gives the sequence, Matt. That should be adequate for that need, without an identity column.
True. As long as the transactions and status are not coming in so fast that you'd run into sequencing problems, the statusdate as defined might be enough to do so. I'd just advocate thinking through any edge cases that might make that fail somehow (e.g., split paths one fast, one slow), then decide if it's good enough.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 26, 2011 at 2:37 pm
Matt Miller (#4) (7/26/2011)
GSquared (7/26/2011)
There's a StatusDate column that gives the sequence, Matt. That should be adequate for that need, without an identity column.True. As long as the transactions and status are not coming in so fast that you'd run into sequencing problems, the statusdate as defined might be enough to do so. I'd just advocate thinking through any edge cases that might make that fail somehow (e.g., split paths one fast, one slow), then decide if it's good enough.
Very true. But ID columns can run into similar problems depending on concurrency, locks, network latency, and other factors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply