34,838
社区成员




Select a.EmpID,a.Term,a.ShiftID,b.xType As DayType,'S'+Cast(Day(a.Term) As Varchar) As Term1
From (
Select EmpID,Case When Len(D)=2 Then Dateadd(DD,Right(D,1)-1,Term) When Len(D)=3 Then Dateadd(DD,Right(D,2)-1,Term) End As Term,ShiftID
From aVw_Shift
UNPIVOT(ShiftID For D In(S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14,S15,S16,S17,S18,S19,S20,S21,S22,S23,S24,S25,S26,S27,S28,S29,S30,S31)) tb
Where EmpID=@EmpID And Datediff(Month,Term,@Term)=1) a,aT_Calendar b
Where a.Term=b.Term
And Datediff(Month,a.Term,b.Term)=0
其中aWv_Shift为横表,S1/2/3这种就是我上面说的D1/2/3,EmpID为用户标识,aT_Calendar 为日历表。[/quote]
Select a.EmpID,a.Term,a.ShiftID,b.xType As DayType,'S'+Cast(Day(a.Term) As Varchar) As Term1
From (
Select EmpID,Case When Len(D)=2 Then Dateadd(DD,Right(D,1)-1,Term) When Len(D)=3 Then Dateadd(DD,Right(D,2)-1,Term) End As Term,ShiftID
From aVw_Shift
UNPIVOT(ShiftID For D In(S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14,S15,S16,S17,S18,S19,S20,S21,S22,S23,S24,S25,S26,S27,S28,S29,S30,S31)) tb
Where EmpID=@EmpID And Datediff(Month,Term,@Term)=1) a,aT_Calendar b
Where a.Term=b.Term
And Datediff(Month,a.Term,b.Term)=0
其中aWv_Shift为横表,S1/2/3这种就是我上面说的D1/2/3,EmpID为用户标识,aT_Calendar 为日历表。[/quote]
Select a.EmpID,a.Term,a.ShiftID,b.xType As DayType,'S'+Cast(Day(a.Term) As Varchar) As Term1
From (
Select EmpID,Case When Len(D)=2 Then Dateadd(DD,Right(D,1)-1,Term) When Len(D)=3 Then Dateadd(DD,Right(D,2)-1,Term) End As Term,ShiftID
From aVw_Shift
UNPIVOT(ShiftID For D In(S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14,S15,S16,S17,S18,S19,S20,S21,S22,S23,S24,S25,S26,S27,S28,S29,S30,S31)) tb
Where EmpID=@EmpID And Datediff(Month,Term,@Term)=1) a,aT_Calendar b
Where a.Term=b.Term
And Datediff(Month,a.Term,b.Term)=0
其中aWv_Shift为横表,S1/2/3这种就是我上面说的D1/2/3,EmpID为用户标识,aT_Calendar 为日历表。[/quote]
我设计 根据您的联系一下 感谢,有问题再请教您
Select a.EmpID,a.Term,a.ShiftID,b.xType As DayType,'S'+Cast(Day(a.Term) As Varchar) As Term1
From (
Select EmpID,Case When Len(D)=2 Then Dateadd(DD,Right(D,1)-1,Term) When Len(D)=3 Then Dateadd(DD,Right(D,2)-1,Term) End As Term,ShiftID
From aVw_Shift
UNPIVOT(ShiftID For D In(S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14,S15,S16,S17,S18,S19,S20,S21,S22,S23,S24,S25,S26,S27,S28,S29,S30,S31)) tb
Where EmpID=@EmpID And Datediff(Month,Term,@Term)=1) a,aT_Calendar b
Where a.Term=b.Term
And Datediff(Month,a.Term,b.Term)=0
其中aWv_Shift为横表,S1/2/3这种就是我上面说的D1/2/3,EmpID为用户标识,aT_Calendar 为日历表。