t-SQL problem - combining two queries into one

Greetings experts,

I am trying to combine 2 SQL queries into 1.   I am giong to try and explain this as best as I can.   I have 2 different queries where one returns the count of patients and the other returns the count of deviations.  When I try to combine the 2 queries into one SQL statement I get multiples results for deviations.  The patients are unique, but each patient can have multiple deviations (probably why it gets mutiplied for me).  

The deviations SQL code is identiical to the patients query expect it adds a left outer join at the end to get the deviations (see attached).

I am looking for a way to combine these into one SQL statement.  Let me know what other info you need.  Thanks.
SQL-Count-patients.txt
SQL-CountDeviations.txt
PharmicaAsked:
Who is Participating?
 
SharathData EngineerCommented:
How are you trying to JOIN these two queries? chcek this one.
SELECT t1.stdinv_id,t1.Studyname,t1.Site_NO,t1.CRA,t1.Country,t1.CountDeviations,t2.CountPatients
  FROM (SELECT rds.stdinv_id,rds.Studyname,rds.Site_NO,rds.CRA,rds.Country,count(D.issue) as CountDeviations
          FROM prod_rdmetrics_sitecrastudy RDS 
          LEFT OUTER JOIN trialworksINO.dbo.tbl_patients P 
            ON rds.stdinv_id = p.stdinv_id
          LEFT OUTER JOIN trialworksINO.dbo.tbl_deviation D 
            ON p.patient_id = d.patient_Id and d.issue='violation'
         GROUP BY rds.stdinv_id,rds.studyname,rds.Site_NO,rds.CRA,rds.Country) t1
  JOIN (SELECT rds.stdinv_id,rds.Studyname,rds.Site_NO,rds.CRA,rds.Country,count(p.patient_Id)as CountPatients
          FROM prod_rdmetrics_sitecrastudy RDS 
          LEFT OUTER JOIN trialworksINO.dbo.tbl_patients P 
            ON rds.stdinv_id = p.stdinv_id
         GROUP BY rds.stdinv_id,rds.studyname,rds.Site_NO,rds.CRA,rds.Country) t2
    ON t1.stdinv_id = t2.stdinv_id AND t1.Studyname = t2.Studyname AND t1.Site_NO  = t2.Site_NO AND t1.CRA = t2.CRA
         

Open in new window

0
 
BrandonGalderisiCommented:
Try:
Select Distinct
rds.stdinv_id,
rds.Studyname,
rds.Site_NO,
rds.CRA,
rds.Country,
count(p.patient_Id)as CountPatients,
count(D.issue) as CountDeviations
 
From prod_rdmetrics_sitecrastudy RDS 
 left outer join trialworksINO.dbo.tbl_patients P 
   on rds.stdinv_id = p.stdinv_id
 left outer join trialworksINO.dbo.tbl_deviation D 
   on p.patient_id = d.patient_Id and d.issue='violation'
 
group by 
rds.stdinv_id,
rds.studyname,
rds.Site_NO,
rds.CRA,
rds.Country

Open in new window

0
 
Randy Wilson.Net DeveloperCommented:
What does this do?
 
Select Distinct
rds.stdinv_id,
rds.Studyname,
rds.Site_NO,
rds.CRA,
rds.Country,
count(D.issue) as CountDeviations, count(p.patient_Id) as CountPatients

From prod_rdmetrics_sitecrastudy RDS left outer join
trialworksINO.dbo.tbl_patients P on rds.stdinv_id = p.stdinv_id

left outer join
trialworksINO.dbo.tbl_deviation D on p.patient_id = d.patient_Id and p.issue='violation'

group by
rds.stdinv_id,
rds.studyname,
rds.Site_NO,
rds.CRA,
rds.Country
 
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Randy Wilson.Net DeveloperCommented:
Oops, looks like we came up with the same solution...
0
 
PharmicaAuthor Commented:
--BrandonGalderisi:  
I tried that and I get too many results for patients.  For example, I should get 17 patients, where now I get 64.  The deviations (violations) is correct however.   It almost seems like it counts a patient everytime there a deviation.  So even though there is 1 patient, if there are 5 deviations for this patient it counts the patient 5 times.  It should just return 5 patients.  Thanks for your help.
0
 
SharathData EngineerCommented:
check this also.
Select 
rds.stdinv_id,
rds.Studyname,
rds.Site_NO,
rds.CRA,
rds.Country,
count(distinct p.patient_Id)as CountPatients,
count(distinct D.issue) as CountDeviations
 
From prod_rdmetrics_sitecrastudy RDS 
 left outer join trialworksINO.dbo.tbl_patients P 
   on rds.stdinv_id = p.stdinv_id
 left outer join trialworksINO.dbo.tbl_deviation D 
   on p.patient_id = d.patient_Id and d.issue='violation'
 
group by 
rds.stdinv_id,
rds.studyname,
rds.Site_NO,
rds.CRA,
rds.Country

Open in new window

0
 
PharmicaAuthor Commented:
In response to wrwilson:
Same issue, I get 64 patients where I should get 17.   The deviations are correct though.  It seems like every time a patient has multiple deviations it counts the patients each time.  I am 100% sure there is 17 patients.  If I run the query exluding the left out join for deviations I get 17 patients.  As soon as the outer join is added, i get 64.
0
 
PharmicaAuthor Commented:
In response to Sharath_123 (first response)
This worked somewhat.  Although it did not multiply the patients, it only returns the first number.  For example, it should return something like this

Studyname  CountPatients    CountDeviations
  A                       9                          4
  B                       0                          0
  C                        8                         11

where as yours only returns the top line
Studyname  CountPatients    CountDeviations
  A                       9                          4

In response to the second example,  the patients are correct but the deviations are not.  For example, instead of reporting the number of deviations it reports 0 or 1.  The results with 0 actually are 0, but the the resutls with 1's really have 5+ deviations  but this just shows a 1.  It seems as if it is treating like true or false.

0
 
PharmicaAuthor Commented:
Thanks gentlemen, but this solution worked with a minor change.  I appreciate your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.