January 31, 2009 at 2:11 pm
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 MYNNNNNNMonday
3 MFYNNNYNNMonday, Friday
.......
18 TNYNNNNNTuesday
........
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!
January 31, 2009 at 7:51 pm
This is a duplicate post. Please do not cross-post.
Please reply to this thread instead: http://www.sqlservercentral.com/Forums/Topic647545-145-1.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply