quote:

---

Originally posted by Badeye

Hi All,

I'm very new to SQL but seem to be slowly working my way there. I have a table with information of employees and there working contracted hours as i am building a database to hold and calculate employees holiday entitlement. At the moment i'm working on the accruals which i have done pretty successfully but now i have my query to calculate the accrual i now need a column in the results to add these accruals up per person. Can anyone help?

My output looks like this:

EmpID | EmpName | Accrual0 | Accrual1 | Accrual2 | Accrual3 | Accrual4 | Accrual5

Now i need a Total_Accrual Column

This is my query code so far:

SELECT [EmpID],
[First_Name],
[Surname],
[Paygroup],
[ContHrs],
[StartDate],
SUM(CASE
WHEN [ContHrs1] IS NUll THEN [ContHrs]*[Hols_Calc]/365* DATEDIFF(Day,[StartDate], GETDATE())
ELSE ([ContHrs1]*[Hols_Calc1])/365* DATEDIFF(day,[StartDate],[AmendDate1])
END)
As 'Accrual0',
SUM(CASE
WHEN [ContHrs2] IS NUll THEN [ContHrs1]*[Hols_Calc1]/365* DATEDIFF(Day,[AmendDate1], GETDATE())
ELSE ([ContHrs2]*[Hols_Calc2])/365* DATEDIFF(day,[AmendDate1],[AmendDate2])
END)
As 'Accrual1',
SUM(CASE
WHEN [ContHrs3] IS NUll THEN [ContHrs2]*[Hols_Calc2]/365* DATEDIFF(Day,[AmendDate2], GETDATE())
ELSE ([ContHrs3]*[Hols_Calc3])/365* DATEDIFF(day,[AmendDate2],[AmendDate3])
END)
As 'Accrual2',
SUM(CASE
WHEN [ContHrs4] IS NUll THEN [ContHrs3]*[Hols_Calc3]/365* DATEDIFF(Day,[AmendDate3], GETDATE())
ELSE ([ContHrs4]*[Hols_Calc4])/365* DATEDIFF(day,[AmendDate3],[AmendDate4])
END)
As 'Accrual3',
SUM(CASE
WHEN [ContHrs5] IS NUll THEN [ContHrs4]*[Hols_Calc4]/365* DATEDIFF(Day,[AmendDate4], GETDATE())
ELSE ([ContHrs5]*[Hols_Calc5])/365* DATEDIFF(day,[AmendDate4],[AmendDate5])
END)
As 'Accrual4',
SUM(CASE
WHEN [ContHrs5] IS NOT NUll THEN [ContHrs5]*[Hols_Calc5]/365* DATEDIFF(Day,[AmendDate5], GETDATE())
END)
As 'Accrual5'

FROM [LabourReportingTest].[dbo].[Emp_Holidays]

GROUP BY
[EmpID],
[First_Name],
[Surname],
[Paygroup],
[ContHrs],
[StartDate]

Any Help, would be great.

Thanks,

Si

---