SQL Query 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)

SQL Query Help

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-11 : 09:58:29

Hi All,

I have table (myTable) in the following format.

SurgeryDate Procedure1 Procedure2

09/01/2003 EGD,PPT QRT
09/02/2003 PPT EGD,QRT
09/03/2003 QRT
09/04/2003 EGD,PPT

My question-- How can i write a SQL query that gives me the total count of the

procedures done betweeen surgeryDates '09/01/2003' and '09/04/2003'. Can i write such a query??

I have this doubt because for the above example it has to give me the count of procedures as 9.I mean how can i write a query to count EGD and PPT present in the same columns as two diffferent procedures. Do i need to look for a "comma" separator and count the total procedures.

Experts Out there, Any suggestions.

Thanks in advance.

VJ

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-11-11 : 10:16:31

I think the most sensible solution to your problem is to structure the table. Introducing a primary key column such as SurgeryID would make life a lot easier. You could then store each individual surgery procedure in a separate row together with it's date + any other information you require.

All you will need then is something like :


SELECT COUNT(surgeryID)
FROM myTable
WHERE surgeryDate between firstDate AND secondDate

or, keeping the same structure for your tables, you could use :


SELECT	sum(sg.egd) as 'EGD', 
	sum(sg.qrt) as 'QRT', 
	sum(sg.ppt) as 'PPT'
FROM	(select	surgeryDate,
		(LEN(surgery) - LEN(REPLACE(surgery, 'EGD', '')))/3 AS 'EGD',
		(LEN(surgery) - LEN(REPLACE(surgery, 'QRT', '')))/3 AS 'QRT',
		(LEN(surgery) - LEN(REPLACE(surgery, 'PPT', '')))/3 AS 'PPT'
	from myTable) as sg
WHERE	sg.surgeryDate BETWEEN CONVERT(DATETIME, '09/01/2003') and CONVERT(DATETIME, '09/04/2003')
go

__________________
Make love not war!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-11 : 21:09:31

[code]
create table myTable (SurgeryDate datetime, Procedure1 varchar(20), Procedure2 varchar(20))
go

insert into myTable
select '09/01/2003', 'EGD','PPT QRT' union
select '09/02/2003', 'PPT EGD','QRT' union
select '09/03/2003', 'QRT',NULL union
select '09/04/2003', 'EGD','PPT'
go

create table #Numbers (n int)
go

insert into #Numbers
select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union
select 13 union select 14 union select 15 union select 16 union select 17 union select 18 union
select 19 union select 20
go

select identity(int,0,1) SurgeryID, surgerydate, ' ' + ISNULL(ltrim(rtrim(procedure1)),'') + ' ' + ISNULL(ltrim(rtrim(procedure2)),'') + ' ' ProcedureN
into #t
from myTable
where SurgeryDate between '09/01/2003' AND '09/04/2003'
go

create table #n (SurgeryDate datetime,ProcedureN varchar(20))
go

declare @id int,@surgerydate datetime,@Procedure varchar(42)
set @id = 0
while @id <= (select max(SurgeryID) from #t)
begin

select @SurgeryDate = surgeryDate FROM #t WHERE SurgeryID = @id
Select @Procedure = ProcedureN FROM #t WHERE SurgeryID = @id

insert into #n
select @SurgeryDate,substring(@Procedure,charindex(' ',@Procedure,n)+1,charindex(' ',@Procedure,n+1)-n-1 )
from #Numbers
where n < len(@procedure) and n > 0
and charindex(' ',@procedure,n) = n

set @id = @id + 1
end

select * from #n
select count(*) 'Total Procedures' from #n

drop table #t
drop table #n
drop table #Numbers
drop table myTable
[/code]

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-12 : 09:05:46

Guys,
Thanks a lot for your replies.

Although the query looks complex( of course it is complex for me) i will work on it. Thanks for giving me an approach.

VJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources