We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

t-SQL problem - combining two queries into one

Medium Priority
634 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

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

Randy Wilson.Net Developer

Commented:
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
 
 
Data Engineer
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Randy Wilson.Net Developer

Commented:
Oops, looks like we came up with the same solution...

Author

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.
SharathData Engineer
CERTIFIED EXPERT

Commented:
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

Author

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.

Author

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.

Author

Commented:
Thanks gentlemen, but this solution worked with a minor change.  I appreciate your help
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.