Complex SQL Query - Need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Complex SQL Query - Need help

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-11 : 14:34:07

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-11 : 14:39:33

Please provide DDL for your tables. DDL is CREATE TABLE statements. Sample data in the form of INSERT INTO statements is needed along with the expected result set.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-11 : 14:50:37

[code]
create table Table1
(
SurgeryDate datetime,
Surgeon1 varchar(10),
Surgeon2 varchar(10),
Procedure1 char(3),
Procedure2 char(3)
)
go

insert into table1
select '09/01/2003','Ron','John','TRT','PRT' union
select '09/02/2003','Smith','John','TRT','PRT' union
select '09/03/2003','John',null,'MRT' ,null
go

create table table2
(
Procedur char(3),
SurgeryType varchar(10)
)
go
insert into table2
select 'TRT','Ortho'
union select 'PRT','General'
union select 'MRT','Gyn'
go

select distinct Surgeon, b.SurgeryType as 'Surgery',count(a.procedur) 'Total Surgeries'
from
(
select surgerydate,surgeon1 surgeon,procedure1 procedur from table1
union all
select surgerydate,surgeon2,procedure2 from table1
) a
join table2 b on a.Procedur=b.Procedur
where (surgeryDate Between '09/01/2003' And '09/30/2003')
and surgeon is not null and a.procedur is not null
group by surgeon,b.SurgeryType
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources