I want to display same result set as combined for Two different Criteria on Max Id in SQL server.
Can someone kindly help with query.
Thank you very much in advance
Data Table is given below:
| Id | Hospital | RecNum | FUpTime | FollowUpDate | FUType | Diagnosis | Loction |
|---|---|---|---|---|---|---|---|
| 11 | x | 12c | 6 Weeks | 14/03/2017 | Call | Side Effects | 1 |
| 18 | y | 16a | 6 Weeks | 14/03/2017 | Call | No Complaints | 2 |
| 26 | x | 12c | 6 Weeks | 10/09/2018 | Clinic | Side Effects | 1 |
| 27 | y | 16a | 6 Weeks | 22/11/2018 | Clinic | No Complaints | 2 |
| 31 | y | 16a | 6 Months | 15/06/2019 | Call | No Complaints | 1 |
| 33 | x | 12c | 6 Months | 10/03/2020 | Clinic | No Complaints | 1 |
| 37 | z | 17q | 6 Months | 10/03/2021 | Call | Side Effects | 1 |
required Data must be
| 6 Weeks | 6 Months | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| RecNum | Hospital | Loction | FUpTime | FollowUpDate | FUType | Diagnosis | FUpTime | FollowUpDate | FUType | Diagnosis |
| 12c | x | 1 | 6 Weeks | 10/09/2018 | Clinic | Side Effects | 6 Months | 10/03/2020 | Clinic | No Complaints |
| 16a | y | 2 | 6 Weeks | 22/11/2018 | Clinic | No Complaints | 6 Months | 15/06/2019 | Call | No Complaints |
| 17q | z | 1 | NULL | NULL | NULL | NULL | 6 Months | 10/03/2021 | Call | Side Effects |
Or required Data set B
| RecNum | Hospital | Loction | FUpTime | FollowUpDate | FUType | Diagnosis |
|---|---|---|---|---|---|---|
| 12c | x | 1 | 6 Weeks | 10/09/2018 | Clinic | Side Effects |
| 12c | x | 1 | 6 Months | 10/03/2020 | Clinic | No Complaints |
| 16a | y | 2 | 6 Weeks | 22/11/2018 | Clinic | No Complaints |
| 16a | y | 2 | 6 Months | 15/06/2019 | Call | No Complaints |
| 17q | z | 1 | NULL | NULL | NULL | NULL |
| 17q | z | 1 | 6 Months | 10/03/2021 | Call | Side Effects |
Mario ScheerenPosted Apr 1, 2021, 6:23 PM
, t1.Hospital
, t1.Loction
, t1.FUpTime
,t1.FollowUpDate
, t1.FUType
, t1.Diagnosis
,t2.FUpTime
,t2.FollowUpDate
, t2.FUType
, t2.Diagnosis
FROM Table t1
JOIN (
SELECT RecNum, FUpTime,FollowUpDate, FUType, Diagnosis
FROM Table-
WHERE FUpTime = "6 Month"
) as t2