January 24, 2012 at 8:52 am
Hey guys...
Here is how my table looks:
SSN | Contract_Number | Effective Date | Term Date
111-11-1111 | 1 | 1-01-2012 | 4-01-2012
111-11-1111 | 2 | 5-01-2012 | 8-01-2012
111-11-1111 | 3 | 8-02-2012 | 11-02-2012
222-22-2222 | 1 | 2-23-2012 | 5-23-2012
222-22-2222 | 2 | 10-01-2012 | 1-01-2013
Im looking for how many days were between coverage for each contract for each ssn...For example, I need to know how many days were between the Term Date of Contract 1 and the Effective Date of Contract 2 for each SSN...I know I need to use the DATEDIFF function, but not sure how to set it up where it takes a date from 2 separate rows for each SSN...Can anyone help me out?
Thank you in advance!
January 24, 2012 at 8:58 am
Join the table to itself on SSN = SSN and Contract_Number = Contract_Number + 1. You can then use DATEDIFF to get the differences between the times.
John
January 24, 2012 at 9:04 am
Ya, what John said.
declare @t table (
ssn varchar(10),
[contract] int,
edate date,
tdate date
)
insert into @t values
('111-11-111', 1, '1-01-2012', '4-01-2012'),
('111-11-111', 2, '5-01-2012', '8-01-2012'),
('111-11-111', 3, '8-02-2012', '11-02-2012'),
('222-22-222', 1, '2-23-2012', '5-23-2012'),
('222-22-222', 2, '10-01-2012', '1-01-2013')
select t1.ssn, t1.contract FirstContract, t2.contract SecondContract, t1.tdate tdateFirst, t2.edate edateSecond,
DATEDIFF(dd, t1.tdate, t2.edate) DaysBetween
from @t t1
join @t t2 on t2.ssn = t1.ssn and t2.contract = t1.contract + 1
Edit: Assuming all your contracts are sequential numbers. If not, you'll probably have to use a CTE with row_number() partitioned on ssn and ordered by contract number or effective date.
January 24, 2012 at 9:17 am
Thank you so much...That worked to perfection!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply