Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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.
0
KCTechNet
Asked:
KCTechNet
  • 4
  • 2
1 Solution
 
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
 
LordKnightshadeCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now