SQL Script help

Question

I have a table like this named "Days"

Day 1    Day 2    Day 3    Day 4    Day 5    Day 6    Day 7
1        1        1        1        0        0        0
1        1        1        1        0        0        0

which has the corresponding details. Where 1 is 'true' and 0 is false.

DaY 1 - sunday
day 2- monday
day 3 - tuesday
day 4- wednesday
day5 - thursday
day 6 - friday
dayy 7- saturday

1 - denotes there is work on the given day
0 - denotes there is no work on the given day.

Given the details above i need to make column to table mapping and need to generate a table like this , where i need to get the data only those which has flag 1

1   Sunday 
2   Monday 
3   Tuesday     
4   Wednesday

for the first record.

Answer 1

I believe you're looking for something like the following:

select 
case when day1=1 then '1 Sunday' end Sunday,
case when day2=1 then '2 Monday' end Monday,
case when day3=1 then '3 Tuesday' end Tuesday,
case when day4=1 then '4 Wednesday' end Wednesday,
case when day5=1 then '5 Thursday' end Thursday,
case when day6=1 then '6 Friday' end Friday, 
case when day7=1 then '7 Saturday' end Saturday
from [table]
where day1+day2+day3+day4+day5+day6+day7 <> 7

Or those should be rows? Then you would need to unpivot the source data first like so:

select     
cast(substring(dayname, 4, 1) as tinyint) as DayNumber, 
case cast(substring(dayname, 4, 1) as tinyint) 
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday' end [DayName]
, DayFlag 
from (select * from [table] t where day1+day2+day3+day4+day5+day6+day7 <> 7) f
unpivot 
(
DayFlag for DayName in ([day1], [day2], [day3], [day4], [day5], [day6], [day7])
) unpvt
where DayFlag = 1 --is this what you need?

Answer 2

WITH    [days] (id, day1, day2, day3, day4, day5, day6, day7) AS
        (
        SELECT  1, 1, 1, 1, 1, 1, 0, 0
        UNION ALL
        SELECT  2, 1, 1, 1, 1, 1, 0, 0
        )
SELECT  id, DATENAME(dw, DATEADD(d, CAST(SUBSTRING(wd, 4, 1) AS INT), '2005-01-01')), work
FROM    [days]
UNPIVOT
        (
        work FOR wd IN
        (day1, day2, day3, day4, day5, day6, day7)
        ) AS up
WHERE   work = 1

Answer 3

hello,

DISCLAIMER - i know it doesn't solve your issue but wouldn't it be better to have a table that was de-normalized out to something like:

ID  | WeekNo  |  DayNo
----------------------
1     1          1
2     1          2
3     1          3
4     2          6
5     2          7

thereby, you only actually add days to the table where work was carried out on that day (i.e. there was no work on days 4-7 in week 1 and only work carried out in days 6 and 7 in week 2).?? You could then have some logic in your .net app that 'knew' about the full spread of days in the week and presented you with an exceptions list (i.e. days that weren't in the table for any given WeekNo)

this would be my 1st alternative thought on this 'debate'...