QueryHelp

N

Nero

HI there....
I have two questions based on the below SQL statement

1. the query takes too long to run, is the query too complex and if so how
do I make it better so that it responds faster?
2. The first part of the WHERE statement matches the month in one table to
the month in another table, i need this to match the month and the year, how
do I do this?

note: i used the query wizard to start the query and then aded in the
formulas i needed.

SQL Statement

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date], [Agent_
Details].Region, Sum(DailyScheduledTime.ScheduledTime) AS TotalScheduledTime,
Sum(ActivityTbl.ActualCalls) AS ActualCalls, Avg(ActivityTbl.ActualAht) AS
AvgAht, Max(ActivityTbl.ActualAht) AS MaxAht, Avg(targets.AHTTarget) AS
AHTTarget, TotalScheduledTime/AHTTarget AS TargetCalls,
TargetCalls/ActualCalls*100 AS productivity, Avg(targets.ProdBudget) AS
ProdBudget, Productivity/prodBudget*100 AS ProdOutcome, AHTTArget/MaxAht*100
AS Efficiency, Avg(targets.AHTBudget) AS EfficiencyBudget,
Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome, [Agent_
Details].JobTitle
FROM targets, ([Agent_ Details] INNER JOIN DailyScheduledTime ON [Agent_
Details].AgentId = DailyScheduledTime.AgentId) INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId
WHERE
(((DatePart("m",[ACtivityTbl].[date]))=(DatePart("m",[targets].[date])))) AND
([Agent_ Details].JobTitle)=(targets.serviceArea)
GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy'), [Agent_ Details].Region,
Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1, [Agent_
Details].JobTitle;
 
J

John Spencer

One way to make this more efficient would be to join Targets to the other
tables involved. That gets rid of the cartesian join, which will create a
large number of records for the where clause to filter down to your desired
results.

Part 2 is the easiest. Use the format function to get the year and the
month instead of the datepart function.

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

I'm not sure that I got the FROM clause correctly structured in the SQL
below, but you can try the idea.

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date]
, [Agent_ Details].Region
, Sum(DailyScheduledTime.ScheduledTime) AS TotalScheduledTime
, Sum(ActivityTbl.ActualCalls) AS ActualCalls
, Avg(ActivityTbl.ActualAht) AS AvgAht
, Max(ActivityTbl.ActualAht) AS MaxAht
, Avg(targets.AHTTarget) AS AHTTarget
, TotalScheduledTime/AHTTarget AS TargetCalls
, TargetCalls/ActualCalls*100 AS productivity
, Avg(targets.ProdBudget) AS ProdBudget
, Productivity/prodBudget*100 AS ProdOutcome
, AHTTArget/MaxAht*100 AS Efficiency
, Avg(targets.AHTBudget) AS EfficiencyBudget
, Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome
, [Agent_ Details].JobTitle

FROM (([Agent_ Details] INNER JOIN DailyScheduledTime
ON [Agent_ Details].AgentId = DailyScheduledTime.AgentId)
INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId) INNER JOIN Targets
ON [Agent_ Details].JobTitle = Targets.ServiceArea

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy')
, [Agent_ Details].Region
, Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1
, [Agent_ Details].JobTitle;


Nero said:
HI there....
I have two questions based on the below SQL statement

1. the query takes too long to run, is the query too complex and if so how
do I make it better so that it responds faster?
2. The first part of the WHERE statement matches the month in one table to
the month in another table, i need this to match the month and the year,
how
do I do this?

note: i used the query wizard to start the query and then aded in the
formulas i needed.

