SQL query/unique entries only

[medic29]

Hello,
I have a query listed below which works but...
The data below is what the query displays. There are hundreds of records I am bringing up and each one has between 3-6 entries displayed in this general format. The LastPFProtocolStageIndex (level reached) column will always reflect the same number for the patient. Based on the number in the first column I would like the query to display the first duplicate occurance and nothing more.
The overall project is to have a printout of this data to be turned in at the end of each month for clinic stats. Right now I take this nice long list, put it through some excel paces and in a hour or so I can have what I want.
My initial question was if anybody could tell me how to structure the query to display only the first entry of the duplicate series, but if anybody wants to go a bit farther and point out any possibilies for adding a count for the various columns (FVL, DLCO, Pleth) for each that is listed true...

Thank you!


level reached Last Name Date FVL DLCO Pleth Protocol
6 Doe 09/02/08 09:05 AM TRUE FALSE FALSE Pre
6 Doe 09/02/08 09:05 AM TRUE FALSE FALSE Pre
6 Doe 09/02/08 09:05 AM TRUE FALSE FALSE Pre

SELECT PatVisit.LastPFProtocolStageIndex, Patient.PatientLastName AS LastName, PatVisit.VisitDateTime, PatVisit.FVLTest AS FVL,
PatVisit.DLCOTest AS DLCO, PatVisit.PlethTest AS PLETH, PFProtocolStage.PFProtocolStageLabel AS ProtocolState3
FROM PatVisit INNER JOIN
Patient ON PatVisit.PatientGUID = Patient.PatientGUID INNER JOIN
PFProtocol ON PatVisit.PFProtocolGUID = PFProtocol.PFProtocolGUID INNER JOIN
PFProtocolStage ON PFProtocol.PFProtocolGUID = PFProtocolStage.PFProtocolGUID
ORDER BY PatVisit.VisitDateTime DESC

[Delerna]

Not sure if this is what you are after but with a bit of experimentation and/or more questions, we should be able to arrive at what you are after.

SELECT a.LastPFProtocolStageIndex, 
             b.PatientLastName AS LastName, 
             a.VisitDateTime, 
             a.FVLTest AS FVL, 
             a.DLCOTest AS DLCO, 
             a.PlethTest AS PLETH,
             d.PFProtocolStageLabel AS ProtocolState3,
             count(a.LastPFProtocolStageIndex) as NumDups
FROM PatVisit a
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
GROUP BY a.LastPFProtocolStageIndex, 
              b.PatientLastName , 
              a.VisitDateTime, 
              a.FVLTest AS FVL, 
              a.DLCOTest AS DLCO, 
              a.PlethTest AS PLETH,
              d.PFProtocolStageLabel
ORDER BY a.VisitDateTime DESC
 

and if you only want a list of the duplicate records then something like this

SELECT a.LastPFProtocolStageIndex, 
             b.PatientLastName AS LastName, 
             a.VisitDateTime, 
             a.FVLTest AS FVL, 
             a.DLCOTest AS DLCO, 
             a.PlethTest AS PLETH,
             d.PFProtocolStageLabel AS ProtocolState3,
             count(a.LastPFProtocolStageIndex) as NumDups
FROM PatVisit a
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
GROUP BY a.LastPFProtocolStageIndex, 
              b.PatientLastName , 
              a.VisitDateTime, 
              a.FVLTest AS FVL, 
              a.DLCOTest AS DLCO, 
              a.PlethTest AS PLETH,
              d.PFProtocolStageLabel
HAVING count(a.LastPFProtocolStageIndex)>1
ORDER BY a.VisitDateTime DESC
 

The only difference between the two queries is the HAVING clause (2nd last line).

I have added table aliases (a,b,c,d) only to make the code a bit more readable
in the limited width code window. You can choose to keep them or not.
The code is almost identical to yours. I have added 1 colmn in the select clause to count the duplicate rows, and a GROUP BY clause.

Not having your tables and their data I cannot test it so I hope I didn't make any syntactical errors?

[medic29]

That you for your suggestion. It looks like I made this a bit harder than it should have been. Since all the entries per patient were identical except the stagelabel part, I simply omitted that and now I'm down to only one row per patient. I have included a few rows of the result below (no info except for last partial names)

The system I am working on is a patient database terminal which has no network access. So getting access to the patient data would not be the best thing :-)

SELECT     PatVisit.LastPFProtocolStageIndex, 
Patient.PatientLastName AS LastName, 
PatVisit.VisitDateTime, 
PatVisit.FVLTest AS FVL, 
 
PatVisit.DLCOTest AS DLCO, 
PatVisit.PlethTest AS PLETH
FROM         PatVisit 
INNER JOIN Patient ON PatVisit.PatientGUID = Patient.PatientGUID 
INNER JOIN PFProtocol ON PatVisit.PFProtocolGUID = PFProtocol.PFProtocolGUID 
INNER JOIN PFProtocolStage ON PFProtocol.PFProtocolGUID = PFProtocolStage.PFProtocolGUID
 
GROUP BY    PatVisit.LastPFProtocolStageIndex, 
            Patient.PatientLastName, 
            PatVisit.VisitDateTime, 
            PatVisit.FVLTest, 
            PatVisit.DLCOTest, 
            PatVisit.PlethTest
HAVING      (PatVisit.VisitDateTime > CONVERT(DATETIME, '2008-09-01 00:00:00', 102))
 
ORDER BY PatVisit.VisitDateTime DESC
 

As you see I've added a limit on the date.
Now the only thing I can see from here is slight cosmetic changes. The numbers before the name will equal to
1=FVC
3=FVC+POST
5=FVC+MTC
6=FVC+MTC+POST
The way we have been doing this by hand was to have 5 columns, FVC, POST/BD,MTC,PLETH, and DLCO. An 'X' is made in each square per patient depending on what we did. Ideally that is what the stats office wants in terms of format. I don't quite know far the SQL query can be pushed as far as this kind of formatting.
Is there any way to have the actual results displayed with the text in place of the numbers? The same would apply to the numbers following the date. Preferably 1=a X and 0's would be blank.
I couldn't get the count feature to work. Don't know if my code modifications would change how the count feature is worded but that would still be a nice feature to have.


1 Ga*** 2008-10-03 09:40:04.000 1 0 0
5 Shamr*** 2008-10-03 09:17:38.000 1 0 0
5 PE*** 2008-10-03 08:27:49.000 1 0 0
1 Na*** 2008-10-02 08:55:23.000 1 1 1
6 TUR*** 2008-10-02 08:00:59.000 1 0 0
1 MEDGRAPHICS-QC 2008-10-02 07:31:38.000 0 1 0
5 GUE*** 2008-10-01 09:06:24.000 1 0 0
5 RIV*** 2008-10-01 08:45:29.000 1 0 0
5 CL*** 2008-10-01 08:11:20.000 1 0 0
1 MEDGRAPHICS-QC 2008-10-01 07:27:07.000 0 1 0
1 Jack*** 2008-09-30 09:13:25.000 1 0 1

Not sure if this is what you are after but with a bit of experimentation and/or more questions, we should be able to arrive at what you are after.

SELECT a.LastPFProtocolStageIndex, 
             b.PatientLastName AS LastName, 
             a.VisitDateTime, 
             a.FVLTest AS FVL, 
             a.DLCOTest AS DLCO, 
             a.PlethTest AS PLETH,
             d.PFProtocolStageLabel AS ProtocolState3,
             count(a.LastPFProtocolStageIndex) as NumDups
FROM PatVisit a
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
GROUP BY a.LastPFProtocolStageIndex, 
              b.PatientLastName , 
              a.VisitDateTime, 
              a.FVLTest AS FVL, 
              a.DLCOTest AS DLCO, 
              a.PlethTest AS PLETH,
              d.PFProtocolStageLabel
ORDER BY a.VisitDateTime DESC
 

and if you only want a list of the duplicate records then something like this

SELECT a.LastPFProtocolStageIndex, 
             b.PatientLastName AS LastName, 
             a.VisitDateTime, 
             a.FVLTest AS FVL, 
             a.DLCOTest AS DLCO, 
             a.PlethTest AS PLETH,
             d.PFProtocolStageLabel AS ProtocolState3,
             count(a.LastPFProtocolStageIndex) as NumDups
FROM PatVisit a
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
GROUP BY a.LastPFProtocolStageIndex, 
              b.PatientLastName , 
              a.VisitDateTime, 
              a.FVLTest AS FVL, 
              a.DLCOTest AS DLCO, 
              a.PlethTest AS PLETH,
              d.PFProtocolStageLabel
HAVING count(a.LastPFProtocolStageIndex)>1
ORDER BY a.VisitDateTime DESC
 

The only difference between the two queries is the HAVING clause (2nd last line).

I have added table aliases (a,b,c,d) only to make the code a bit more readable
in the limited width code window. You can choose to keep them or not.
The code is almost identical to yours. I have added 1 colmn in the select clause to count the duplicate rows, and a GROUP BY clause.

Not having your tables and their data I cannot test it so I hope I didn't make any syntactical errors?

[Delerna]

Wasn't requesting access, just making a statement that I couldn't test for that reason. :)

To replace the numbers with the text
==========================
all you need do is make this table
ID,Text
1,FVC
3,FVC+POST
5,FVC+MTC
6,FVC+MTC+POST


Now you can join PatVisit.LastPFProtocolStageIndex to the ID of the above and the select the Text field of above table instead of PatVisit.LastPFProtocolStageIndex.


With your HAVING clause
==================
having is useful for filtering by aggregated fields
ie HAVING sum(field)>10 and count(OtherField)>1

For the filter you are using (no agregated field) the WHERE clause is what you would use
WHERE PatVisit.VisitDateTime > CONVERT(DATETIME, '2008-09-01 00:00:00', 102)


For the count
==========
I don't understand why it didnt work
all you need is Count(field) in the select clause
and an appropriate GROUP BY clause.
Count(field) returns the number of rows
where all the fields in the GROUP BY clause
are exactly identical.

What was the problem?
Was there an error reported? what was it?