Need help with SQL 2005 SELECT CASE statement

• helen.com

  SSC Rookie

  Points: 48

  More actions

  January 31, 2009 at 2:09 pm

  #94765

  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!

• Andy Reilly

  Hall of Fame

  Points: 3677

  More actions

  January 31, 2009 at 4:49 pm

  #936048

  Why not just use UNION ALL with each of the dfferent days

  INSERT INTO @strDays

  SELECT '1'

  FROM [dbo].SSS_DOW WITH (NOLOCK)

  WHERE SSS_DOWID IN (28,41,44)

  and sun = 'Y'

  UNION ALL

  SELECT '2'

  FROM [dbo].SSS_DOW WITH (NOLOCK)

  WHERE SSS_DOWID IN (28,41,44)

  and mon = 'Y'

  UNION ALL

  SELECT '3'

  FROM [dbo].SSS_DOW WITH (NOLOCK)

  WHERE SSS_DOWID IN (28,41,44)

  and tue = 'Y'

  ...

  SELECT * FROM @strDays

• Jeff Moden

  SSC Guru

  Points: 1004392

  More actions

  January 31, 2009 at 8:57 pm

  #936097

  Helen, you're new. Help us help you. Take a moment and read the article at the link in my signature below. Thanks.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)