SQL Statement

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date],
[Agent_
Details].Region, Sum(DailyScheduledTime.ScheduledTime) AS
TotalScheduledTime,
Sum(ActivityTbl.ActualCalls) AS ActualCalls, Avg(ActivityTbl.ActualAht) AS
AvgAht, Max(ActivityTbl.ActualAht) AS MaxAht, Avg(targets.AHTTarget) AS
AHTTarget, TotalScheduledTime/AHTTarget AS TargetCalls,
TargetCalls/ActualCalls*100 AS productivity, Avg(targets.ProdBudget) AS
ProdBudget, Productivity/prodBudget*100 AS ProdOutcome,
AHTTArget/MaxAht*100
AS Efficiency, Avg(targets.AHTBudget) AS EfficiencyBudget,
Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome, [Agent_
Details].JobTitle
FROM targets, ([Agent_ Details] INNER JOIN DailyScheduledTime ON [Agent_
Details].AgentId = DailyScheduledTime.AgentId) INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId
WHERE
(((DatePart("m",[ACtivityTbl].[date]))=(DatePart("m",[targets].[date]))))
AND
([Agent_ Details].JobTitle)=(targets.serviceArea)
GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy'), [Agent_ Details].Region,
Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1, [Agent_
Details].JobTitle;
Click to expand...
 
N

Nero

Thanks for the help John.... The Where clause works well however the query is
still running too slow.

John Spencer said:
One way to make this more efficient would be to join Targets to the other
tables involved. That gets rid of the cartesian join, which will create a
large number of records for the where clause to filter down to your desired
results.

Part 2 is the easiest. Use the format function to get the year and the
month instead of the datepart function.

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

I'm not sure that I got the FROM clause correctly structured in the SQL
below, but you can try the idea.

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date]
, [Agent_ Details].Region
, Sum(DailyScheduledTime.ScheduledTime) AS TotalScheduledTime
, Sum(ActivityTbl.ActualCalls) AS ActualCalls
, Avg(ActivityTbl.ActualAht) AS AvgAht
, Max(ActivityTbl.ActualAht) AS MaxAht
, Avg(targets.AHTTarget) AS AHTTarget
, TotalScheduledTime/AHTTarget AS TargetCalls
, TargetCalls/ActualCalls*100 AS productivity
, Avg(targets.ProdBudget) AS ProdBudget
, Productivity/prodBudget*100 AS ProdOutcome
, AHTTArget/MaxAht*100 AS Efficiency
, Avg(targets.AHTBudget) AS EfficiencyBudget
, Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome
, [Agent_ Details].JobTitle

FROM (([Agent_ Details] INNER JOIN DailyScheduledTime
ON [Agent_ Details].AgentId = DailyScheduledTime.AgentId)
INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId) INNER JOIN Targets
ON [Agent_ Details].JobTitle = Targets.ServiceArea

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy')
, [Agent_ Details].Region
, Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1
, [Agent_ Details].JobTitle;


Nero said:
HI there....
I have two questions based on the below SQL statement

1. the query takes too long to run, is the query too complex and if so how
do I make it better so that it responds faster?
2. The first part of the WHERE statement matches the month in one table to
the month in another table, i need this to match the month and the year,
how
do I do this?

note: i used the query wizard to start the query and then aded in the
formulas i needed.

SQL Statement

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date],
[Agent_
Details].Region, Sum(DailyScheduledTime.ScheduledTime) AS
TotalScheduledTime,
Sum(ActivityTbl.ActualCalls) AS ActualCalls, Avg(ActivityTbl.ActualAht) AS
AvgAht, Max(ActivityTbl.ActualAht) AS MaxAht, Avg(targets.AHTTarget) AS
AHTTarget, TotalScheduledTime/AHTTarget AS TargetCalls,
TargetCalls/ActualCalls*100 AS productivity, Avg(targets.ProdBudget) AS
ProdBudget, Productivity/prodBudget*100 AS ProdOutcome,
AHTTArget/MaxAht*100
AS Efficiency, Avg(targets.AHTBudget) AS EfficiencyBudget,
Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome, [Agent_
Details].JobTitle
FROM targets, ([Agent_ Details] INNER JOIN DailyScheduledTime ON [Agent_
Details].AgentId = DailyScheduledTime.AgentId) INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId
WHERE
(((DatePart("m",[ACtivityTbl].[date]))=(DatePart("m",[targets].[date]))))
AND
([Agent_ Details].JobTitle)=(targets.serviceArea)
GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy'), [Agent_ Details].Region,
Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1, [Agent_
Details].JobTitle;
Click to expand...
Click to expand...
 
J

John Spencer

