SQL CASE Unexpected Results
I am using SQL 2008 and am trying to run a query where I am checking values in multiple columns and concatenating the results into a new column. From my research, it looks like I need to use CONCAT to do this, but I can't figure out where to place this in my request. Also, the first problem I run into is that my query seems to return inaccurate results for everything.
I have a table "Shifts" that contains the EmplID along with different columns for each day of the week (bit type) and time. For example:
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 this:
LegalName EmployeeID
Doe, John 1001
My request looks like this
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:
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 really need to show is something like this:
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 that is giving me unexpected results? And where do I put CONCAT to get valid working concatenations as needed? Or is there an option other than CONCAT that I should use?
source to share
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 connect the days. Use ISNULL ((SELECT ...), '') even though SQL Server 2008 does not support IIF statement.
source to share
It seems pretty straightforward - unless I miss something. I used an expression CASE
to create a sequence of days for an employee to work.
This query appears to be producing the desired results.
SELECT *
-- string representing all working days.
,CASE WHEN Shifts.M = '1' THEN 'M' ELSE '' END +
CASE WHEN Shifts.Tu = '1' THEN 'Tu' ELSE '' END +
CASE WHEN Shifts.W = '1' THEN 'W' ELSE '' END +
CASE WHEN Shifts.Th = '1' THEN 'Th' ELSE '' END +
CASE WHEN Shifts.F = '1' THEN 'F' ELSE '' END +
CASE WHEN Shifts.Sa = '1' THEN 'Sa' ELSE '' END +
CASE WHEN Shifts.Su = '1' THEN 'Su' ELSE '' END AS [WorkingDays]
FROM
#Shifts AS Shifts
-- INNER JOIN Personnel ON Personnel.EmployeeID=Shifts.EmplID
WHERE EmplID = '1001'
-- only show shifts that contain one or more day?
AND (
Shifts.M='1' OR
Shifts.Tu='1' OR
Shifts.W='1' OR
Shifts.Th='1' OR
Shifts.F='1' OR
Shifts.Sa='1' OR
Shifts.Su='1'
);
source to share