SQL Query for Optimization (SQL Server 2000)
Code:
--select 0 datrow ,
select
c.clientid,
sum(isnull(d1.points, 0)) delta1,
case when ( select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()) - 2) ) = 0 or not exists(select points from t_dsr where (year(servicedate)) = (year(getdate()) - 2) ) then
0
else
sum(isnull(d1.points, 0)) / (select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()) - 2)) * 100
end deltapercent1,
sum(isnull(d2.points, 0)) delta2,
case when ( select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()) - 1) ) = 0 or not exists(select points from t_dsr where (year(servicedate)) = (year(getdate()) -1 ) ) then
0
else
sum(isnull(d2.points, 0)) / (select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()) - 1)) * 100
end deltapercent2,
sum(isnull(d3.points, 0)) delta3,
case when ( select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()) ) ) = 0 or not exists(select points from t_dsr where (year(servicedate)) = (year(getdate()) ) ) then
0
else
sum(isnull(d3.points, 0)) / (select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()))) * 100
end deltapercent3
from
m_client c left join
(select td1.clientid, td1.points
from
t_dsr td1
where
(year(td1.servicedate)) = (year(getdate()) - 2) ) d1
on
c.clientid = d1.clientid
left join
(select td2.clientid, td2.points
from
t_dsr td2
where
(year(td2.servicedate)) = (year(getdate()) - 1) ) d2
on
c.clientid = d2.clientid
left join
(select td3.clientid, td3.points
from
t_dsr td3
where
(year(td3.servicedate)) = (year(getdate()) ) ) d3
on
c.clientid = d3.clientid
group by
c.clientid
/*
union
select 1 datrow, 'Total' clientid,
(select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()) - 2) ) delta1, 100 deltapercent,
(select sum(points) from t_dsr where (year(servicedate)) = (year(getdate()) - 1) ) delta2, 100 deltapercent
(select sum(points) from t_dsr where (year(servicedate)) = (year(getdate())) ) delta3
*/
basically, there is a client and points. he accumulates points every day for an entire year. and other years as well.
I only summed a table the t_dsr .
this query shows the client, with his sum of points and percentage against the total points incurred for a year...
the query shall only show the current year and the last two years.
I hope i desribed it well enough.
fields
client : client name
delta1 : current year - 2 points total
deltapercent1 : percentage ( delta1 / total of the year for which delta1 is acquired from )
delta2 : current year - 1 points total
deltapercent2 : percentage ( delta2 / total of the year for which delta2 is acquired from )
delta3 :current yaer points total
deltapercent3 : percentage ( delta3 / total of the year for which delta3 is acquired from )
For now this is assuming that i
I cannot use user-functions.
If anyone can give better ideas. Please do post.
I'd appreciate all inputs and comments.
Thanks.
Re: SQL Query for Optimization (SQL Server 2000)
You are running too many sub-queries that are not needed.
I don't have enough time right now to pull apart your entire query, but this example might help you see another way to accomplish your goal with all those sub-queries.
This:
Code:
select 1 datrow, 'Total' clientid,
(select sum(isnull(points, 0)) from t_dsr where (year(servicedate)) = (year(getdate()) - 2) ) delta1, 100 deltapercent,
(select sum(points) from t_dsr where (year(servicedate)) = (year(getdate()) - 1) ) delta2, 100 deltapercent
(select sum(points) from t_dsr where (year(servicedate)) = (year(getdate())) ) delta3
Can be replaced with:
Code:
Select 1 datrow, 'Total' ClientId
,Sum(Case When Year(ServiceDate)=Year(GetDate())-2 Then IsNull(Points,0) Else 0 End) Delta1
,Sum(Case When Year(ServiceDate)=Year(GetDate())-1 Then IsNull(Points,0) Else 0 End) Delta2
,Sum(Case When Year(ServiceDate)=Year(GetDate()) Then IsNull(Points,0) Else 0 End) Delta3
From T_DSR
You want to "run through" the data just one time and make the "condition" in the SUM() statement smart enough to pinpoint the data you want.
Can this logic be applied to the bigger query you have up top?
Re: SQL Query for Optimization (SQL Server 2000)
Hey thanks for the reply szlamany,
I'll look into it. :D for now i am about to go home though. :D
Happy weekend! :wave:
Re: SQL Query for Optimization (SQL Server 2000)
Quote:
Originally Posted by oceanebelle
Hey thanks for the reply szlamany,
I'll look into it. :D for now i am about to go home though. :D
Happy weekend! :wave:
That’s good you got the solution
Boom
Re: SQL Query for Optimization (SQL Server 2000)
Quote:
Originally Posted by shakti5385
That’s good you got the solution
Boom
well actually, not yet, I got busy with the other project that i'm handling. :cry:
but i jump from place to place.
