Don't think this can be done?

I have a table (which I didn't design ) which has the following columns
Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
each of which are flagged by either an'X' to indicate there is something on
that day or an'-' to indicate nothing on that day.
Is it possible to write a statement to select the last day which has
something on?
i.e.
Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
Sunday='-'
is it possible have a statement that returns Thursday is the last day with
something on?
Thanks in advance,
Geoselect CASE Sunday WHEN 'x' THEN 'Sunday' ELSE CASE Saturday WHEN 'x' THEN
'Saturday' ELSE ...
I'm lazy so I won't write the entire script but you can see where I'm going.
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:OdDpXAT6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table (which I didn't design ) which has the following columns
> Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
> each of which are flagged by either an'X' to indicate there is something
> on that day or an'-' to indicate nothing on that day.
> Is it possible to write a statement to select the last day which has
> something on?
> i.e.
> Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
> Sunday='-'
> is it possible have a statement that returns Thursday is the last day with
> something on?
> Thanks in advance,
> Geo
>|||Obviously this is a totally amateurish table design and personally I
wouldn't want to do anything that would prolong its unfortunate life,
but here goes:
SELECT ... ,
CASE
WHEN sunday = 'x' THEN 'sunday'
WHEN saturday = 'x' THEN 'saturday'
WHEN friday = 'x' THEN 'friday'
..
WHEN monday = 'x' THEN 'monday'
ELSE 'nothing'
END
FROM your_table ;
David Portas
SQL Server MVP
--|||Hi Geo,
SELECT
CASE
WHEN Sunday = 'x' THEN 'Sunday'
WHEN Saturday = 'x' THEN 'Saturday'
WHEN Friday = 'x' THEN 'Friday'
WHEN Thursday = 'x' THEN 'Thursday'
WHEN Wednesday = 'x' THEN 'Wednesday'
WHEN Tuesday = 'x' THEN 'Tuesday'
WHEN Monday = 'x' THEN 'Monday'
ELSE 'Noday'
END
>From SomeTable
HTH, Jens Suessmeyer.|||Forget this...
Mondays :-(
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJUdmFT6FHA.3636@.TK2MSFTNGP12.phx.gbl...
> select CASE Sunday WHEN 'x' THEN 'Sunday' ELSE CASE Saturday WHEN 'x' THEN
> 'Saturday' ELSE ...
> I'm lazy so I won't write the entire script but you can see where I'm
> going.
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:OdDpXAT6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||Hi Geo,
When does your w start? Sunday or Monday? David and Jens solution
both assume from the listing of column names that Sunday is the last
day of the w (not the first). CASE will return the first result
that matches the conditional, so if Sunday is the first day of the
w, then you'll need to modify the statement accordingly.
Stu|||On Mon, 14 Nov 2005 15:28:37 -0000, "Geo" <noSpamgbarr@.ibigroup.com>
wrote:
>I have a table (which I didn't design ) which has the following columns
>Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
>each of which are flagged by either an'X' to indicate there is something on
>that day or an'-' to indicate nothing on that day.
>Is it possible to write a statement to select the last day which has
>something on?
>i.e.
>Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
>Sunday='-'
>is it possible have a statement that returns Thursday is the last day with
>something on?
>Thanks in advance,
>Geo
Easy - write a seven-clause pivot select, create a reference table
with the names and ordering, and there you are!
Or use the case statement, it's no worse than the table.
J.|||Guys thanks for the input it's much appreciated. David I couldn't agree more
about the table design you should see some of the others I've inherited.
Geo
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:OdDpXAT6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table (which I didn't design ) which has the following columns
> Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
> each of which are flagged by either an'X' to indicate there is something
> on that day or an'-' to indicate nothing on that day.
> Is it possible to write a statement to select the last day which has
> something on?
> i.e.
> Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
> Sunday='-'
> is it possible have a statement that returns Thursday is the last day with
> something on?
> Thanks in advance,
> Geo
>