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)
below conditions need to consider
1)patient patientid=patinetstatus patientid
2)patient status='1' and patinetstatus status='2'
3)and patient admitdate<= patientstatus startdate and patient admitdate-patientstatus startdate <=30 days
4)after above conditions
[if found single qualified records on patientstatus for one single patient record,then record consider as update in the filter and guid taken from patientstatus
[if found multiple qualified records on patientstatus for one single patient record,then select min of patientstatus record and then consdier records is update
and guid comes from patientstatus
[if found multiple qualified records on patient for one single patientstatus record,then select min of patient record and consider as update record and guid taken from
patientstatus table.
[if found multiple qualified records on patient for multiple qualified patientstatus records,then select min of patient and target record and guid taken from
patientstatus table.
All above conditions not qualified ,then insert patient records consider as insert and guid from patient table.
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.