Hi everyone,
I would really appreciate help with this, I am seriously stuck. Basically I have a table that
looks like this:
SSS_DOWID Name Mon Tue Wed Thu Fri Sat Sun Description
2 M Y N N N N N N Monday
3 MF Y N N N Y N N Monday, Friday
.......
18 T N Y N N N N N Tuesday
........
etc.
What I need to do is to convert the values in this table to a table that contains only the
corresponding numbers for days of the week, e.g, 1 for Sunday, 2 for Monday, 3 for Tuesday, etc., all the way until 8 for Sunday.
SO I have this little bit of SQL:
DECLARE @strDays table
(SSS_DOWID int)
INSERT INTO @strDays
SELECT
case (sun) when 'Y' then '1' else '' end +
case (mon) when 'Y' then '2' else '' end +
case (tue) when 'Y' then '3' else '' end +
case (wed) when 'Y' then '4' else '' end +
case (thu) when 'Y' then '5' else '' end +
case (fri) when 'Y' then '6' else '' end +
case (sat) when 'Y' then '7' else '' end
FROM
[dbo].SSS_DOW WITH (NOLOCK)
WHERE
SSS_DOWID IN (28,41,44)
SELECT * FROM @strDays
Which works fine for individual days, EXCEPT day combinations. So in this case, when I pass in
28 (Wed), 41 (Fri), and 44 (SaSun), I get 4 (perfect), 6 (perfect), and 17 (oh crap - should be
1 and 7, separately).
Can anyone please help me restructure my SQL so I get a table containing 1, 4, 6, and 7 instead
of 4, 6, 17?
Thanks very much for your anticipated assistance!