By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

SQL query/unique entries only

P: 2
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
Oct 2 '08 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT a.LastPFProtocolStageIndex, 
  2.              b.PatientLastName AS LastName, 
  3.              a.VisitDateTime, 
  4.              a.FVLTest AS FVL, 
  5.              a.DLCOTest AS DLCO, 
  6.              a.PlethTest AS PLETH,
  7.              d.PFProtocolStageLabel AS ProtocolState3,
  8.              count(a.LastPFProtocolStageIndex) as NumDups
  9. FROM PatVisit a
  10. INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
  11. INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
  12. INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
  13. GROUP BY a.LastPFProtocolStageIndex, 
  14.               b.PatientLastName , 
  15.               a.VisitDateTime, 
  16.               a.FVLTest AS FVL, 
  17.               a.DLCOTest AS DLCO, 
  18.               a.PlethTest AS PLETH,
  19.               d.PFProtocolStageLabel
  20. ORDER BY a.VisitDateTime DESC
  21.  
and if you only want a list of the duplicate records then something like this
Expand|Select|Wrap|Line Numbers
  1. SELECT a.LastPFProtocolStageIndex, 
  2.              b.PatientLastName AS LastName, 
  3.              a.VisitDateTime, 
  4.              a.FVLTest AS FVL, 
  5.              a.DLCOTest AS DLCO, 
  6.              a.PlethTest AS PLETH,
  7.              d.PFProtocolStageLabel AS ProtocolState3,
  8.              count(a.LastPFProtocolStageIndex) as NumDups
  9. FROM PatVisit a
  10. INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
  11. INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
  12. INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
  13. GROUP BY a.LastPFProtocolStageIndex, 
  14.               b.PatientLastName , 
  15.               a.VisitDateTime, 
  16.               a.FVLTest AS FVL, 
  17.               a.DLCOTest AS DLCO, 
  18.               a.PlethTest AS PLETH,
  19.               d.PFProtocolStageLabel
  20. HAVING count(a.LastPFProtocolStageIndex)>1
  21. ORDER BY a.VisitDateTime DESC
  22.  
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?
Oct 2 '08 #2

P: 2
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 :-)

Expand|Select|Wrap|Line Numbers
  1. SELECT     PatVisit.LastPFProtocolStageIndex, 
  2. Patient.PatientLastName AS LastName, 
  3. PatVisit.VisitDateTime, 
  4. PatVisit.FVLTest AS FVL, 
  5.  
  6. PatVisit.DLCOTest AS DLCO, 
  7. PatVisit.PlethTest AS PLETH
  8. FROM         PatVisit 
  9. INNER JOIN Patient ON PatVisit.PatientGUID = Patient.PatientGUID 
  10. INNER JOIN PFProtocol ON PatVisit.PFProtocolGUID = PFProtocol.PFProtocolGUID 
  11. INNER JOIN PFProtocolStage ON PFProtocol.PFProtocolGUID = PFProtocolStage.PFProtocolGUID
  12.  
  13. GROUP BY    PatVisit.LastPFProtocolStageIndex, 
  14.             Patient.PatientLastName, 
  15.             PatVisit.VisitDateTime, 
  16.             PatVisit.FVLTest, 
  17.             PatVisit.DLCOTest, 
  18.             PatVisit.PlethTest
  19. HAVING      (PatVisit.VisitDateTime > CONVERT(DATETIME, '2008-09-01 00:00:00', 102))
  20.  
  21. ORDER BY PatVisit.VisitDateTime DESC
  22.  
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT a.LastPFProtocolStageIndex, 
  2.              b.PatientLastName AS LastName, 
  3.              a.VisitDateTime, 
  4.              a.FVLTest AS FVL, 
  5.              a.DLCOTest AS DLCO, 
  6.              a.PlethTest AS PLETH,
  7.              d.PFProtocolStageLabel AS ProtocolState3,
  8.              count(a.LastPFProtocolStageIndex) as NumDups
  9. FROM PatVisit a
  10. INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
  11. INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
  12. INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
  13. GROUP BY a.LastPFProtocolStageIndex, 
  14.               b.PatientLastName , 
  15.               a.VisitDateTime, 
  16.               a.FVLTest AS FVL, 
  17.               a.DLCOTest AS DLCO, 
  18.               a.PlethTest AS PLETH,
  19.               d.PFProtocolStageLabel
  20. ORDER BY a.VisitDateTime DESC
  21.  
and if you only want a list of the duplicate records then something like this
Expand|Select|Wrap|Line Numbers
  1. SELECT a.LastPFProtocolStageIndex, 
  2.              b.PatientLastName AS LastName, 
  3.              a.VisitDateTime, 
  4.              a.FVLTest AS FVL, 
  5.              a.DLCOTest AS DLCO, 
  6.              a.PlethTest AS PLETH,
  7.              d.PFProtocolStageLabel AS ProtocolState3,
  8.              count(a.LastPFProtocolStageIndex) as NumDups
  9. FROM PatVisit a
  10. INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID 
  11. INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID 
  12. INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
  13. GROUP BY a.LastPFProtocolStageIndex, 
  14.               b.PatientLastName , 
  15.               a.VisitDateTime, 
  16.               a.FVLTest AS FVL, 
  17.               a.DLCOTest AS DLCO, 
  18.               a.PlethTest AS PLETH,
  19.               d.PFProtocolStageLabel
  20. HAVING count(a.LastPFProtocolStageIndex)>1
  21. ORDER BY a.VisitDateTime DESC
  22.  
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?
Oct 3 '08 #3

Delerna
Expert 100+
P: 1,134
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?
Oct 4 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.