need to help query in sql server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have a question about SQL Server

Table : patient

dos             |pn     |code   |servicecode
2015-09-21      |10     |4      |90
2015-10-06      |10     |4      |91
2015-09-04      |10     |4      |92
2015-04-07      |11     |5      |80
2015-04-07      |11     |5      |94
2015-04-08      |11     |5      |94
2015-02-02      |12     |6      |96
2015-02-02      |12     |6      |97
2015-07-21      |12     |6      |94
2012-11-07      |13     |7      |93
2012-11-07      |13     |7      |94
2012-11-07      |13     |7      |99
2013-10-07      |13     |7      |90
2014-11-09      |13     |7      |98
2014-12-17      |13     |7      |90

Table 2: Patientrefs

Pn    |  code    | sdate      |  edate 
10    |   4      | 2015-09-04 | 2015-10-05
11    |   5      | 2015-04-01 | 2015-09-21
12    |   6      | 2015-02-02 | 2015-10-12
12    |   6      | 2014-04-20 | 2014-05-23
13    |   7      | 2012-11-05 | 2015-05-20

Table 3: PatientService

Servicecode

90
94

Here patient and patientrefts common columns Pn + code if pn+ code combination matches then we look into min(dos) after that patient table servicecode look into patientservice table service code then that combination matches then we consider dos is minimumdos

Based on above tables I want output like below

pn    | code   | mindos
11    | 5      | 2015-04-07
13    | 7      | 2012-11-07

Here pn=10 and pn=12 do not have mindos because of min(dos) related service code not satisfy with patientservice table service code
ere mainly we need to consider dos foll between sdate and edate.if satisfy then we go min(dos)in patient table then
that records corresponding servicecode is match with patientservice table if not matche then there is no mindos of that patient.
example:

Patient 
dos        |pn |code  |servicecode 
2015-09-21 |10 |4     |90 
2015-10-06 |10 |4     |91 
2015-09-04 |10 |4     |92 

Patientrefs
Pn | code | sdate      | edate 
10 | 4    | 2015-09-04 | 2015-10-05

here patient table dos foll between sdate and edate of patientrefs table then we will consider min(dos) in patient table that time min(dos) is 2015-09-04 |10 |4 |92 then
we look in patientservice table servicecode match with patien table servicecodethat time records is not match so pn=10 donot have mindos similar way pn:12

I tried like this

SQL

select
    pn, code, dos as mindos
from
    (select
         pn, code, servicecode
     from
         (select
              pn, code, servicecode,
              row_number() over (partition by pn, code order by dos) as rn
          from patient) a
     inner join
          patientrefs b on a.pn = b.pn and a.code = b.code and b.rn = 1
                        and a.dos between b.sdate and b.edate) as mindos
inner join
    patientservic c on mindos.servicecode = c.servicecode

But above query is not returning the expected result accurately.

Please tell me how to write query to achieve this task in SQL Server

Posted 17-Nov-15 21:32pm

baluchinti

Updated 18-Nov-15 6:34am

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Answer 1 · 2015-11-17T23:43:00

I have run your query on the dummy data supplied and it generates a few error messages:

Invalid column name 'rn'.
Invalid column name 'dos'.
Invalid column name 'dos'.
Ambiguous column name 'pn'.
Ambiguous column name 'code'.

On fixing these errors the query returned the following:

pn	code	mindos
11	5	2015-04-07

The reason the following record is not being returned

pn    | code   | mindos
13    | 7      | 2012-11-07

is due to the way these two records are being sorted in the row_number() over part of the query

dos             |pn     |code   |servicecode
2012-11-07      |13     |7      |94
2012-11-07      |13     |7      |99

The record with the servicecode = 99 is coming in first and the join to PatientService does not have service record 99, so the expected record is not returned.

To get the result you desire, the query can be modified to
1. take into consideration of the servicecode in the order by for min dos
or
2. filter the patient table with the PatientService initially and then do the min dos order by

Hope that helps you on your way.
If you need anymore help, fix the initial errors stated in the error messages and then just make a comment.
But if you have having issues with the initial errors, I can help you out with those too.
---update---

SQL

--here is your supplied query, modified to get your desired results
select
    pn, code, dos as mindos
from
    (select
         a.pn, a.code, servicecode, a.dos
     from
         (select
              pn, code, servicecode, dos, 
              row_number() over (partition by pn, code order by dos, servicecode) as rn
          from patient) a
     inner join
          patientrefs b on a.pn = b.pn and a.code = b.code and a.rn = 1
                        and a.dos between b.sdate and b.edate) as mindos
inner join
    PatientService c on mindos.servicecode = c.servicecode

SQL

--here is an additional query which will get the required results.
select 
	* 
from(	
	select 
	row_number() over (partition by pn, code order by dos, servicecode) as rn,
	* 
	from Patient) p
inner join Patientrefs pr
	on p.pn = pr.pn and p.code = pr.code
inner join PatientService ps
	on p.servicecode = ps.Servicecode
where p.rn = 1
and p.dos between pr.sdate and pr.edate
;

--update -- one way of putting a type of ordering to the servicecode can be done by modifying the sub-query portion of the query (row_number() over of Patient).
--something like this, the servicecodeOrder to be taken as 999 if not in PatientService table.
--you will need to define your correct ordering of servicecode 
(	
	select 
	row_number() over (partition by pn, code order by dos, isnull(ps.servicecode, 999)) as rn,
	p.*,
	isnull(ps.servicecode, 999) servicecodeOrder
	from Patient p
	left join PatientService ps
		on p.servicecode = ps.servicecode
) p

They should both work, but you need to test it out.