July 10, 2012 at 3:00 pm
I know my description is not clear but I will try to explain and give the tables and data so hopefully that will help. I have a table of transactions for a claim. One claim will have multiple transactions. I am trying to determine the status of each claim from the time it was billed until the end or until it gets rebilled.
Here is the table
CREATE TABLE [dbo].[ClaimTransaction](
[ClaimTransactionID] [int] IDENTITY(1,1) NOT NULL,
[ClaimTransactionTypeCode] [char](3) NOT NULL,
[ClaimID] [int] NOT NULL,
[PracticeID] [int] NULL,
[PostingDate] [datetime] NOT NULL,
,
CONSTRAINT [PK_ClaimTransaction] PRIMARY KEY NONCLUSTERED
(
[ClaimTransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Insert Into ClaimTransaction (10331093,'EDI',113044,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10335939,'EDI',113044,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10329194,'BLL',113044,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10329161,'RAS',113044,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10335940,'RJT',113044,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10350005,'RAS',113044,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10350006,'BLL',113044,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10350594,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10354039,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10355835,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10356316,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10379129,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10329198,'BLL',194860,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10329157,'RAS',194860,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10331094,'EDI',194860,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10335941,'EDI',194860,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10335942,'RJT',194860,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10350595,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10354040,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10355836,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10356317,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10379251,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10350004,'RAS',194860,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10350007,'BLL',194860,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10329202,'BLL',278889,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10329151,'RAS',278889,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10331099,'EDI',278889,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10335951,'EDI',278889,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10335952,'RJT',278889,2,'Jun 7 2012 12:00AM')
Insert Into ClaimTransaction (10350596,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10354041,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10355837,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10356318,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10379236,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10350001,'RAS',278889,2,'Jun 8 2012 12:00AM')
Insert Into ClaimTransaction (10350008,'BLL',278889,2,'Jun 8 2012 12:00AM')
INSERT INTO #ClaimsBilled
SELECT Distinct PracticeId, ClaimId, ct.ClaimTransactionID
FROM ClaimTransactions AS ct
WHERE ct.PostingDate BETWEEN '6/3/2012' AND '6/09/2012'
ORDER BY ClaimId
So I need to get the Max(ClaimTransactionId) AFter the 'BLL' Transaction before the next BLL Transaction if there is one.
So I started out with finding all claims that have multiple billings and I think I probably need to loop through each one. But I am drawing a blank on where to begin.
So what I basically want is for each claim:
ClaimID PostingDate for BLL, ClaimTransactionTypeCode for that Bll, So I know that the Bll was Rejected or something else.
So My query looks something like this, but it doesn't count for multiple billings
SELECT ct2. PracticeID, sub.CTID, ct2.ClaimID, ct2.ClaimTransactionTypeCode, ct2.PostingDate
FROM (
SELECT ct.ClaimId,MAX(ct.ClaimTransactionID)AS CTID
FROM ClaimTransaction AS ct
INNER JOIN #ClaimsBilled AS cb ON ct.ClaimID = cb.ClaimID AND ct.PracticeID = cb.PracticeID
WHERE ct.ClaimTransactionID>cb.ClaimTransactionID AND ct.PostingDate BETWEEN '6/3/2012' AND '6/16/2012'
GROUP BY ct.ClaimId)sub
INNER JOIN ClaimTransaction AS ct2 ON sub.CTID=ct2.ClaimTransactionID
Please let me know if this makes sense.
July 10, 2012 at 3:18 pm
Can you also show us the expected results out of the sample data posted?
July 10, 2012 at 3:19 pm
And what if there are more than 2 rows for TrasactionTypeCode = "BLL" ?? How would that change ur expected result?
July 10, 2012 at 3:22 pm
Sure
PracticeIDCTIDClaimIDFinalTransactionPostingDate
210380821427853RJT2012-06-12 00:00:00.000
210387292427884RJT2012-06-03 00:00:00.000
210387296427884RAS2012-06-08 00:00:00.000
210387299427884EDI2012-06-12 00:00:00.000
210387293427885EDI2012-06-12 00:00:00.000
210379221427890EDI2012-06-08 00:00:00.000
210379222427892EDI2012-06-08 00:00:00.000
210373004428001EDI2012-06-11 00:00:00.000
210373006428002EDI2012-06-11 00:00:00.000
210373008428003EDI2012-06-11 00:00:00.000
210373010428004EDI2012-06-11 00:00:00.000
210379224428014EDI2012-06-08 00:00:00.000
210379225428015EDI2012-06-08 00:00:00.000
210380823428061RJT2012-06-12 00:00:00.000
210380825428065RJT2012-06-12 00:00:00.000
210380827433566RJT2012-06-12 00:00:00.000
210387447437805EDI2012-06-12 00:00:00.000
So for Claim 472884 There were say 3 billed transactions so I see that claim 3 times with the posting date and what the transactions code was.
July 10, 2012 at 3:35 pm
I am kinda confused. Can you please let me know the expected reuslt form the sample data you posted in the first post?
Also, 472884 does not have 3 BLL transactions; how did u arrive at your result?
July 10, 2012 at 3:46 pm
Try this instead. The 3 billed don't actually exist. I was just showing what I would want it to look like.
CREATE TABLE [#ClaimTransaction](
[ClaimTransactionID] [int] NOT NULL,
[ClaimTransactionTypeCode] [char](3) NOT NULL,
[ClaimID] [int] NOT NULL,
[PracticeID] [int] NULL,
[PostingDate] [datetime] NOT NULL)
Insert Into #ClaimTransaction values(10331093,'EDI',113044,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10335939,'EDI',113044,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10329194,'BLL',113044,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10329161,'RAS',113044,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10335940,'RJT',113044,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10350005,'RAS',113044,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10350006,'BLL',113044,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10350594,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10354039,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10355835,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10356316,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10379129,'EDI',113044,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10329198,'BLL',194860,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10329157,'RAS',194860,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10331094,'EDI',194860,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10335941,'EDI',194860,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10335942,'RJT',194860,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10350595,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10354040,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10355836,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10356317,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10379251,'EDI',194860,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10350004,'RAS',194860,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10350007,'BLL',194860,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10329202,'BLL',278889,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10329151,'RAS',278889,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10331099,'EDI',278889,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10335951,'EDI',278889,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10335952,'RJT',278889,2,'Jun 7 2012 12:00AM')
Insert Into #ClaimTransaction values(10350596,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10354041,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10355837,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10356318,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10379236,'EDI',278889,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10350001,'RAS',278889,2,'Jun 8 2012 12:00AM')
Insert Into #ClaimTransaction values(10350008,'BLL',278889,2,'Jun 8 2012 12:00AM')
SELECT *
FROM #ClaimTransaction AS ct
ORDER BY claimId, Postingdate
113044,RAS,6/7/2012
113044,EDI,6/8/2012
194860,RAS,6/7/2012
194860,NULL,6/8/2012
278889,RAS,6/7/2012
278889,Null,6/8/2012
July 11, 2012 at 6:32 am
Can you explain the logic for the second row in your expected output
113044,RAS,6/7/2012
113044,EDI,6/8/2012 -- should this not be NULL too, as there is no BLL after the BLL on 6/8/2012?
194860,RAS,6/7/2012
194860,NULL,6/8/2012
278889,RAS,6/7/2012
278889,Null,6/8/2012
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 11, 2012 at 9:34 am
I'm Looking for the last transaction after the BLL before the next BLL if there is one. So if there is no transaction after the BLL it should be null but in that case the last transaction was EDI.
July 12, 2012 at 1:56 am
pamozer (7/11/2012)
I'm Looking for the last transaction after the BLL before the next BLL if there is one. So if there is no transaction after the BLL it should be null but in that case the last transaction was EDI.
How do you decide which is the last transaction when you have records with same PostingDate and same ClaimID?
You can't take the last row inserted as the last transaction, you should probably take ClaimTransactionID into account
; WITH cte_ClaimTransaction AS
(
SELECTROW_NUMBER() OVER ( PARTITION BY CT.ClaimID ORDER BY CT.PostingDate DESC, CT.ClaimTransactionID DESC ) RN, *
FROM#ClaimTransaction AS CT
WHERECT.ClaimTransactionTypeCode = 'BLL'
)
SELECTcte_CT1.ClaimID, iTVF.ClaimTransactionTypeCode, CONVERT(VARCHAR(10),cte_CT1.PostingDate,101) AS PostingDate
FROMcte_ClaimTransaction AS cte_CT1
LEFT OUTER JOINcte_ClaimTransaction AS cte_CT2 ON cte_CT1.ClaimID = cte_CT2.ClaimID AND cte_CT1.RN = cte_CT2.RN - 1
CROSS APPLY(
SELECTTOP 1 *
FROM#ClaimTransaction AS CT
WHEREcte_CT1.ClaimID = CT.ClaimID AND CT.ClaimTransactionID < ISNULL( cte_CT2.ClaimTransactionID, 2147483647 )
ORDER BY CT.ClaimTransactionID DESC
) iTVF
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply