|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 11th, 2003, 01:32 PM
|
|||
|
|||
|
Complex SQL query - need help
Hi all,
The below is structure of my tables table1 SurgeryDate| Surgeon1 | Surgeon2|Procedure1| Procedure2| ________________________________________________________ 09/01/2003 |Ron |John | TRT | PRT 09/02/2003 |Smith |John | TRT | PRT 09/03/2003 |John | | MRT | table2 Procedure| SurgeryType| ________________________ TRT | Ortho PRT | General MRT | Gyn I worte a query as follows: SELECT Surgeon1, SurgeryType, COUNT(*) AS 'Total Surgeries' FROM table1,tabel2 WHERE( (table1.Procedure1=table2.Procedure) AND (surgeryDate Between '09/01/2003' And '09/30/2003')) Group BY [Surg Type], [Name of Surgeon] But this only gives me the partial total of the surgeries done by any surgeon becuase i am not including surgeon2 and procedure2. How can i reconstruct my query that gives me the total number of surgeries done by any surgeon whether his name is under surgeon1 or surgoen2 column and how can i get the count of total procedures whether it is under procedure1 or procedure2. I am not sure whether my question is proper but i am trying to get the total count of different type of procedures done by any surgeon. Thanks in advance. VJ
|
|
#2
November 11th, 2003, 05:52 PM
|
|||
|
|||
|
This will return each surgerytype and the number of times it has been carried out. If I understood you correctly this is what you wanted (I left out the date stuff, you'll have to add it to the sub-SELECT's)
Code:
SELECT s.surgeryType,
COUNT(*)
FROM (SELECT t1.surgeon1 as surgeon, t2.surgerytype FROM table1 t1, table2 t2 WHERE t1.procedure1 = t2.procedure
UNION
SELECT t1.surgeon2 as surgeon, t2.surgerytype FROM table1 t1, table2 t2 WHERE t1.procedure2 = t2.procedure) s
GROUP BY surgerytype
But honestly: you should rethink your DB model!
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Complex SQL query - need help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
