Solved

help with  sql - join problem?? sql 2008

Posted on 2013-01-17
7
364 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 39

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server encoding for storing Indian languages 9 38
Managing Columnstore Indexes 2 19
Sql Join Problem 2 33
Error running stored procedure 11 19
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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now