November 23, 2009 at 11:16 am
I'm trying to optimize a top 50 accounts receivable report. Currently the report makes 51 calls to the database every time it's run: one call for the main dataset, and then for each row, it needs to find the top three managers associated with the top 50 clients that owe us money. I'd like to put everything into one query if it's possible to do so efficiently with the hope of making the report execute more quickly.
I wrote what I thought would be a solution. It works for one project code hard coded in but then when I try to reference the derived table "core" in the derived table in my join, "top3", I get the old "The multi-part identifier "core.client_code" could not be bound", and that's no fun!
Any advice? Am I going in the wrong direction? There's no need to rewrite my query for me, just point me in the right direction...
The problem is, some query with some aggregates on it, and then for one of the contexts on that aggregate, I want to left join on the top 3 managers, kind of like:
select client, sum(balance), manager1, manager2, manager3
from...
select core.*,
MAX(CASE WHEN top3.[RANK] = 1 THEN top3.lname ELSE '' END) AS 'top_pic_lname1',
MAX(CASE WHEN top3.[RANK] = 2 THEN ISNULL(top3.lname, '') ELSE '' END) AS 'top_pic_lname2',
MAX(CASE WHEN top3.[RANK] = 3 THEN ISNULL(top3.lname, '') ELSE '' END) AS 'top_pic_lname3'
from (
select top 50
are.client_code,
are.client_name,
eo.country_id,
SUM(are.balance) as 'balance',
SUM(are.bal30) as 'bal30',
SUM(are.bal60) as 'bal60',
SUM(are.bal90) + SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal60plus',
SUM(are.bal90) as 'bal90',
SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal90plus',
SUM(are.bal120) as bal120,
SUM(are.bal150) + SUM(are.bal180) + sum(are.bal180plus) as bal120plus,
SUM(are.bal150) as bal150,
SUM(are.bal180) + SUM(are.bal180plus) as bal150plus,
SUM(are.bal180) as bal180,
SUM(are.bal180plus) as bal180plus
FROM AR_Report_Employee_Dev are
JOIN dbo.vw_EmployeeOffices eo ON eo.emp_code = are.emp_code
WHERE eo.country_id = '9999'
and are.balance <> 0
and are.resp_type_code = 'PIC'
group by are.client_code, are.client_name, eo.country_id
order by sum(are.balance) desc
) as core
LEFT JOIN (
SELECT TOP 3
are2.client_code,
e.emp_code,
e.lname,
SUM(are2.balance) AS balance,
ROW_NUMBER() OVER (
ORDER BY SUM(are2.balance) DESC
) AS [rank]
FROM AR_Report_Employee_Dev are2
LEFT JOIN employees e ON are2.emp_code = e.emp_code
WHERE are2.client_code = core.client_code
AND are2.resp_type_code = 'PIC'
GROUP BY are2.client_code, e.lname, e.emp_code
) top3 ON top3.client_code = core.client_code
GROUP BY
core.client_code,
core.client_name,
core.country_id,
core.balance,
core.bal30,
core.bal60,
core.bal60plus,
core.bal90,
core.bal90plus,
core.bal120,
core.bal120plus,
core.bal150,
core.bal150plus,
core.bal180,
core.bal180plus
order by core.balance desc
November 23, 2009 at 11:22 am
this may work
just a quick and dirty
With Core as (select top 50
are.client_code,
are.client_name,
eo.country_id,
SUM(are.balance) as 'balance',
SUM(are.bal30) as 'bal30',
SUM(are.bal60) as 'bal60',
SUM(are.bal90) + SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal60plus',
SUM(are.bal90) as 'bal90',
SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal90plus',
SUM(are.bal120) as bal120,
SUM(are.bal150) + SUM(are.bal180) + sum(are.bal180plus) as bal120plus,
SUM(are.bal150) as bal150,
SUM(are.bal180) + SUM(are.bal180plus) as bal150plus,
SUM(are.bal180) as bal180,
SUM(are.bal180plus) as bal180plus
FROM AR_Report_Employee_Dev are
JOIN dbo.vw_EmployeeOffices eo ON eo.emp_code = are.emp_code
WHERE eo.country_id = '9999'
and are.balance <> 0
and are.resp_type_code = 'PIC'
group by are.client_code, are.client_name, eo.country_id
),top3 as (
SELECT TOP 3
are2.client_code,
e.emp_code,
e.lname,
SUM(are2.balance) AS balance,
ROW_NUMBER() OVER (
ORDER BY SUM(are2.balance) DESC
) AS [rank]
FROM AR_Report_Employee_Dev are2
LEFT JOIN employees e ON are2.emp_code = e.emp_code
WHERE are2.client_code = core.client_code
AND are2.resp_type_code = 'PIC'
GROUP BY are2.client_code, e.lname, e.emp_code
)
select core.*,
MAX(CASE WHEN t.[RANK] = 1 THEN t.lname ELSE '' END) AS 'top_pic_lname1',
MAX(CASE WHEN t.[RANK] = 2 THEN ISNULL(t.lname, '') ELSE '' END) AS 'top_pic_lname2',
MAX(CASE WHEN t.[RANK] = 3 THEN ISNULL(t.lname, '') ELSE '' END) AS 'top_pic_lname3'
from core c
LEFT JOIN top3 t
ON t.client_code = c.client_code
GROUP BY
c.client_code,
c.client_name,
c.country_id,
c.balance,
c.bal30,
c.bal60,
c.bal60plus,
c.bal90,
c.bal90plus,
c.bal120,
c.bal120plus,
c.bal150,
c.bal150plus,
c.bal180,
c.bal180plus
order by c.balance desc
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2009 at 11:26 am
Thanks for the response however that results in the exact same error I have now.
It seems you're trying to use CTE's which sounds like a reasonable suggestion. I'll pursue that avenue and see if that leads to a solution.
November 23, 2009 at 11:34 am
sorry about that
missed one line
LEFT JOIN employees e ON are2.emp_code = e.emp_code
WHERE are2.client_code = core.client_code
should be
LEFT JOIN employees e ON are2.emp_code = e.emp_code
Inner Join Core C on are2.client_code = c.client_code
Naturally you would need to fix the where and subsequent and clauses in the top3 cte
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2009 at 11:52 am
yeah I see what you're trying to do...sort of...
interestingly, for the first three rows, the top manager shows up but in the wrong column. The first row has the top manager in top_pic_lname1, which is correct. Then the 2nd row has it's top manager in top_pic_lname2, and the third row has it's top manager in top_pic_lname3, and then the rest are blank...
With Core as (select top 50
are.client_code,
are.client_name,
eo.country_id,
SUM(are.balance) as 'balance',
SUM(are.bal30) as 'bal30',
SUM(are.bal60) as 'bal60',
SUM(are.bal90) + SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal60plus',
SUM(are.bal90) as 'bal90',
SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal90plus',
SUM(are.bal120) as bal120,
SUM(are.bal150) + SUM(are.bal180) + sum(are.bal180plus) as bal120plus,
SUM(are.bal150) as bal150,
SUM(are.bal180) + SUM(are.bal180plus) as bal150plus,
SUM(are.bal180) as bal180,
SUM(are.bal180plus) as bal180plus
FROM AR_Report_Employee_Dev are
JOIN dbo.vw_EmployeeOffices eo ON eo.emp_code = are.emp_code
WHERE eo.country_id = '9999'
and are.resp_type_code = 'PIC'
group by are.client_code, are.client_name, eo.country_id
ORDER BY SUM(are.balance) desc
),
top3 AS (
SELECT TOP 3
are2.client_code,
e.emp_code,
e.lname,
SUM(are2.balance) AS balance,
ROW_NUMBER() OVER (
ORDER BY SUM(are2.balance) DESC
) AS [rank]
FROM AR_Report_Employee_Dev are2
LEFT JOIN employees e ON are2.emp_code = e.emp_code
Inner Join Core C on are2.client_code = c.client_code
AND are2.resp_type_code = 'PIC'
GROUP BY are2.client_code, e.lname, e.emp_code
)
select c.*,
MAX(CASE WHEN t.[RANK] = 1 THEN t.lname ELSE '' END) AS 'top_pic_lname1',
MAX(CASE WHEN t.[RANK] = 2 THEN ISNULL(t.lname, '') ELSE '' END) AS 'top_pic_lname2',
MAX(CASE WHEN t.[RANK] = 3 THEN ISNULL(t.lname, '') ELSE '' END) AS 'top_pic_lname3'
from core c
LEFT JOIN top3 t ON t.client_code = c.client_code
GROUP BY
c.client_code,
c.client_name,
c.country_id,
c.balance,
c.bal30,
c.bal60,
c.bal60plus,
c.bal90,
c.bal90plus,
c.bal120,
c.bal120plus,
c.bal150,
c.bal150plus,
c.bal180,
c.bal180plus
order by c.balance desc
November 23, 2009 at 12:04 pm
You should be able to manipulate the query to be able to get your desired results.
The idea of the CTE is to make the code more readable and less complex. Without really knowing the data or table structure - this would be the only thing i could suggest for the query.:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2009 at 12:08 pm
The only way I can get it to work is to use subqueries, but it is one UGLY query:
select *
from (
select top 50
are.client_code,
are.client_name,
eo.country_id,
(
SELECT o1.lname
FROM (
SELECT TOP 3 e1.emp_code, e1.lname, SUM(are1.balance) AS balance,
ROW_NUMBER() OVER (
ORDER BY SUM(are1.balance) DESC
) AS _rank
FROM AR_Report_Employee are1
LEFT JOIN employees e1 ON are1.emp_code = e1.emp_code
WHERE are1.client_Code = are.client_code
AND are1.resp_type_code = 'PIC'
AND are1.balance > 0
GROUP BY e1.lname, e1.emp_code
) o1
WHERE o1._rank = 1
) AS 'top1pic',
(
SELECT o2.lname
FROM (
SELECT TOP 3 e2.emp_code, e2.lname, SUM(are2.balance) AS balance,
ROW_NUMBER() OVER (
ORDER BY SUM(are2.balance) DESC
) AS _rank
FROM AR_Report_Employee are2
LEFT JOIN employees e2 ON are2.emp_code = e2.emp_code
WHERE are2.client_Code = are.client_code
AND are2.resp_type_code = 'PIC'
AND are2.balance > 0
GROUP BY e2.lname, e2.emp_code
) o2
WHERE o2._rank = 2
) AS 'top2pic',
(
SELECT o2.lname
FROM (
SELECT TOP 3 e2.emp_code, e2.lname, SUM(are2.balance) AS balance,
ROW_NUMBER() OVER (
ORDER BY SUM(are2.balance) DESC
) AS _rank
FROM AR_Report_Employee are2
LEFT JOIN employees e2 ON are2.emp_code = e2.emp_code
WHERE are2.client_Code = are.client_code
AND are2.resp_type_code = 'PIC'
AND are2.balance > 0
GROUP BY e2.lname, e2.emp_code
) o2
WHERE o2._rank = 3
) AS 'top3pic',
SUM(are.balance) as 'balance',
SUM(are.bal30) as 'bal30',
SUM(are.bal60) as 'bal60',
SUM(are.bal90) + SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal60plus',
SUM(are.bal90) as 'bal90',
SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal90plus',
SUM(are.bal120) as bal120,
SUM(are.bal150) + SUM(are.bal180) + sum(are.bal180plus) as bal120plus,
SUM(are.bal150) as bal150,
SUM(are.bal180) + SUM(are.bal180plus) as bal150plus,
SUM(are.bal180) as bal180,
SUM(are.bal180plus) as bal180plus
from AR_Report_Employee_dev are
JOIN dbo.vw_EmployeeOffices eo ON eo.emp_code = are.emp_code
where eo.country_id = '9999'
and are.resp_type_code = 'PIC'
group by are.client_code, are.client_name, eo.country_id
order by sum(are.balance) desc
) as core
order by core.balance desc
I suppose hiding thus ugly stepchild of a query away in a stored procedure is acceptable. It just looks awful...
November 23, 2009 at 12:14 pm
Yeah I very much appreciate your time, I took your suggestion with the CTE and I've tried quite a few things with it, none of which were any more successful than what I was doing with the derived tables in the more traditional looking query. I'll keep hacking it though. I know it's next to impossible for you to assist without having the tables in front of you.
I considered writing up a simpler example that focuses only on solving the problem with some tables and fake data. I'm almost to that point actually, because even if I hack this together I'll have absolutely no idea why it's working which is always a fail in a way. I'm not in a rush here (it's a slow week anyway) so might as well figure it out...
November 23, 2009 at 3:42 pm
I setup some tables with test data based on your query
minor change
, ROW_NUMBER() OVER (Partition by e.emp_code ORDER BY SUM(are2.balance) DESC ) AS [Rank]
This might be a little closer to what you need.
With Core as (
select top 50 are.client_code,are.client_name,eo.country_id
,SUM(are.balance) as 'balance'
,SUM(are.bal30) as 'bal30'
,SUM(are.bal60) as 'bal60'
,SUM(are.bal90) + SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal60plus'
,SUM(are.bal90) as 'bal90'
,SUM(are.bal120) + SUM(are.bal150) + SUM(are.bal180) + SUM(are.bal180plus) as 'bal90plus'
,SUM(are.bal120) as bal120
,SUM(are.bal150) + SUM(are.bal180) + sum(are.bal180plus) as bal120plus
,SUM(are.bal150) as bal150
,SUM(are.bal180) + SUM(are.bal180plus) as bal150plus
,SUM(are.bal180) as bal180
,SUM(are.bal180plus) as bal180plus
FROM #AR_Report_Employee_Dev are
Inner JOIN #EmployeeOffices eo
ON eo.emp_code = are.emp_code
And eo.country_id = '9999'
Where are.resp_type_code = 'PIC'
Group By are.client_code, are.client_name, eo.country_id
),top3 AS (
SELECT are2.client_code ,e.emp_code, e.lname
, SUM(are2.balance) AS balance
, ROW_NUMBER() OVER (Partition by e.emp_code ORDER BY SUM(are2.balance) DESC ) AS [Rank]
FROM #AR_Report_Employee_Dev are2
--Inner Join Core C --this join didn't seem necessary
--on are2.client_code = c.client_code
LEFT JOIN #employees e
ON are2.emp_code = e.emp_code
Where are2.resp_type_code = 'PIC'
GROUP BY are2.client_code, e.lname, e.emp_code
)
select c.*
,max(t1.lname) AS 'top_pic_lname1'
,max(t2.lname) AS 'top_pic_lname2'
,max(t3.lname) AS 'top_pic_lname3'
from core c
Left Outer Join top3 t
On t.client_code = c.client_code
Left Outer JOIN top3 t1
ON t1.client_code = c.client_code
And t1.rank = 1
Left Outer Join top3 t2
On t2.client_code = c.client_code
And t2.rank = 2
Left Outer Join top3 t3
On t3.client_code = c.client_code
And t3.rank = 3
GROUP BY c.client_code,c.client_name,c.country_id,c.balance,c.bal30,c.bal60,c.bal60plus,c.bal90, c.bal90plus,c.bal120
,c.bal120plus,c.bal150,c.bal150plus,c.bal180,c.bal180plus
order by c.balance desc
I'm not sure if it is quite there yet, but this might be closer to your needs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2009 at 10:42 pm
Nice, much closer. In that line you changed, I modified it to partition by client code instead of employee code and that got it very, very close:
ROW_NUMBER() OVER (PARTITION BY are2.client_code ORDER BY SUM(are2.balance) DESC) AS [Rank]
There are a few names listed in the 2 and 3 slots that the current report doesn't show, but it's 90% correct and I bet I can figure it out.
I hadn't thought to use PARTITION BY so thanks again for your help with this. That plus the CTE is the critical features that makes this work and is cleaner than the subqueries. The performance is a big improvement over the current report which can take between 10-15 seconds whereas this one runs in 1-2.
November 24, 2009 at 10:06 am
Thanks for the update. Glad I could be of some help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply