Help with T-SQL (2008R2)
-
Hi,
I'm creating a report and am trying to work out how to output the following info.
First a bit about the data.
We have a column called TotalProfiledHours which represents how many hours a week an employee is contracted to work.
We have another column called ALTotalUnits which contains the annual leave balance for an employee.
I need to report on the balances and whether they fall above or below 3 weeks.
So for an employee who has a TotalProfiledHours of 40 then 3 weeks or over would be 120 or more in the ALTotalUnits column.
An employee who has a TotalProfiledHours of 20 would have 60 or more in the ALTotalUntils column to be considered 3 weeks or over.
This is how I want to output the data....Name | TotalProfiledHours | Higher than 3 weeks | Lower than 3 weeks
John | 40 | 121 | -
Jane | 40 | - | 80
Fred | 20 | - | 25
Sue | 20| 70 | -Can't seem to get my head around the required CASE (?) statement required.
-
CASE WHEN ALTotalUnits / TotalProfiledHours >= 3 THEN <higher than 3 weeks>
CASE WHEN ALTotalUnits / TotalProfiledHours < 3 THEN <lower than 3 weeks>SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
How can I output the actual values under the column headings as shown below?
-
If you want blanks rather than NULL, then:
CASE WHEN ALTotalUnits / TotalProfiledHours >= 3 THEN CAST(ALTotalUnits AS varchar(30)) ELSE '' END AS [Higher than 3 Weeks],
CASE WHEN ALTotalUnits / TotalProfiledHours < 3 THEN '' ELSE CAST(ALTotalUnits AS varchar(30)) END AS [Lower than 3 Weeks]SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply