sql - 从位掩码确定一周中的几天

Question

我正在使用第三方应用程序，并尝试根据数据提取有关类次信息的有意义的信息。

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

2014-05-27 是一个Tuesday，该形态的开始位置是Tuesday。因此，我希望结果显示星期二、星期三、星期四和星期五。

2015-10-25 是一个星期日，该形态的起始位置是星期日。结果应为星期日、星期一、星期二、星期五和星期六。

对于确定正确的工作日有什么想法或建议吗？

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

返回

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