November 22, 2015 at 11:02 am
I have a question about SQL Server.
Table patient:
create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19',100),
(1,10,'2015-02-19',101),
(1,10,'2015-02-19',102),
(2,10,'2015-02-12',101),
(2,10,'2015-02-13',102),
(2,10,'2015-02-14',103),
(3,10,'2015-02-15',103),
(3,10,'2015-02-18',104),
(3,10,'2015-02-26',105),
(4,10,'2015-01-26',105),
(5,10,'2013-05-24',100),
(5,10,'2013-05-14',101),
(5,10,'2013-05-14',102),
(5,10,'2013-03-22',103),
(5,10,'2013-05-14',105)
Table Patientref:
create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-13','2015-02-19',2),
(1,10,'2015-04-28','2015-05-08',4),
(2,10,'2015-02-08','2015-02-19',4),
(2,10,'2015-02-09','2015-02-19',2),
(2,10,'2015-02-10','2015-02-19',2),
(2,10,'2015-02-11','2015-02-18',1),
(3,10,'2015-02-10','2015-02-17',4),
(3,10,'2015-02-10','2015-02-17',3),
(3,10,'2015-02-11','2015-02-18',3),
(2,10,'2015-04-10','2015-05-19',1),
(3,10,'2015-02-11','2015-02-18',1),
(3,10,'2015-02-26','2015-03-18',1),
(4,10,'2015-04-30','2015-06-23',4),
(5,10,'2013-02-13','2013-02-13',4),
(5,10,'2013-02-13','2013-04-30',2),
(5,10,'2013-05-14','2013-05-31',2)
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Examples: patient
pn | code | date | doctorcode
2 | 10 |2015-02-12 | 101
2 | 10 |2015-02-13 | 102
2 | 10 |2015-02-14 | 103
Table : Patientref:
pn | code | sdate | edate | Status
2 | 10 |2015-02-08 | 2015-02-19 | 4
2 | 10 |2015-02-09 | 2015-02-19 | 2
2 | 10 |2015-02-10 | 2015-02-19 | 2
2 | 10 |2015-02-11 | 2015-02-18 | 1
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
Based on this, the desired output is below:
pn | code | date | doctorcode | sdate |edate |status
1 | 10 |2015-02-19 | 100 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 101 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 102 |2015-02-19 |2015-03-24 | 2
2 | 10 |2015-02-12 | 101 |2015-02-10 |2015-02-19 | 2
2 | 10 |2015-02-13 | 102 |2015-02-10 |2015-02-19 | 2
2 | 10 |2015-02-14 | 103 |2015-02-10 |2015-02-19 | 2
3 | 10 |2015-02-15 | 103 |2015-02-10 |2015-02-17 | 4
3 | 10 |2015-02-18 | 104 |2015-02-11 |2015-02-18 | 3
3 | 10 |2015-02-26 | 105 |2015-02-26 |2015-03-18 | 1
4 | 10 |2015-01-26 | 105 | | |
5 | 10 |2013-05-24 | 100 |2013-05-14 |2013-05-31 | 2
5 | 10 |2013-05-14 | 101 |2013-05-14 |2013-05-31 | 2
5 | 10 |2013-05-14 | 102 |2013-05-14 |2013-05-31 | 2
5 | 10 |2013-03-22 | 103 |2013-02-13 |2013-04-30 | 2
5 | 10 |2013-05-14 | 105 |2013-05-14 |2013-05-31 | 2
I tried it like this:
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
order by case when pr.status=2
then 1 when pr.status=4 then 2
when pr.status=3 then 3
when pr.status=1 then 4 end ,pr.sdate
)pr
or I tried like
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
left join (select pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] ,row_number()over (partition by pn,code,sdate
order by case when pr.status=2
then 1 when pr.status=4 then 2 when pr.status=3 then 3 when pr.status=1 then 4 end ,pr.sdate )as rno
from patientref pr )pr
on pr.pn=p.pn and pr.code=p.code and
pr.rno=1
and p.date between pr.sdate and pr.edate
even this query also not given expected result.I want get result using left join .please help it to resolve this issue in sql server.
please tell me how to write query to achive this task in sql server
November 23, 2015 at 6:29 am
This sounds somewhat like homework. But since you've posted what you've tried...
Break down your problem. Don't do it all at once.
Let's start with the first concern. Here we need consider patient dates that fall between sdate and edate of the patientrefs table. We know from this sentence that you need patient dates, and you need to compare them to the sdate and edate of the patient ref table.
Since you only want patient dates that are between the sdate and edate, write this code first. The DISTINCT keyword will assist you in filtering out any duplicates.
Once you have that code working, write a query that only searches for the highest status value for patients. Your comments about which is highest, doesn't make sense to me. I would think the order would be 4,3,2,1 or 1,2,3,4, not 2,4,3,1. Go back and review that for issues.
Once you have the highest status value query, you can use that as a subquery to join back to patientref to pull only those highest statuses from patientref then add your code for finding the patient dates to the query.
November 23, 2015 at 7:10 am
Hi , as per given status data we consider highest 2,4,3,1 only.here if we found multiple status values for same pn then we need to consider max(sdate).
please provide query to achieve this task in sql server
November 23, 2015 at 7:13 am
Sorry, I don't provide queries for homework. Please look at what I previously said about breaking down the problem and try it yourself.
November 23, 2015 at 7:55 am
From what I understood from your problem, it seems that you were really close to the solution. You need to use the APPLY approach because you need to correlate your query before defining the "last row". You just needed to order your sdate properly.
SELECT p.pn,
p.code,
p.[date],
p.doctorcode,
pr.sdate,
pr.edate,
pr.[status]
FROM patient p
OUTER APPLY (SELECT TOP 1
pr.sdate,
pr.edate,
pr.[status]
FROM patientref pr
WHERE pr.pn=p.pn
AND pr.code=p.code
AND p.date between pr.sdate and pr.edate
ORDER BY CASE WHEN pr.status=2 THEN 1
WHEN pr.status=4 THEN 2
WHEN pr.status=3 THEN 3
WHEN pr.status=1 THEN 4 END ,
pr.sdate DESC)pr;
And yes, I'm posting code because is basically the same code that you posted, reformatted. I just removed unused columns and added the DESC keyword.
November 23, 2015 at 8:16 am
Hi If I tested records for 1 or 2 patients that time execution taken less time only.when I ran 17 laksh + records to use outer apply that time its taking more than 7 hours .when i use left join that time few patient missing sdate and edate status values even condition is satisfy.outer apply logic execution time taking more.is it possible
to resolve use left join.if possible you please help query to get same result with left join.
November 23, 2015 at 8:27 am
baludw22 (11/23/2015)
Hi If I tested records for 1 or 2 patients that time execution taken less time only.when I ran 17 laksh + records to use outer apply that time its taking more than 7 hours .when i use left join that time few patient missing sdate and edate status values even condition is satisfy.outer apply logic execution time taking more.is it possibleto resolve use left join.if possible you please help query to get same result with left join.
Interesting....you posted your response to Luis's suggestion of outer apply just 21 minutes after his post yet you state it took 7 hours.
Can you please try to format the last part of your post more clearly? I can't make heads or tails of what you are trying to say there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 23, 2015 at 8:32 am
Sean Lange (11/23/2015)
Interesting....you posted your response to Luis's suggestion of outer apply just 21 minutes after his post yet you state it took 7 hours.
Good catch, Sean. I didn't even notice that discrepancy.
baludw22, when you ran Luis's code, what exactly happened in those 21 minutes?
November 23, 2015 at 8:36 am
outer apply logic already I tested since 7 hours back.that query execution still running.so I mention approximately time .
November 23, 2015 at 8:50 am
My guess is that you can't have the proper indexes for the query to run correctly. The query needs to sort the patientref each time, which is what might be killing the performance on this query.
Certainly, a LEFT JOIN might be better, but not because of the LEFT JOIN, but because you'll end up sorting just once. This is, however, open to a problem made by the additional rows returned and later discarded. As I told you, to get the expected results, you need to relate the tables before choosing the appropriate row.
WITH CTE AS(
SELECT p.pn,
p.code,
p.[date],
p.doctorcode,
pr.sdate,
pr.edate,
pr.[status],
ROW_NUMBER() OVER( PARTITION BY p.pn,
p.code,
p.[date],
p.doctorcode
ORDER BY CASE pr.status
WHEN 2 THEN 1
WHEN 4 THEN 2
WHEN 3 THEN 3
WHEN 1 THEN 4 END,
pr.sdate DESC) rn
FROM patient p
LEFT JOIN patientref pr ON pr.pn=p.pn
AND pr.code=p.code
AND p.date between pr.sdate and pr.edate
)
SELECT pn,
code,
[date],
doctorcode,
sdate,
edate,
status
FROM CTE
WHERE rn = 1;
Another solution, would be to create an index for this query. This will come with an overhead for any DML against this table (INSERT, DELETE, UPDATE, MERGE).
ALTER TABLE patientref ADD statusorder AS CASE status WHEN 2 THEN 1
WHEN 4 THEN 2
WHEN 3 THEN 3
WHEN 1 THEN 4 END;
--Change the index name
CREATE INDEX IX_patientref_test ON patientref(statusorder, sdate DESC) INCLUDE(edate,[status], pn, code);
November 23, 2015 at 10:29 am
Hi Thank you very much. left join logic given good performance also to me.
all most very close to expected result.
I have small doubt in patient table: example :
pn|code|date |doctorcode
2|10|2015-02-19 |101
2|10|2015-02-19 |101
in the above records have duplicate data in patient table.
I want output both records. as per requirement we need duplicate data also.
is it possible or not.
pn|code|date |doctorcode|sdate|edate |status
1|10|2015-02-19|100 |2015-02-19|2015-03-24 |2
1|10|2015-02-19|100 |2015-02-19|2015-03-24 |2
can you please help query to solve this issue.
November 23, 2015 at 12:10 pm
You could use RANK instead of ROW_NUMBER. That might lead to a different problem when you have duplicate rows in patientref.
If you have an additional column that deduplicates the row, just include it in the PARTITION BY and leave the ROW_NUMBER.
If you don't have one, you'll need to generate one on the fly. That will be expensive, but might be the only choice.
November 23, 2015 at 7:27 pm
Thank you.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply