Peter Larsson Blog

Patron Saint of Lost Yaks

Double duty

I was browsing some of my older solutions, and I stumbled across this interesting piece.
The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'.

DECLARE
     @Roles TABLE (Person VARCHAR(5), [Role] VARCHAR(1))

INSERT
      @Roles
SELECT
      'Smith', 'O' UNION ALL
SELECT
      'Smith', 'D' UNION ALL
SELECT
      'Jones', 'O' UNION ALL
SELECT
      'White', 'D' UNION ALL
SELECT
      'Brown', 'X'

SELECT
      Person,
            SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty

FROM
        @Roles
WHERE
       [Role] IN ('D', 'O')
GROUP
BY    Person

Legacy Comments


vadym
2009-04-23
re: Double duty
really smart solution... However I would not use this crazy substring in production code :).