help with sql - join problem?? sql 2008

I have a table with transactions.  I don't want to rely on the TransactionID to assume the order of records as things could be inserted in the wrong "order of events".
There are three types of transactions, 1=new, 2=change, 3=expiration.  This is regarding insurance policy changes.  

Lets say here are a few records.  I am going to keep the TrasactionIDs in order for the example to be clear, but again, I need to use dates instead in case some slipped in a change after they entered an expiration.

TransactionID    TypeFlag   PolicyNumber   EffectiveDate    ChangeDate   ExpireDate
1                         1             123                     1/1/2011          
2                         2             123                     1/1/2011             2/1/2011      
3                         2             123                     1/1/2011             3/1/2011
4                         3             123                     1/1/2011             5/1/2011          4/1/2011

I need to have something like

TransactionID  TransactionFromDate   TransactionToDate
1                       1/1/2011                       1/31/2011
2                       2/1/2011                      2/28/2011
3                       3/1/2011                       4/1/2011


Select *,  isNull(ChangeDate, EffectiveDate) as TransactionFromDate,
               isNull(nextRecord.ExpireDate, nextRecord.ChangeDate - 1) as TransactionToDate
Where FlagType In (1,2)

the issue being nextRecord/TransactionToDate

I tried different joins on a subQuery to get the nextRecord but they didn't work.  I tried using (Select blah blah) as TransactionToDate but that select statement didn't work either.
KCTechNetAsked:
Who is Participating?
 
LordKnightshadeConnect With a Mentor Commented:
I would do something like this (re-ordered some columns):

/* Holding Table */
DECLARE @TransList TABLE 
(
	TransactionID INT IDENTITY,
	PolicyNum VARCHAR(20),
	EffectiveDate DATE,
	ExpirationDate DATE,
	ChangeDate DATE,
	TypeFlag INT
)

/* Example Records */	
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', NULL, NULL, 1
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', NULL, '2/1/2011', 2
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', NULL, '3/1/2011', 2	
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', '4/1/2011', '5/1/2011', 3

/* Select by order of Endorsement Period */
SELECT B.TransactionID, B.PolicyNum, B.TypeFlag, 
	IsNull((CASE B.TypeFlag WHEN 3 THEN B.ExpirationDate ELSE B.ChangeDate END),B.EffectiveDate) AS EndorsementBegins,
	(CASE B.TypeFlag WHEN 3 THEN E.ExpirationDate ELSE DateAdd(Day,-1,E.ChangeDate) END) AS EndorsementEnds
FROM @TransList B
	LEFT JOIN @TransList E ON E.TransactionID = (SELECT MIN(TransactionID) FROM @TransList T WHERE T.PolicyNum = B.PolicyNum 
		AND IsNull(T.ChangeDate,T.EffectiveDate) > IsNull((CASE B.TypeFlag WHEN 3 THEN B.ExpirationDate ELSE B.ChangeDate END),B.EffectiveDate))
		

Open in new window


Good to see meet another Insurance IT junky :)

[CODE CORRECTED]
0
 
lcohanDatabase AnalystCommented:
" I don't want to rely on the TransactionID to assume the order of records as things could be inserted in the wrong "order of events".

Why not use a IDENTITY column to ensure the order of inserts?
0
 
KCTechNetAuthor Commented:
because they could have inserted out of order:

someone entered the expiration yesterday.  ID = 7
today, someone realized that there was a change to the policy they forgot to record, so they entered it today. They correctly used the ChangeDate of when the change occured (say last month).  ID = 8 since it was entered after yesterday's record.

The Identity correctly identifies the order that the records were inserted, but in this case, they were inserted out of order.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
KCTechNetAuthor Commented:
Wow, thank you for your quick and detailed response!!!

I run yours in Query Analyzer and get incorrect EndDate for TransactionID 2 and 3

I then changed it a bit since I don't need Type = 3 in B.  

Also, in E it will only have Type = 2 or 3 and there will ALWAYS be a change date (enforced at data entry screen) so cleaned that up so a little easier to read, but I still get incorrect End Dates
0
 
KCTechNetAuthor Commented:
sorry, forgot to re-post what I had changed...

/* Holding Table */
DECLARE @TransList TABLE
(
      TransactionID INT IDENTITY,
      PolicyNum VARCHAR(20),
      EffectiveDate DATEtime,
      ExpirationDate DATEtime,
      ChangeDate DATEtime,
      TypeFlag INT
)

/* Example Records */      
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', NULL, NULL, 1
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', NULL, '2/1/2011', 2
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', NULL, '3/1/2011', 2      
INSERT INTO @TransList (PolicyNum, EffectiveDate, ExpirationDate, ChangeDate, TypeFlag) SELECT '123', '1/1/2011', '4/1/2011', '5/1/2011', 3

/* Select by order of Endorsement Period */
SELECT B.TransactionID, E.TransactionID, B.PolicyNum, B.TypeFlag,
      IsNull(B.ChangeDate,B.EffectiveDate) AS EndorsementBegins,
      DateAdd(Day,(CASE B.TypeFlag WHEN 3 THEN 0 ELSE -1 END),IsNull(E.ExpirationDate,E.ChangeDate)) AS EndorsementEnds
FROM (Select * From @TransList where TypeFlag in (1,2)) as B
      LEFT JOIN (Select * From @TransList where TypeFlag in (2,3)) E ON E.TransactionID = (SELECT MIN(TransactionID) FROM @TransList WHERE PolicyNum = B.PolicyNum
            AND ChangeDate >= IsNull(B.ChangeDate,B.EffectiveDate))
0
 
LordKnightshadeCommented:
Thank you for the post back :)  I think you grabbed the code prior to my update to it which is why TransID 2/3 were pulling the wrong dates.  I would test it with the change of the ">=" to just ">" in the last condition of the join, I found that fixed it for me.
0
 
KCTechNetAuthor Commented:
that was it. thanks.

I just needed to make a slight change, looking at E.TypeFlag instead of B.TypeFlag and all was perfect.

THank you for all your time and commitment to this one :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.