Determine days of week from bit mask

Question

I am working with a third party application and am trying to extract meaningful information about shift information based on the data.

shift_pattern_start_dt     pattern
2014-05-27                 1111000
2015-10-25                 1110011

2014-05-27 is a Tuesday the start position of the pattern is Tuesday. So I would want the results to show Tuesday, Wednesday, Thursday, and Friday.

2015-10-25 is a Sunday the start position of this pattern is Sunday. The results should be Sunday, Monday, Tuesday, Friday, and Saturday.

Any ideas or suggestions to determine the correct working days?

Answer 1

Declare @YourTable table (shift_pattern_start_dt date, pattern varchar(25))
Insert Into @YourTable values
('2014-05-27','1111000'),
('2015-10-25','1110011')

Select *
      ,NewCol = concat(
                IIF(substring(pattern,1,1)='1',   +DateName(WEEKDAY,shift_pattern_start_dt),'')
               ,IIF(substring(pattern,2,1)='1',','+DateName(WEEKDAY,dateadd(DAY,1,shift_pattern_start_dt)),null)
               ,IIF(substring(pattern,3,1)='1',','+DateName(WEEKDAY,dateadd(DAY,2,shift_pattern_start_dt)),null)
               ,IIF(substring(pattern,4,1)='1',','+DateName(WEEKDAY,dateadd(DAY,3,shift_pattern_start_dt)),null)
               ,IIF(substring(pattern,5,1)='1',','+DateName(WEEKDAY,dateadd(DAY,4,shift_pattern_start_dt)),null)
               ,IIF(substring(pattern,6,1)='1',','+DateName(WEEKDAY,dateadd(DAY,5,shift_pattern_start_dt)),null)
               ,IIF(substring(pattern,7,1)='1',','+DateName(WEEKDAY,dateadd(DAY,6,shift_pattern_start_dt)),null)
               )
 From  @YourTable

Returns

shift_pattern_start_dt  pattern   NewCol
2014-05-27              1111000   Tuesday,Wednesday,Thursday,Friday
2015-10-25              1110011   Sunday,Monday,Tuesday,Friday,Saturday

EDIT - Cross Apply Version

Select A.*
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select NewCol =Stuff((Select ',' +D 
                  From (
                        Select N,D = IIF(substring(A.pattern,N,1)='0',null,DateName(WEEKDAY,DateAdd(DAY,N-1,A.shift_pattern_start_dt)))
                         From (Values (1),(2),(3),(4),(5),(6),(7)) N(N)             
                        ) B1 
                  For XML Path ('')),1,1,'') 
             ) B

Execution Plan for Concat() Approach

Execution Plan for Cross Apply Approach