Do you have indexes on all the fields you are using in joins?

Do you have indexes on the fields you are Grouping By (not sure this one
will help much)?

The bottle neck is probably due to the where clause, but without having your
tables and your data I can't see a way to get around the using the format
calls. Also, try dropping DISTINCTRow (again that shouldn't help much) - I
don't think you need it since you are doing an aggregate query.



Nero said:
Thanks for the help John.... The Where clause works well however the query
is
still running too slow.

John Spencer said:
One way to make this more efficient would be to join Targets to the other
tables involved. That gets rid of the cartesian join, which will create a
large number of records for the where clause to filter down to your
desired
results.

Part 2 is the easiest. Use the format function to get the year and the
month instead of the datepart function.

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

I'm not sure that I got the FROM clause correctly structured in the SQL
below, but you can try the idea.

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date]
, [Agent_ Details].Region
, Sum(DailyScheduledTime.ScheduledTime) AS TotalScheduledTime
, Sum(ActivityTbl.ActualCalls) AS ActualCalls
, Avg(ActivityTbl.ActualAht) AS AvgAht
, Max(ActivityTbl.ActualAht) AS MaxAht
, Avg(targets.AHTTarget) AS AHTTarget
, TotalScheduledTime/AHTTarget AS TargetCalls
, TargetCalls/ActualCalls*100 AS productivity
, Avg(targets.ProdBudget) AS ProdBudget
, Productivity/prodBudget*100 AS ProdOutcome
, AHTTArget/MaxAht*100 AS Efficiency
, Avg(targets.AHTBudget) AS EfficiencyBudget
, Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome
, [Agent_ Details].JobTitle

FROM (([Agent_ Details] INNER JOIN DailyScheduledTime
ON [Agent_ Details].AgentId = DailyScheduledTime.AgentId)
INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId) INNER JOIN Targets
ON [Agent_ Details].JobTitle = Targets.ServiceArea

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy')
, [Agent_ Details].Region
, Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1
, [Agent_ Details].JobTitle;


Nero said:
HI there....
I have two questions based on the below SQL statement

1. the query takes too long to run, is the query too complex and if so
how
do I make it better so that it responds faster?
2. The first part of the WHERE statement matches the month in one table
to
the month in another table, i need this to match the month and the
year,
how
do I do this?

note: i used the query wizard to start the query and then aded in the
formulas i needed.

SQL Statement

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date],
[Agent_
Details].Region, Sum(DailyScheduledTime.ScheduledTime) AS
TotalScheduledTime,
Sum(ActivityTbl.ActualCalls) AS ActualCalls, Avg(ActivityTbl.ActualAht)
AS
AvgAht, Max(ActivityTbl.ActualAht) AS MaxAht, Avg(targets.AHTTarget) AS
AHTTarget, TotalScheduledTime/AHTTarget AS TargetCalls,
TargetCalls/ActualCalls*100 AS productivity, Avg(targets.ProdBudget) AS
ProdBudget, Productivity/prodBudget*100 AS ProdOutcome,
AHTTArget/MaxAht*100
AS Efficiency, Avg(targets.AHTBudget) AS EfficiencyBudget,
Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome, [Agent_
Details].JobTitle
FROM targets, ([Agent_ Details] INNER JOIN DailyScheduledTime ON
[Agent_
Details].AgentId = DailyScheduledTime.AgentId) INNER JOIN ActivityTbl
ON
[Agent_ Details].AgentId = ActivityTbl.AgentId
WHERE
(((DatePart("m",[ACtivityTbl].[date]))=(DatePart("m",[targets].[date]))))
AND
([Agent_ Details].JobTitle)=(targets.serviceArea)
GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy'), [Agent_
Details].Region,
Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1, [Agent_
Details].JobTitle;
Click to expand...
Click to expand...
Click to expand...
 
N

Nero

thanks again.. that seemed to have helped somewhat.... by the way, what does
the DISTINCTRow do?

John Spencer said:
Do you have indexes on all the fields you are using in joins?

Do you have indexes on the fields you are Grouping By (not sure this one
will help much)?

