Solved

help with  sql - join problem?? sql 2008

Posted on 2013-01-17
7
386 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

734 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