Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# re: Double duty

really smart solution... However I would not use this crazy substring in production code :).
4/23/2009 3:20 PM | vadym
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET