locked
need to help query based on conditions in sql server RRS feed

  • Question

  • 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

    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
    ;

    But above query is not returning the expected result accurately.
    Please tell me how to write query to achieve this task in SQL Server
    Wednesday, November 18, 2015 6:12 PM

Answers

  • Hi vasuna,

    Based on my understanding, to get the expected output, you can reference the below sample.
    create table #patient
    (dos datetime,pn int,code int,servicecode int)
    
    insert  #patient
    values('2015-09-21',10,4,90)
    insert  #patient
    values('2015-10-06',10,4,91)
    insert  #patient
    values('2015-09-04',10,4,92)
    insert  #patient
    values('2015-04-07',11,5,80)
    insert  #patient
    values('2015-04-07',11,5,94)
    insert  #patient
    values('2015-04-08',11,5,94)
    insert  #patient
    values('2015-02-02',12,6,96)
    insert  #patient
    values('2015-02-02',12,6,97)
    insert  #patient
    values('2015-07-21',12,6,94)
    insert  #patient
    values('2012-11-07',13,7,93)
    insert  #patient
    values('2012-11-07',13,7,94)
    insert  #patient
    values('2012-11-07',13,7,99)
    insert  #patient
    values('2013-10-07',13,7,90)
    insert  #patient
    values('2014-11-09',13,7,98)
    insert  #patient
    values('2014-12-07',13,7,90)
    
    
    create table #PatientService
    (Servicecode int)
    
    insert #PatientService
    values(90)
    insert #PatientService
    values(94)
    
    
    create table #Patientrefs
    (pn int,code int,sdate datetime,edate datetime)
    
    insert #Patientrefs
    values(10,4,'2015-09-04','2015-10-05')
    insert #Patientrefs
    values(11,5,'2015-04-01','2015-09-21')
    insert #Patientrefs
    values(12,6,'2015-02-02','2015-10-12')
    insert #Patientrefs
    values(12,6,'2014-04-20','2014-05-23')
    insert #Patientrefs
    values(13,7,'2012-11-05','2015-05-20')
     
     ;WITH Cte AS(
    SELECT DISTINCT pn,code FROM #patient p
    WHERE EXISTS
    (
    SELECT * FROM #Patientrefs
    WHERE pn=p.pn and code=p.code
    	 AND p.dos BETWEEN sdate AND edate
    )
    )
    SELECT * FROM #patient p
    WHERE EXISTS 
    (
    SELECT 1 FROM Cte WHERE p.pn=pn and p.code=code
    )
    AND NOT EXISTS
    (
    SELECT 1 FROM #patient WHERE p.pn=pn and p.code=code AND dos<p.dos
    )
    AND EXISTS
    (
    SELECT 1 FROM #PatientService WHERE ServiceCode=p.ServiceCode
    )
    
    GO
    
    DROP TABLE #Patientrefs,#patient,#PatientService

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Marked as answer by Eric__Zhang Thursday, December 3, 2015 5:42 AM
    Thursday, November 19, 2015 7:45 AM

All replies

  • Your explanation is not clear.  Why is 10, 4 not returned but 11,5 and 13, 7 returned?

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, November 18, 2015 7:53 PM
  • create table #patient
    (dos datetime,pn int,code int,servicecode int)
    
    insert  #patient
    values('2015-09-21',10,4,90)
    insert  #patient
    values('2015-10-06',10,4,91)
    insert  #patient
    values('2015-09-04',10,4,92)
    insert  #patient
    values('2015-04-07',11,5,80)
    insert  #patient
    values('2015-04-07',11,5,94)
    insert  #patient
    values('2015-04-08',11,5,94)
    insert  #patient
    values('2015-02-02',12,6,96)
    insert  #patient
    values('2015-02-02',12,6,97)
    insert  #patient
    values('2015-07-21',12,6,94)
    insert  #patient
    values('2015-11-07',13,7,93)
    insert  #patient
    values('2015-11-07',13,7,94)
    insert  #patient
    values('2015-11-07',13,7,99)
    insert  #patient
    values('2013-10-07',13,7,90)
    insert  #patient
    values('2014-11-09',13,7,98)
    insert  #patient
    values('2014-12-07',13,7,90)
    
    
    create table #PatientService
    (Servicecode int)
    
    insert #PatientService
    values(90)
    insert #PatientService
    values(94)
    
    
    create table #Patientrefs
    (pn int,code int,sdate datetime,edate datetime)
    
    insert #Patientrefs
    values(10,4,'2015-09-04','2015-10-05')
    insert #Patientrefs
    values(11,5,'2015-04-01','2015-09-21')
    insert #Patientrefs
    values(12,6,'2015-02-02','2015-10-12')
    insert #Patientrefs
    values(12,6,'2014-04-20','2014-05-23')
    insert #Patientrefs
    values(13,7,'2012-11-05','2015-05-20')
    
    
    with x 
    as(
    select min (dos) as dos,servicecode  from (
    select pa.dos as dos,pa.pn as pn,pa.code as code,pa.servicecode as servicecode from #patient as pa ,#Patientrefs as  pat
      where pa.dos between  pat.sdate and  pat.edate        
      )t group by servicecode 
      ) select pat.pn ,pat.code,x.dos as mindos from x x,#PatientService pt,#patient as pat
          where x.servicecode=pt.servicecode
    	        and x.dos=pat.dos
    			and pat.servicecode=pt.Servicecode  
    
    
    
    
    
    pn	code	   mindos
    11	5	2015-04-07 00:00:00.000
    13	7	2013-10-07 00:00:00.000  


    in my insert min from pn 13 is 2013-10-07
    Wednesday, November 18, 2015 8:56 PM
  • Hi vasuna,

    Based on my understanding, to get the expected output, you can reference the below sample.
    create table #patient
    (dos datetime,pn int,code int,servicecode int)
    
    insert  #patient
    values('2015-09-21',10,4,90)
    insert  #patient
    values('2015-10-06',10,4,91)
    insert  #patient
    values('2015-09-04',10,4,92)
    insert  #patient
    values('2015-04-07',11,5,80)
    insert  #patient
    values('2015-04-07',11,5,94)
    insert  #patient
    values('2015-04-08',11,5,94)
    insert  #patient
    values('2015-02-02',12,6,96)
    insert  #patient
    values('2015-02-02',12,6,97)
    insert  #patient
    values('2015-07-21',12,6,94)
    insert  #patient
    values('2012-11-07',13,7,93)
    insert  #patient
    values('2012-11-07',13,7,94)
    insert  #patient
    values('2012-11-07',13,7,99)
    insert  #patient
    values('2013-10-07',13,7,90)
    insert  #patient
    values('2014-11-09',13,7,98)
    insert  #patient
    values('2014-12-07',13,7,90)
    
    
    create table #PatientService
    (Servicecode int)
    
    insert #PatientService
    values(90)
    insert #PatientService
    values(94)
    
    
    create table #Patientrefs
    (pn int,code int,sdate datetime,edate datetime)
    
    insert #Patientrefs
    values(10,4,'2015-09-04','2015-10-05')
    insert #Patientrefs
    values(11,5,'2015-04-01','2015-09-21')
    insert #Patientrefs
    values(12,6,'2015-02-02','2015-10-12')
    insert #Patientrefs
    values(12,6,'2014-04-20','2014-05-23')
    insert #Patientrefs
    values(13,7,'2012-11-05','2015-05-20')
     
     ;WITH Cte AS(
    SELECT DISTINCT pn,code FROM #patient p
    WHERE EXISTS
    (
    SELECT * FROM #Patientrefs
    WHERE pn=p.pn and code=p.code
    	 AND p.dos BETWEEN sdate AND edate
    )
    )
    SELECT * FROM #patient p
    WHERE EXISTS 
    (
    SELECT 1 FROM Cte WHERE p.pn=pn and p.code=code
    )
    AND NOT EXISTS
    (
    SELECT 1 FROM #patient WHERE p.pn=pn and p.code=code AND dos<p.dos
    )
    AND EXISTS
    (
    SELECT 1 FROM #PatientService WHERE ServiceCode=p.ServiceCode
    )
    
    GO
    
    DROP TABLE #Patientrefs,#patient,#PatientService

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Marked as answer by Eric__Zhang Thursday, December 3, 2015 5:42 AM
    Thursday, November 19, 2015 7:45 AM