SQLTeam.com | Weblogs | Forums

Need Help with SQL Query

Hey there,

I'm very bad with SQL statements and am struggling for a while now. Maybe you guys can help me out?
I have a table with tracked work times "RecordedTimeInSeconds" and a flag, if they are productive or unproductive "IsCalculable"

I would like to calculate the percentage of productive and unproductive time grouped by Firstname.

This is what I tried but seems not to work:

SELECT [Lastname],((SUM([RecordedTimeInSeconds])/3600 )* 100) / 
(
SELECT (SUM([RecordedTimeInSeconds])/3600)
FROM [cvw_EmployeeHelpdeskTimerStatistic]
WHERE YEAR([Date]) = YEAR(getdate())
) as Score

FROM [cvw_EmployeeHelpdeskTimerStatistic]
WHERE YEAR([Date]) = YEAR(getdate())
AND [IsCalculable] = '1'
GROUP BY [Lastname]

SELECT [Lastname],
    CAST(SUM(CASE WHEN [IsCalculable] = '1' THEN [RecordedTimeInSeconds] ELSE 0 END) *
        100.0 / NULLIF(SUM([RecordedTimeInSeconds]), 0) AS decimal(4, 1)) AS pct_calculable
FROM [cvw_EmployeeHelpdeskTimerStatistic]
WHERE [date] >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) 
AND[ date] < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
1 Like

Thank You Scott! You made my day :slight_smile: