declare@Y1datetime,@Y2datetimeset@Y1=left(@YYMM,4)+'-'+right(@YYMM,2)+'-01'set@Y2=DateAdd(mm,1,@Y1)-1ifisnull(@PID1,'')=''andisnull(@PID2,'')=''select@YYMMasYYMM,E.EmpNo,E.EmpCName,P1.PartIDasPartID1,P1.PartNameasPartName1,P2.PartIDas

Question

declare @Y1 datetime,@Y2 datetime
set @Y1=left(@YYMM,4)+'-'+right(@YYMM,2)+'-01'
set @Y2=DateAdd(mm,1,@Y1)-1
if isnull(@PID1,'')='' and isnull(@PID2,'')=''
select @YYMM as YYMM,E.EmpNo,E.EmpCName,P1.PartID as PartID1,P1.PartName as PartName1,P2.PartID as PartID2,P2.PartName as PartName2,P3.PartID as PartID3,P3.PartName as PartName3,
[D1]=isnull(sum(case when Day(YYMMDD)=1 then FactAddWorkTimes else 0 end),0),
[D2]=isnull(sum(case when Day(YYMMDD)=2 then FactAddWorkTimes else 0 end),0),
[D3]=isnull(sum(case when Day(YYMMDD)=3 then FactAddWorkTimes else 0 end),0),
[D4]=isnull(sum(case when Day(YYMMDD)=4 then FactAddWorkTimes else 0 end),0),
[D5]=isnull(sum(case when Day(YYMMDD)=5 then FactAddWorkTimes else 0 end),0),
[D6]=isnull(sum(case when Day(YYMMDD)=6 then FactAddWorkTimes else 0 end),0),
[D7]=isnull(sum(case when Day(YYMMDD)=7 then FactAddWorkTimes else 0 end),0),
[D8]=isnull(sum(case when Day(YYMMDD)=8 then FactAddWorkTimes else 0 end),0),
[D9]=isnull(sum(case when Day(YYMMDD)=9 then FactAddWorkTimes else 0 end),0),
[SumTimes]=isnull(sum(FactAddWorkTimes),0)
from kq_empdaycarddata K
left join ps_employee E on E.EmpID=K.EmpID
left join PS_Part P1 on P1.AutoID=K.PartAutoID1
left join PS_Part P2 on P2.AutoID=K.PartAutoID2
left join PS_Part P3 on P3.AutoID=K.PartAutoID3
where yymmdd between @Y1 and @Y2
Group By K.EmpID,K.PartAutoID1,K.PartAutoID2,E.EmpNo,E.EmpCName,P1.PartID,P1.PartName,P2.PartID,P2.PartName,P3.PartID,P3.PartName
order by P1.PartID,P2.PartID,P3.PartID,E.EmpNo

Answer 1

声明@Y1 datetime,@Y2 datetime
　　设置@Y1 =左(@YYMM,4)+ ' - ' +右(@YYMM,2)+‘-01’
　　设置@Y2 =例子(毫米,1,@Y1)1
　　如果测试(@PID1,”)= "和测试(@PID2,”)= "
　　选择@YYMM YYMM一样,E.EmpNo,E.EmpCName P1.PartID作为PartID1 P1.作为PartName1 PartName,P2.PartID作为PartID2,P2.作为PartName2 PartName,P3.PartID作为PartID3,P3.作为PartName3 PartName,
　　[D1]=测试(全额(案例当天(系统)= 1然后返回0 FactAddWorkTimes结束),0),
　　[D2]=测试(全额(案例当天(系统)= 2然后返回0 FactAddWorkTimes结束)