SQL CASE Unexpected Results

Question

I'm using SQL 2008 and trying to run a query where I check for values in several columns and concatenate the results in a new column. From my research it looks like I need to use CONCAT to do this but I cant seem to figure out where I would place this in my query. Also, the first issue I'm having is that my query seems to be returning inaccurate results for everything.

I have a 'Shifts' table that contains an EmplID along with different columns for each day of the week (type bit) and times. Such as the following:

ShiftsID    EmplID  M   Tu  W   Th  F   Sa  Su  StartTime   EndTime
2001        1001    0   0   0   0   0   0   1   8:30:00     15:00:00
2002        1001    1   1   1   1   1   0   0   7:00:00     15:00:00

My Personnel table looks something like the following:

LegalName   EmployeeID
Doe, John   1001

My query looks like the following

SELECT Shifts.ShiftsID,
       X.WorkingDays,
       Personnel.EmployeeID,
       Personnel.FullName,
       Shifts.Start,
       Shifts.End
FROM   (SELECT *,
               CASE
                 WHEN Shifts.M = '1' THEN 'M'
                 WHEN Shifts.Tu = '1'THEN 'Tu'
                 WHEN Shifts.W = '1' THEN 'W'
                 WHEN Shifts.Th = '1' THEN 'Th'
                 WHEN Shifts.F = '1' THEN 'F'
                 WHEN Shifts.Sa = '1' THEN 'Sa'
                 WHEN Shifts.Su = '1' THEN 'Su'
                 ELSE NULL
               END AS WorkingDays
        FROM   Shifts
        WHERE  EmplID = '1001') X,
       Personnel
       INNER JOIN Shifts
               ON Personnel.EmployeeID = Shifts.EmplID
WHERE  ( Personnel.EmployeeID = '1001' )
       AND ( X.WorkingDays != '' ) 

The results of this query are:

ShiftsID    WorkingDays EmployeeID  LegalName   StartTime   EndTime
2001        Su          1001        Doe, John   8:30:00     15:00:00
2002        Su          1001        Doe, John   7:00:00     15:00:00
2001        M           1001        Doe, John   8:30:00     15:00:00
2002        M           1001        Doe, John   7:00:00     15:00:00

What I actually am needing to show would be something like the following:

ShiftsID    WorkingDays EmployeeID  LegalName   StartTime   EndTime
2001        Su          1001        Doe, John   8:30:00     15:00:00
2002        MTuWThF     1001        Doe, John   7:00:00     15:00:00

So, what am I doing wrong with my current query thats giving me unexpected results? And where do I put the CONCAT to get the valid WorkingDays concatenated as needed? Or is there an option other than CONCAT that I should be using?

Answer 1

I think this should work SQL Fiddle

SELECT *
FROM   (SELECT Shifts.ShiftsID,
               ISNULL((SELECT 'M' WHERE Shifts.M = '1'), '')
               + ISNULL((SELECT 'Tu' WHERE Shifts.Tu = '1'), '')
               + ISNULL((SELECT 'W' WHERE Shifts.W = '1'), '')
               + ISNULL((SELECT 'Th' WHERE Shifts.Th = '1'), '')
               + ISNULL((SELECT 'F' WHERE Shifts.F = '1'), '')
               + ISNULL((SELECT 'Sa' WHERE Shifts.Sa = '1'), '')
               + ISNULL((SELECT 'Su' WHERE Shifts.Su = '1'), '') AS WorkingDays,
               Personnel.EmployeeID,
               Personnel.LegalName,
               StartTime,
               EndTime
        FROM   Shifts
               INNER JOIN Personnel
                       ON Personnel.EmployeeID = Shifts.EmplID
        WHERE  Personnel.EmployeeID = '1001') T
WHERE  WorkingDays <> '' 

This should concatenate the days. Use ISNULL((SELECT ...), '') While SQL server 2008 does not support the IIF statement.