You are here:

MS SQL Server/Getting summarised total amounts from one query and table

Advertisement


Question
QUESTION: Hello,

I have a database table in SQL server that I am trying to write a query on for administration purposes. The table design looks like this

TABLE NAME: patient_study
FIELDS: patientId (INT - PrimaryKey), studyCode (VARCHAR - PrimaryKey), dateConsented DATE).

The data in the table looks something like this:

patientID  studyCode  dateConsented
---------  ---------  -------------
0          bstop      01/12/2014
1          bstop      01/11/2014
1          badbir     01/11/2014
2          core       01/10/2014
3          bstop      01/11/2014
3          badbir     01/11/2014
3          core       01/12/2014
4          bstop      05/11/2014
5          core       


So what I want is to get the following:

total amount of consented patients on the 'core' study.
total amount of consented patients on the 'badbir' study.
total amount of consented patients who are on the 'bstop' study and may or may not be on the 'core' study, but are definitely not on the 'badbir' study.

Also patientId 0 should not be included in any of the total amounts.

I tried doing this using GROUP BY but I kept getting the wrong results. Any help would be much appreciated.

Thanks

ANSWER: Apologies for the delay. I think the best approach is for you to show me the query(ies) you've used and the result(s) you've received, so far. (Not all, necessarily, but the 1 or 2 or 3 which seem "closest to right").

We can probably just tweak those and have it working.

Also: What version of SQL Server?

---------- FOLLOW-UP ----------

QUESTION: Here is the query that I came up with:

SELECT count(patientId) as total_consented, studyCode
FROM patient_study
WHERE dateConsented IS NOT NULL and patientId > 0
GROUP BY studyCode

This actually works for the first 2 requirements I mentioned previously. However when it comes to getting patients who are on the 'bstop' study but not on the 'badbir' study, as it just looks at the table and adds together all the patient's who are assigned to the 'bstop' study regardless of whatever else they are assigned to.
Hopefully that makes it a little easier to understand. I think I'm just missing an extra rule in the WHERE/GROUP BY clause, but I'm not sure how to write it.

Thanks again

ANSWER: I'm glad I had you send me that code because I missed the part where you wanted this all in one query. Yikes! Reading comprehension fail, eh?

Anyway, I'm not at a computer where I can test this, but this is the first thing that came to mind:

SELECT count(PS.patientId) as total_consented, PS.studyCode
FROM patient_study AS PS
LEFT JOIN (select patientID FROM patient_study WHERE studyCode = 'badbir' AND dateConsented IS NOT NULL AND patientId > 0) AS badbirs
   ON PS.PatientID = badbirs.PatientID
WHERE PS.dateConsented IS NOT NULL and PS.patientId > 0
 AND CASE PS.studyCode
  WHEN 'bstop' AND badbirs.PatentID IS NOT NULL THEN 'EXCLUDE'
  ELSE 'INCLUDE'
     END = 'INCLUDE'
GROUP BY PS.studyCode


Explanation:
* I'm creating a virtual table (everything inside the parens after the LEFT JOIN). That table will contain patientID for rows in the badbir study. I've given it the alias "badbirs".
* I LEFT JOINed that virtual table to your original query, so your original query will scan the patient_study table in its entirety just as before
* I added a CASE statement in the WHERE to eliminate the bstop patient who are also badbir patients
* If the badbirs.PatientID is not null, that means there's a "match" between the patient_study.patientID and the badbirs.patientID, which means that row is to be excluded.
* I used "EXCLUDE"/"INCLUDE" for readability, but you could also use 1/0 or "true"/"false" or whatever you want.

Let me know if this doesn't work (and, if not, what results you get). Tomorrow I'll be at a location where I can actually test code, if we need to explore this further.



---------- FOLLOW-UP ----------

QUESTION: Hello,

The query that you wrote:

SELECT count(PS.patientId) as total_consented, PS.studyCode
FROM patient_study AS PS
LEFT JOIN (select patientID FROM patient_study WHERE studyCode = 'badbir' AND dateConsented IS NOT NULL AND patientId > 0) AS badbirs
  ON PS.PatientID = badbirs.PatientID
WHERE PS.dateConsented IS NOT NULL and PS.patientId > 0
AND CASE PS.studyCode
WHEN 'bstop' AND badbirs.PatentID IS NOT NULL THEN 'EXCLUDE'
ELSE 'INCLUDE'
    END = 'INCLUDE'
GROUP BY PS.studyCode

throws up a few errors in SQL Server the first one being:
'Incorrect syntax near the keyword AND' on line 7

Answer
Try this


SELECT count(PS.patientId) as total_consented, PS.studyCode
FROM patient_study AS PS
LEFT JOIN (select patientID FROM patient_study WHERE studyCode = 'badbir' AND dateConsented IS NOT NULL AND patientId > 0) AS badbirs
 ON PS.PatientID = badbirs.PatientID
WHERE PS.dateConsented IS NOT NULL and PS.patientId > 0
AND CASE
   WHEN PS.studyCode = 'bstop' AND badbirs.PatentID IS NOT NULL THEN 'EXCLUDE'
ELSE 'INCLUDE'
   END = 'INCLUDE'
GROUP BY PS.studyCode

I changed the CASE clause.

(I still have not tried this in my own SSMS, so there might still be problems)

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.