December 31, 2015 at 12:59 pm
HI I have two tables
table : patient
create table patient ( patientid int, admitdate date , status int , guid int ,flag int)
insert into patient (patientid,admitdate,status,guid,flag)
values
(1,'2013--09-25', 1 ,10,1),
(1,'2014--03-25', 1 ,11,2),
(1,'2014--04-09', 1 ,12,3),
(1,'2015--01-22', 1 ,13,4),
(2,'2015--12-13', 1 ,14,5),
(2,'2014--10-10', 1 ,15,6),
(3,'2015--10-11', 1 ,16,7),
(3,'2015--12-27', 1 ,17,8),
(3,'2015--01-01', 6 ,18,9),
(4,'2011--01-01', 1 ,19,10),
(4,'2011--01-01', 1 ,20,11),
table2: patientstatus
create table patientstatus ( patientid int , startdate date , status int ,guid int,check int)
insert into patientstatus (patientid ,startdate ,status ,guid,check)
values
(1 ,'2013-10-02',2,50,20),
(1 ,'2014-04-09',2,51,21),
(1 ,'2015-01-30',2,52,22),
(2 ,'2015-12-15',2,53,23),
(2 ,'2015-12-15',2,54,24),
(3 ,'2015-10-11',2,55,25),
(3 ,'2015-10-12',2,56,26),
(4 ,'2011-01-02',2,57,27),
(4 ,'2010-07-02',8,58,28)
both table have common column patientid.
few conditons need to satisafied to get output
1) patient table admitdate<= startdate from patientstatus
2) days differncebetween admitdate and startdate and days differnce must me less than equal to 30 days
if multiple records found in patient table then we take min(flag) priority value( order by flag values)
if multiple records found in patientstatus table then we take min(check) priority value( order by check values)
based on above two tables I want output like below.
patientid | admitdate | status | guid | Filter
1 | 2013--09-25 | 1 | 50 | update
1 | 2014--03-25 | 1 | 11 | insert
1 | 2014--04-09 | 1 | 51 | update
1 | 2015--01-22 | 1 | 52 | update
2 | 2015--12-13 | 1 | 53 | update
2 | 2014--10-10 | 1 | 15 | insert
3 | 2015--10-11 | 1 | 55 | update
3 | 2015--12-27 | 1 | 17 | insert
4 | 2011--01-01 | 1 | 57 | update
4 | 2011--01-01 | 1 | 20 | insert
I tried like below:
SELECT p.patientid , p.admitdate, p.status,
CASE WHEN datediff(day, p.admitdate, ps.startdate) < 30
THEN 'Update'
ELSE 'Insert'
END AS filter,
CASE WHEN datediff(day, p.admitdate, ps.startdate) < 30
THEN ps.guid
ELSE p.guid
END AS guid
FROM (SELECT , row_number() OVER(PARTITION BY patientid ORDER BY flag) AS rowno
FROM patient) AS p
OUTER APPLY (SELECT TOP 1 ps.*
FROM patientstatus ps
WHERE ps.patientid=p.patientid
AND ps.startdate >= p.admitdate
AND ps.status = 2
ORDER BY ps.check) AS ps
WHERE p.status = 1
AND p.rowno = 1
but above query not given expected result. please tell me how to write query to achive this task in sql server
December 31, 2015 at 1:06 pm
seems very similar request to one posted earlier today
http://www.sqlservercentral.com/Forums/Topic1748862-3077-1.aspx
??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply