Count Query Help

[mossbs]

Hi guys,

Trying to do a query to produce a count by dealer code of when certain things are true about a quote raised by that dealer.

the main tables i am using are the Quote table and the QuoteItem table (quote has details on when the quote was raised and by who, QuoteItem contains details of the items on that quote)

There are 3 counts i need to do - one for 'standard' items on the quote, one for 'Miscelanious' items, and one for custom jobs.

The way to tell these 3 types apart are a column called Type in the QuoteItem table which can be one of the following values.....

CJOB
DEAL
JOB
LABR
LITM
MITM
PART
PITM

...the same quote number can have many of these against it though.

- Standard jobs will always have a type of 'JOB' attached to it
- Custom jobs will always have a type of 'CJOB' attached to it
- The 'MISC' items jobs - can have any of the types listed above but will not have 'CJOB' or 'JOB' attached to it.

I have tried the following for the 'MISC' items part....

select d.Code,count(Id_Quote)
from Quote q
inner join Dealer d on d.Id_Dealer = q.Id_Dealer and d.Code_Market = 'ES'
where Id_Quote in (select Id_Quote from QuoteItem where Type in ('PART','LITM','LABR','MITM','DEAL','PITM'))
and Id_Quote not in (select Id_Quote from QuoteItem where Type = 'JOB')
group by d.Code

but it took nearly 14 minutes to complete!!

Is there a more efficient way to do this?
Cheers..

 

[bborissov]

Try:

SELECT Dealer.Code,Count(Quote.Id_Quote)
FROM Quote
INNER JOIN Dealer    ON Dealer.Id_Dealer   = Quote.Id_Dealer AND
                        Dealer.Code_Market = 'ES'
INNER JOIN QuoteItem ON QuoteItem.Id_Quote = Quote.Id_Quote AND
                        QuoteItem.Type IN ('PART','LITM','LABR','MITM','DEAL','PITM')
GROUP BY Dealer.Code

(not tested!)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[mossbs]

Thanks for the reply Borislav, has speeded it up well.

however - the script you have given only checks whether it has one of ('PART','LITM','LABR','MITM','DEAL','PITM') attached to the quote id. I need it to count those that have one of the above but don't have 'JOB' attached to it.

I may not have explained it very well before .... but...

records in the QuoteItem table for instance could look something like this...

Id_QItem	Id_Quote	Type
1			1	JOB
2			1	PART
3			1	LABR
4			2	PART
5			2	LABR
6			3	LABR
7			3	PART
8			4	JOB
9			4	PART
10			4	LABR

... so for this i would only want to count Id_Quote 2 and 3 as the others have the type 'JOB' or 'CJOB' attached to them.

does this script look like it'll do what i mean? (added one line to the one provided by you)

SELECT Dealer.Code,Count(Quote.Id_Quote)
FROM Quote
INNER JOIN Dealer ON Dealer.Id_Dealer = Quote.Id_Dealer AND Dealer.Code_Market = 'ES'
INNER JOIN QuoteItem ON QuoteItem.Id_Quote = Quote.Id_Quote 
	AND QuoteItem.Type IN ('PART','LITM','LABR','MITM','DEAL','PITM') 

where QuoteItem.Type not in ('JOB', 'CJOB')

GROUP BY Dealer.Code

Cheers.

 

[mossbs]

ok it seems that my second query works fine.

so now basically i have 3 seperate queries,

--MISC ITEMS

SELECT Dealer.Code,Count(distinct(Quote.Id_Quote))
FROM Quote
INNER JOIN Dealer ON Dealer.Id_Dealer = Quote.Id_Dealer AND Dealer.Code_Market = 'ES'
INNER JOIN QuoteItem ON QuoteItem.Id_Quote = Quote.Id_Quote 
	AND QuoteItem.Type IN ('PART','LITM','LABR','MITM','DEAL','PITM') 
where QuoteItem.Type not in ('JOB', 'CJOB')
and datediff(m,CreateDate,Getdate())< 6
GROUP BY Dealer.Code

--Standard Jobs

SELECT Dealer.Code,Count(distinct(Quote.Id_Quote))
FROM Quote
INNER JOIN Dealer ON Dealer.Id_Dealer = Quote.Id_Dealer AND Dealer.Code_Market = 'ES'
INNER JOIN QuoteItem ON QuoteItem.Id_Quote = Quote.Id_Quote 
	AND QuoteItem.Type = 'JOB'
where datediff(m,CreateDate,Getdate())< 6
GROUP BY Dealer.Code


-- Custom Jobs

SELECT Dealer.Code,Count(distinct(Quote.Id_Quote))
FROM Quote
INNER JOIN Dealer ON Dealer.Id_Dealer = Quote.Id_Dealer AND Dealer.Code_Market = 'ES'
INNER JOIN QuoteItem ON QuoteItem.Id_Quote = Quote.Id_Quote 
	AND QuoteItem.Type = 'CJOB'
where datediff(m,CreateDate,Getdate())< 6
GROUP BY Dealer.Code

is there anyway to stick these all together into one query so that it would produce one table with 4 columns....

1. DealerCode,
2. Misc Items,
3. Standard Jobs,
4. Custom Jobs


....?

cheers,

 

[Zurich98]

will this work?

SELECT Dealer.Code
,sum(case when QuoteItem.[Type] = 'JOB' then 1 else 0 end) as StandardJob

,sum(case when QuoteItem.[Type] = 'CJOB' then 1 else 0 end) as CustomJob

,sum(case when QuoteItem.[Type] not in ('JOB','CJOB') then 1 else 0 end) as MISC

FROM Quote
INNER JOIN Dealer ON Dealer.Id_Dealer = Quote.Id_Dealer AND
Dealer.Code_Market = 'ES'
INNER JOIN QuoteItem ON QuoteItem.Id_Quote = Quote.Id_Quote AND

GROUP BY Dealer.Code

 

[mossbs]

cheers for the reply Zurich -

your script works in essence, however it is counting duplicates in the 'MISC' area. As the example table i gave above shows, the Id_Quote can have more than one entry in that table as it can have more than one 'item'/'Part' etc attached to it.

I need to count(distinct(Id_Quote)) i guess... but cant see a way of doing it using case statements like you have.

Any ideas?

cheers.