Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

help with  sql - join problem?? sql 2008

Posted on 2013-01-17
7
Medium Priority
?
413 Views
Last Modified: 2013-01-17
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
Comment
Question by:KCTechNet
  • 4
  • 2
7 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 38788741
" 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
 

Author Comment

by:KCTechNet
ID: 38788811
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
 
LVL 3

Accepted Solution

by:
LordKnightshade earned 2000 total points
ID: 38788874
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:KCTechNet
ID: 38788998
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
 

Author Comment

by:KCTechNet
ID: 38789008
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
 
LVL 3

Expert Comment

by:LordKnightshade
ID: 38789121
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
 

Author Comment

by:KCTechNet
ID: 38789685
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question