The bottle neck is probably due to the where clause, but without having your
tables and your data I can't see a way to get around the using the format
calls. Also, try dropping DISTINCTRow (again that shouldn't help much) - I
don't think you need it since you are doing an aggregate query.



Nero said:
Thanks for the help John.... The Where clause works well however the query
is
still running too slow.

John Spencer said:
One way to make this more efficient would be to join Targets to the other
tables involved. That gets rid of the cartesian join, which will create a
large number of records for the where clause to filter down to your
desired
results.

Part 2 is the easiest. Use the format function to get the year and the
month instead of the datepart function.

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

I'm not sure that I got the FROM clause correctly structured in the SQL
below, but you can try the idea.

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date]
, [Agent_ Details].Region
, Sum(DailyScheduledTime.ScheduledTime) AS TotalScheduledTime
, Sum(ActivityTbl.ActualCalls) AS ActualCalls
, Avg(ActivityTbl.ActualAht) AS AvgAht
, Max(ActivityTbl.ActualAht) AS MaxAht
, Avg(targets.AHTTarget) AS AHTTarget
, TotalScheduledTime/AHTTarget AS TargetCalls
, TargetCalls/ActualCalls*100 AS productivity
, Avg(targets.ProdBudget) AS ProdBudget
, Productivity/prodBudget*100 AS ProdOutcome
, AHTTArget/MaxAht*100 AS Efficiency
, Avg(targets.AHTBudget) AS EfficiencyBudget
, Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome
, [Agent_ Details].JobTitle

FROM (([Agent_ Details] INNER JOIN DailyScheduledTime
ON [Agent_ Details].AgentId = DailyScheduledTime.AgentId)
INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId) INNER JOIN Targets
ON [Agent_ Details].JobTitle = Targets.ServiceArea

WHERE Format(ActivityTbl.Date,"yyyymm") = Format(Targets.Date,"yyyymm")

GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy')
, [Agent_ Details].Region
, Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1
, [Agent_ Details].JobTitle;


HI there....
I have two questions based on the below SQL statement

1. the query takes too long to run, is the query too complex and if so
how
do I make it better so that it responds faster?
2. The first part of the WHERE statement matches the month in one table
to
the month in another table, i need this to match the month and the
year,
how
do I do this?

note: i used the query wizard to start the query and then aded in the
formulas i needed.

SQL Statement

SELECT DISTINCTROW Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date],
[Agent_
Details].Region, Sum(DailyScheduledTime.ScheduledTime) AS
TotalScheduledTime,
Sum(ActivityTbl.ActualCalls) AS ActualCalls, Avg(ActivityTbl.ActualAht)
AS
AvgAht, Max(ActivityTbl.ActualAht) AS MaxAht, Avg(targets.AHTTarget) AS
AHTTarget, TotalScheduledTime/AHTTarget AS TargetCalls,
TargetCalls/ActualCalls*100 AS productivity, Avg(targets.ProdBudget) AS
ProdBudget, Productivity/prodBudget*100 AS ProdOutcome,
AHTTArget/MaxAht*100
AS Efficiency, Avg(targets.AHTBudget) AS EfficiencyBudget,
Efficiency/EfficiencyBudget*100 AS EfficiencyOutcome, [Agent_
Details].JobTitle
FROM targets, ([Agent_ Details] INNER JOIN DailyScheduledTime ON
[Agent_
Details].AgentId = DailyScheduledTime.AgentId) INNER JOIN ActivityTbl
ON
[Agent_ Details].AgentId = ActivityTbl.AgentId
WHERE
(((DatePart("m",[ACtivityTbl].[date]))=(DatePart("m",[targets].[date]))))
AND
([Agent_ Details].JobTitle)=(targets.serviceArea)
GROUP BY Format$(ActivityTbl.Date,'mmmm yyyy'), [Agent_
Details].Region,
Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1, [Agent_
Details].JobTitle;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

QueryHelp 5
Sort by Month in a query 3
Query returns empty columns 2
Awkward Query Idea / Help!? Percentiles. 2
Crosstab query help please 1
Help with query 6
Crosstab query help please 0
Help with criteria on select query 13

Top