Define days of the week with a bitmask

Question

I work with a third-party application and try to extract meaningful information about plug-in information based on data.

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

2014-05-27is the Tuesdaystarting position of the template Tuesday. So I would like to see the results showed Tuesday, Wednesday, Thursdayand Friday.

2015-10-25is Sunday, the starting position of this template Sunday. The results should be Sunday, Monday, Tuesday, Fridayand Saturday.

Any ideas or suggestions for determining the right 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-reference 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

Implementation Plan for Concat () Approach

Cross Apply