January 28, 2010 at 9:43 am
Hi
I am looking to create a script that will add a columns to this table e.g. (Table shown with an example of the new field)
Person Start_Date End_Date PKID Days_Between_CEs Latest_Period_Of_CC
1041, 1982-12-01, 1988-05-17, 1, 0,1982-12-01,
1481, 1986-12-01, 1987-08-24, 2, 0, 1986-12-01,
1481, 1987-08-24, 1987-10-28, 3, 0, NULL,
1481, 1987-10-28, 1989-02-19, 4, 0,NULL,
2041, 1973-02-14,1974-01-04, 5,0, 1973-02-14,
2041, 1974-01-04, 1990-12-17, 6, 0, NULL,
2071,1981-08-10, 1981-09-09, 7, 7,1981-09-16,
2071, 1981-09-16, 1981-10-06, 8, 0, NULL,
2071, 1981-10-06, 1991-10-14, 9, 0, NULL,
2071,1991-10-14, 1992-07-20, 10 ,0,NULL,
31941, 1978-03-09, 1978-11-17 11, 0 1978-03-09,
31941, 1978-11-17,1979-04-07 12, 0,NULL,
31941, 1979-04-07, 1990-07-25 13, 0,NULL,
31991, 1988-09-19, 1989-01-18 14, 1757, 1994-09-01,
31991, 1993-11-10, 1994-01-25 15, 219,NULL,
31991, 1994-09-01, 1996-11-18 16, 0,NULL,
The new fields would be ‘‘Days_Between_Care_Episodes’’ (Days between CE’s)
The ‘‘Days_Between_Care_Episodes’’ must calculate how long each episode has lasted between the first end date and the start date of the next transaction, for example, for Person_ID 2071 the number of days in care between 09/09/1981 and 16/09/1981 is 7. I don’t count the 16/09/ as a day as they would have been discharged on this day. That’s why it’s 7 and not 8 days.
If there is no next transaction for the person “Days_Between_Care_Episodes” will be 0.
Here is the script to create the Sample table
CREATE TABLE #Care_Episodes
(
Person_ID INT NOT NULL ,
Start_Date DATETIME NULL ,
End_Date DATETIME NULL ,
PKID INT NULL ,
)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(1041, '1982-12-01', '1988-05-17', 1)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(1481, '1986-12-01', '1987-08-24', 2)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(1481, '1987-08-24', '1987-10-28', 3)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(1481, '1987-10-28', '1989-02-19', 4)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(2041, '1973-02-14', '1974-01-04', 5)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(2041, '1974-01-04', '1990-12-17', 6)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(2071, '1981-08-10', '1981-09-09', 7)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(2071, '1981-09-16', '1981-10-06', 8)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(2071, '1981-10-06', '1991-10-14', 9)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(2071, '1991-10-14', '1992-07-20', 10)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(31941, '1978-03-09', '1978-11-17', 11)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(31941, '1978-11-17', '1979-04-07', 12)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(31941, '1979-04-07', '1990-07-25', 13)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(31991, '1988-09-19', '1989-01-18', 14)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(31991, '1993-11-10', '1994-01-25', 15)
insert into #Care_Episodes (Person_ID, Start_Date, End_Date, PKID)
values(31991, '1994-09-01', '1996-11-18', 16)
Thank you for any help.
January 28, 2010 at 9:04 pm
select a.* ,b.Start_Date,datediff(day,a.End_Date,b.Start_Date)diff
from
#Care_Episodes a
left outer join
#Care_Episodes b
on
a.Person_ID = b.Person_ID
and
a.PKID = b.PKID-1
January 28, 2010 at 9:39 pm
as per requirment first and Last Records Records with 0 days.
SELECT Cd.*,DATEDIFF(dd,CD.End_Date,ISNULL(CE.Start_Date,CD.End_Date)) AS Day_DIFF FROM #Care_EpisodesCD
LEFT JOIN
#Care_EpisodesCE
ON
CD.PKID=CE.PKID-1AND
CD.Person_ID=CE.Person_ID
Regards,
Mitesh OSwal
+918698619998
January 28, 2010 at 9:55 pm
Hi Mitesh Oswal,
I expect this feed back from OP.
Moreover you just put the if null in the
isnull(datediff(day,a.End_Date,b.Start_Date),0/*any other values*/)diff
So that we have map other than zero in this place.
January 29, 2010 at 1:57 am
Thank you loads for these. Were going to have a go at these and let you know how we get on 😀
Debbie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply