SQL Query for Optimization (SQL Server 2000)

[oceanebelle]

--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.

[szlamany]

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:

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:

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?

[oceanebelle]

Hey thanks for the reply szlamany,

I'll look into it. for now i am about to go home though.

Happy weekend!

[shakti5385]

Hey thanks for the reply szlamany,

I'll look into it. for now i am about to go home though.

Happy weekend!

That’s good you got the solution

Boom

[oceanebelle]

That’s good you got the solution

Boom

well actually, not yet, I got busy with the other project that i'm handling.

but i jump from place to place.