Thinking outside the box

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

Print | posted on Tuesday, April 21, 2009 3:24 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]