• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Comples join, assist in remaking to improve query time

Hi,

Kindly assist in remaking the query to improve.

select
cast(avg(delivery.score)as decimal (10,2)) as [Delivery],
cast(avg(listeningskill.score)as decimal (10,2)) as [Listening skills],
cast(avg(communicationskill.score)as decimal (10,2)) as [Communication skills]
from
(
select a.operatorid,b.score, a.scoreid
from operator_analysis a
inner join (select * from #tempOperatorlist) d on d.operatorid=a.operatorid and a.datemonitored between @weekstartdate and dateadd (dd,6,@weekstartdate)
inner join operator_score b on a.scoreid = b.scoreid
inner join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid and c.subcategorydescription = 'delivery'
) delivery
join
(
select a.operatorid,b.score, a.scoreid from operator_analysis a
inner join (select * from #tempOperatorlist) d on d.operatorid=a.operatorid and a.datemonitored between @weekstartdate and dateadd (dd,6,@weekstartdate)
inner join operator_score b on a.scoreid = b.scoreid
inner join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid and c.subcategorydescription = 'listening skill'
) listeningskill on delivery.scoreid=listeningskill.scoreid and delivery.operatorid=listeningskill.operatorid
join
(
select a.operatorid,b.score, a.scoreid from operator_analysis a
inner join (select * from #tempOperatorlist) d on d.operatorid=a.operatorid and a.datemonitored between @weekstartdate and dateadd (dd,6,@weekstartdate)
inner join operator_score b on a.scoreid = b.scoreid
inner join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid and c.subcategorydescription = 'communication skill'
) communicationskill on delivery.scoreid=communicationskill.scoreid and delivery.operatorid=communicationskill.operatorid
0
EdwardPeter
Asked:
EdwardPeter
  • 7
  • 5
1 Solution
 
HilaireCommented:
Please try

Please try

select
cast(avg(case when c.subcategorydescription = 'delivery' then b.score else 0 end)as decimal (10,2)) as [Delivery],
cast(avg(case when c.subcategorydescription = 'listening skill' then b.score else 0 end)as decimal (10,2)) as [Listening skills],
cast(avg(case when c.subcategorydescription = 'communication skill' then b.score else 0 end)as decimal (10,2)) as [Communication skills]
from operator_analysis a
inner join (select * from #tempOperatorlist) d on d.operatorid=a.operatorid and a.datemonitored between @weekstartdate and dateadd (dd,6,@weekstartdate)
inner join operator_score b on a.scoreid = b.scoreid
inner join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
0
 
HilaireCommented:
Just realized that the 'else 0 end' is not a good idea... at all !!
It would be better with 'else null end' which is the default 'else' when you don't provide one.

Please use this version instead

select
cast(avg(case when c.subcategorydescription = 'delivery' then b.score end) as decimal (10,2)) as [Delivery],
cast(avg(case when c.subcategorydescription = 'listening skill' then b.score end) as decimal (10,2)) as [Listening skills],
cast(avg(case when c.subcategorydescription = 'communication skill' then b.score end) as decimal (10,2)) as [Communication skills]
from operator_analysis a
inner join (select * from #tempOperatorlist) d on d.operatorid=a.operatorid and a.datemonitored between @weekstartdate and dateadd (dd,6,@weekstartdate)
inner join operator_score b on a.scoreid = b.scoreid
inner join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid

0
 
HilaireCommented:
"else 0 " would be OK with another aggregate function like SUM, MAX, but not with AVG ...
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
EdwardPeterAuthor Commented:
Hilaire,

You're simple a genius.

small problem though, it generated null values on the output. what can we do to troubleshoot?

Thanks.
0
 
EdwardPeterAuthor Commented:
Sample column:

Select
cast(avg((case when c.subcategorydescription = 'delivery' then b.score end +
case when c.subcategorydescription = 'listening skill' then b.score end+
case when c.subcategorydescription = 'communication skill' then b.score end)/3)*@customerinteraction as decimal(10,2))as [Average Total Customer Interaction],
0
 
HilaireCommented:
just replace

cast(avg(....) as decimal(10,2)) as alias

with

cast(ISNULL(avg(....),0) as decimal(10,2)) as alias
0
 
EdwardPeterAuthor Commented:
Hilaire,

Is it for the whole columns or individual cast statement?

as a whole (single column)

cast(isnull(avg((case when c.subcategorydescription = 'delivery' then b.score end +
case when c.subcategorydescription = 'listening skill' then b.score end+
case when c.subcategorydescription = 'communication skill' then b.score end)/3)*@customerinteraction,0) as decimal(10,2))as [Average Total Customer Interaction],

Thanks.
0
 
HilaireCommented:
I think you don't need to divide by 3 because each row can have only ONE description out of the 3 possible descriptions so the expression
>>case when c.subcategorydescription = 'delivery' then b.score end +
case when c.subcategorydescription = 'listening skill' then b.score end+
case when c.subcategorydescription = 'communication skill' then b.score end<<
is ALWAYS NULL

instead, I'd write it as follows

select
cast(avg(case when c.subcategorydescription = 'delivery' then b.score end) as decimal (10,2)) as [Delivery],
cast(avg(case when c.subcategorydescription = 'listening skill' then b.score end) as decimal (10,2)) as [Listening skills],
cast(avg(case when c.subcategorydescription = 'communication skill' then b.score end) as decimal (10,2)) as [Communication skills],
cast(avg(b.score) as decimal (10,2)) as [Average Total Customer Interaction]
from operator_analysis a
inner join (select * from #tempOperatorlist) d on d.operatorid=a.operatorid and a.datemonitored between @weekstartdate and dateadd (dd,6,@weekstartdate)
inner join operator_score b on a.scoreid = b.scoreid
inner join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription in ('delivery', 'listening skill', 'communication skill' )

-- the additional where clause ensures you don't compute AVG for other descriptions


0
 
EdwardPeterAuthor Commented:
Hilaire,

Great idea, simply a genius!!!

Sadly there are other columns like Timely, opening, and the number to divide them is base on the number of columns we are averaging.

Will this also work for our scenario?

Thanks.
0
 
HilaireCommented:
>>Will this also work for our scenario?<<
I think so, but it's hard to say for sure whithout seeing the whole thing

if you think the where clause is a problem, just use

cast(avg(case when c.subcategorydescription in ('delivery', 'listening skill', 'communication skill')  then b.score end) as decimal (10,2)) as [Average Total Customer Interaction]

instead of

cast(avg(b.score) as decimal (10,2)) as [Average Total Customer Interaction]

Thus you can get rid of the where clause
0
 
EdwardPeterAuthor Commented:
Hilaire,

You're the man !!! You're simply the best !!!

Thanks so much for your time and patience.
0
 
HilaireCommented:
Glad I could help and thanks for the poinks !
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now