Combine distinct queries with different where

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)

Combine distinct queries with different where

Author

Topic

Gareth_Hastings
Starting Member

7 Posts

Posted - 2008-02-25 : 10:11:55

Hi,

I am currently converting a huge slow sql query that scans through a table and produces lots of totals for a customer. It currently defines lots of variables and then does a select per variable. This results in lots of table scans and really slow performance.

I have re-written the query so it uses a single select into a derived table and then multiple case when statements to create the totals

eg


select
    sum(case when value = 1 then 1 else 0 end) as Total
from
    ( select * from datatable where customer = 1 and date between @a and @b) as DerivedTable

This works great and runs in a fraction of a section compared to the other query.

The problem I've come across now is that the old query does a

select count(distinct value + value2)

in a few places. So far I haven't worked out how I can replicate the same thing in my single query.

I guess my question would be: Is there anyway to write the BELOW code as a single query using only a single scan of the table @t ?


declare @t table
(
	forename varchar(10),
	surname  varchar(10),
	id int
)

insert @t
	select 'Fred',	'Smith', 0 union all
	select 'Jane',	'Smith', 1 union all
	select 'Andy',	'Melon', 1 union all
	select 'Tim',	'Apple', 2 union all
	select 'Fred',	'Smith', 0


select count(distinct(forename + surname)) as a from @t where id = 0
select count(distinct(forename + surname)) as a from @t where id = 1
select count(distinct(forename + surname)) as a from @t

Thanks for any help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 10:24:33

quote:

Originally posted by Gareth_Hastings

select
    sum(case when value = 1 then 1 else 0 end) as Total
from
    ( select * from datatable where customer = 1 and date between @a and @b) as DerivedTable

SELECT	COUNT(*) AS Total
FROM	DataTable
WHERE	Customer = 1
	AND Date BETWEEN @A AND @B
	AND Value = 1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 10:27:36

Also concatenation of strings is generally a bad idea.
Add

select 'Freds', 'Mith', 0 union all

to your sample data...

E 12°55'05.25"
N 56°04'39.16"

Gareth_Hastings
Starting Member

7 Posts

Posted - 2008-02-25 : 10:29:33

Hi Peso,

Thanks for the reply. I should have been more clear. The statement you re-wrote was a snippet of something much larger and I only posted it as a kind of background info. The actual question was about the number of table scans on the temp table @t!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 10:31:48

[code]-- Prepare sample data
declare @t table (forename varchar(10), surname varchar(10),id int)

insert @t
select 'Fred', 'Smith', 0 union all
select 'Freds', 'Mith', 0 union all
select 'Jane', 'Smith', 1 union all
select 'Andy', 'Melon', 1 union all
select 'Tim', 'Apple', 2 union all
select 'Ti', 'Mapple', 0 union all
select 'Fred', 'Smith', 0

-- Faulty original queries
select count(distinct(forename + surname)) as a from @t where id = 0
select count(distinct(forename + surname)) as a from @t where id = 1
select count(distinct(forename + surname)) as a from @t

-- Peso's suggestion
SELECT COUNT(DISTINCT CASE WHEN ID = 0 THEN ForeName + CHAR(3) + SurName ELSE NULL END),
COUNT(DISTINCT CASE WHEN ID = 1 THEN ForeName + CHAR(3) + SurName ELSE NULL END),
COUNT(DISTINCT ForeName + CHAR(3) + SurName)
FROM @T[/code]

E 12°55'05.25"
N 56°04'39.16"

Gareth_Hastings
Starting Member

7 Posts

Posted - 2008-02-25 : 10:40:13

Thanks very much,

The table I have to work with is huge and doesn't have a single unique field :( I am also unable to change it which is a pain!

I should have picked better sample data too, that'll teach me for not posting proper table info related to my real question !!

Again thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources