Cutting the SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cutting the SUM

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-07-08 : 03:52:10

hi

As you can see there are many SUM function being used in this query. I would like to use a variable to help in cutting down the number of SUM. How should i go about it? If you have a better solution, pls show me. Thanks a million.

SELECT dbo.SA.PID, dbo.SA.Name
,COUNT(*) AS Days
,SUM(dbo.SA.AHours - 4) AS RegHour
,SUM(dbo.SA.AHours - 4) * dbo.SA.RegularHourlyRate AS RegPay
,SUM(dbo.SA.AHours - 8) AS OTHour
,SUM(dbo.SA.AHours - 8) * dbo.SA.OTHourlyRate AS OTPay
,SUM(dbo.SA.AHours) AS TotalHours
,((SUM(dbo.SA.AHours - 4) * dbo.SA.RegularHourlyRate) + ( SUM(dbo.SA.AHours - 8) * dbo.SA.OTHourlyRate)) as TotalPay
,(((SUM(dbo.SA.AHours - 4) * dbo.SA.RegularHourlyRate) + ( SUM(dbo.SA.AHours - 8) * dbo.SA.OTHourlyRate)) * EmployeeTax/100) as EmployeeTax
,(((SUM(dbo.SA.AHours - 4) * dbo.SA.RegularHourlyRate) + ( SUM(dbo.SA.AHours - 8) * dbo.SA.OTHourlyRate)) * EmployerTax/100) as EmployerTax
FROM dbo.SA

Sachin.Nand

2937 Posts

Posted - 2010-07-08 : 05:50:02

I cannot see any scope for improvement except for calculating the TotalPay once & then using the value in the derived table to calculate other values.Something like this.


Select *,
TotalPay*EmployeeTax/100 as EmployeeTax,
TotalPay*EmployerTax/100 as EmployerTax from

(
SELECT dbo.SA.PID, dbo.SA.Name
,COUNT(*) AS Days
,SUM(dbo.SA.AHours - 4) AS RegHour
,SUM(dbo.SA.AHours - 4) * dbo.SA.RegularHourlyRate AS RegPay
,SUM(dbo.SA.AHours - 8) AS OTHour
,SUM(dbo.SA.AHours - 8) * dbo.SA.OTHourlyRate AS OTPay
,SUM(dbo.SA.AHours) AS TotalHours
,((SUM(dbo.SA.AHours - 4) * dbo.SA.RegularHourlyRate) + ( SUM(dbo.SA.AHours - 8) * dbo.SA.OTHourlyRate)) as TotalPay
FROM dbo.SA 
)T

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources