Alternative approach to avoid 30 CASE statements

Transact-SQL
1

I am looking for some advice on how best to approach this scenario. I have been asked to produce a query that returns engineer hours. There are many engineers (rows) and many time categories (columns). Hopefully, the SQL below will clarify what i am trying to achieve, however, I am not sure that CASE statements is the best solution. There are around 30 different time categories, e.g. standard time, time and a half, etc, etc. Any help would be appreciated. :smile:

DECLARE @HOURS TABLE
(
H_Code VARCHAR(10),
H_Hours REAL,
H_Name VARCHAR(10)
)

INSERT INTO @Hours

SELECT
'A1', 8, 'Bob'
UNION ALL
SELECT
'A2', 12,'Bob'
UNION ALL
SELECT
'A3', 8, 'Bob'
UNION ALL
SELECT
'A4', 8.5, 'Bob'
UNION ALL
SELECT
'A1', 12, 'Fred'
UNION ALL
SELECT
'A2', 6, 'Fred'
UNION ALL
SELECT
'A3', 10, 'Fred'
UNION ALL
SELECT
'A4', 9, 'Fred'

SELECT

H_NAME AS Name
,SUM(CASE WHEN H_CODE = 'A1' THEN H_HOURS ELSE 0 END) AS StandardTime
,SUM(CASE WHEN H_CODE = 'A2' THEN H_HOURS ELSE 0 END) AS TimeAndHalf
,SUM(CASE WHEN H_CODE = 'A3' THEN H_HOURS ELSE 0 END) AS DoubleTime
,SUM(CASE WHEN H_CODE = 'A4' THEN H_HOURS ELSE 0 END) AS SomeOtherTime

FROM

@HOURS
GROUP BY H_NAME

** Apologies but i wasn't sure how to space out the data in the result set below...

Name | StandardTime | TimeAndHalf | DoubleTime | SomeOtherTime
Bob 8 12 8 8.5
Fred 12 6 10 9

2

Its a bit of a pain to code, but I'd probably do it that way.

If this is a frequent requirement then you could create a VIEW that flattens the @HOURS table into multiple columns for each H_Code, and then you/others can just SUM them

CREATE VIEW MyView
AS
SELECT H_Hours, H_Name
,CASE WHEN H_CODE = 'A1' THEN H_HOURS ELSE 0 END AS StandardTime
,CASE WHEN H_CODE = 'A2' THEN H_HOURS ELSE 0 END AS TimeAndHalf
,CASE WHEN H_CODE = 'A3' THEN H_HOURS ELSE 0 END AS DoubleTime
,CASE WHEN H_CODE = 'A4' THEN H_HOURS ELSE 0 END AS SomeOtherTime
FROM YourHoursTable

and then you can do:

SELECT H_Hours, H_Name, SUM(StandardTime) AS StandardTime, ...
FROM MyView
1 Like
3

Thanks for the reply. I'm not overly concerned about the coding as this is a one off. The requirements is actually a little more complex but I just posted the part that I thought could be quite costly when executed. The query will probably end up as a proc feeding a Crystal Report. I appreciate your advice.

4

Mechanically generate the code perhaps?

SELECT DISTINCT ',CASE WHEN H_CODE = ''' + H_Code + ''' THEN H_HOURS ELSE 0 END AS XXXXXX'
FROM YourTable
ORDER BY H_Code

If you can JOIN to a lookup-table to get the descriptive name for the H_Code and use that as the alias so much the better!!

1 Like
5

Hmmm,... I have never used dynamic SQL but I have seen it in use. I thought I understood your suggestion (and I like the idea) but I have just had play with it and clearly do not!! I am not sure where this fits into the main query, is there any chance you could elaborate to help me understand?

6

How about pivot?

select H_Name
      ,A1 as StandardTime
      ,A2 as TimeAndHalf
      ,A3 as DoubleTime
      ,A4 as SomeOtherTime
  from (select *
          from @HOURS
       ) as a
 pivot (sum(H_Hours)
        for H_Code in (A1,A2,A3,A4)
       ) as b
1 Like
7

Wow! This simplifies the code and works a treat. Thanks very much.

8

No, not dynamic SQL. The output from my SQL is intended to be used in the actual SQL query. Where there are lots of columns, particularly with repetitive and similar manipulations (such as SUM(CASE ...), it is easier to query SQL's Meta Data to get a list of the columns, with the output formatted to display the desired syntax for the data manipulation, rather than write it all by hand.

Substitute "MyTable" with your actual table name, in my query above, and I expect you'll see what I mean. If you have a descriptive lookup table for the definition of H_CODE then it would even help with generating the SELECT list for bitsmed's PIVOT approach.

Waste of time if you only have tow or three columns, but you said you have 30 categories so on that basis it will be quicker to mechanically generate the actual SQL Query, rather than type it by hand.

1 Like
9

I get it now. I did run your script with my table but I thought it was intended to be the starting point for some dynamic solution. I feel a little stupid now that I understand what you intended :smile:. Nice one and thanks for your help!

10

If your Categories change often, and users are happy to get "differently shaped reports each time" (IME they have a habit of pasting data into XLS and may not notice / all hell may break loose!!, if the column sequence changes ...) then Dynamic SQL would be a useful solution.

If the Categories change once-in-a-while/never then re-running the "Mechanical SQL Generation code" and pasting that into a Version-Two of the query, would be my approach.

11

Thanks. The report will be used as the basis for a CSV file that the end user will import into their payroll system, so pretty crucial that the structure remains unchanged. It's unlikely that categories will change as they reflect the basis on which staff are paid.

Regarding dynamic SQL, I am keen to understand it but I don't think I would recognise a situation in which it might be useful. :smirk: