22,207
社区成员
发帖
与我相关
我的任务
分享
when c.Myfield1='23' then 'N56/N61' when c.Myfield1='24' then 'B170'
when c.Myfield1='25' then 'B171' end),(case when PerField3='01' then '人资部' when PerField3='02' then '财务部' when
PerField3='03' then '资讯部'
when PerField3='04' then '客服部' when PerField3='05' then '关务部'
when PerField3='06' then '计划部' when PerField3='07' then '资材部'
when PerField3='08' then '工程技术部' when PerField3='09' then '安全课'
when PerField3='10' then '运作部' when PerField3='11' then '压铸模具'
when PerField3='12' then '品保部' end ),EmpTypeName,b.EmpNo,EmpName,IDCardNo,DtName,c.PartName,bb,
LevelLevel,OverTime1,ComeDate,LeaveDate,PayNo6,PayNo16,PayNo17,
OverTime2,OverTime3,OverTimeAll,WTType2Num,WTType3Num,
PayNo21,PayNo22,PayNo23,PayNo25,PayNo26,PayNo2,PayNo3,PayNo4,
PayNo27,PayNo42,PayNo32,PayNo33,PayNo34,PayNo35,PayNo43,
PayNo37,PayNo38,PayNo39,PayNo40,PayNo13,PayNo14,PayNo15,PayNo16
PayNo5,PayNo7,PayNo8,PayNo9,PayNo10,PayNo21+PayNo22+PayNo23+PayNo24+PayNo29 as 公司成本,
PayNo28+PayNo30 as 个人成本,
PayNo11,PayNo12,PayNo43,PayNo18,PayNo20,PayNo24,PayNo28,PayNo29,PayNo30,ComeSourceNo
InComeTax,PayNo36,b.MyField1 as 入职结算差,b.MyField2 as 离职结算差 from PayMonthData a left join AttMonthData b on
a.EmpID=b.EmpID and a.YYMM=b.YYMM left join PerEmployee c on a.EmpID=c.EmpID left join bbview on c.PartName=bbview.PartName
where a.YYMM='{YYMM}' and PerField3!='' and PerField3 is not null and ComeSourceNo='08' and PayNo20>=0
order by PerField3,PartName
else
select(case when c.Myfield1='01' then 'B24' when c.Myfield1='05' then 'X241'
when c.Myfield1='06' then '其他' when c.Myfield1='15' then '办公人员'
when c.Myfield1='17' then '铣床' when c.Myfield1='18' then 'TMA'
when c.Myfield1='19' then '其他/保洁员' when c.Myfield1='20' then 'N12'
when c.Myfield1='21' then 'X340' when c.Myfield1='22' then 'B9'
when c.Myfield1='23' then 'N56/N61' when c.Myfield1='24' then 'B170'
when c.Myfield1='25' then 'B171' end),(case when PerField3='01' then '人资部' when PerField3='02' then '财务部' when
PerField3='03' then '资讯部'
when PerField3='04' then '客服部' when PerField3='05' then '关务部'
when PerField3='06' then '计划部' when PerField3='07' then '资材部'
when PerField3='08' then '工程技术部' when PerField3='09' then '安全课'
when PerField3='10' then '运作部' when PerField3='11' then '压铸模具'
when PerField3='12' then '品保部' end ),EmpTypeName,b.EmpNo,EmpName,IDCardNo,DtName,c.PartName,bb,
LevelLevel,OverTime1,ComeDate,LeaveDate,PayNo6,PayNo16,PayNo17,
OverTime2,OverTime3,OverTimeAll,WTType2Num,WTType3Num,
PayNo21,PayNo22,PayNo23,PayNo25,PayNo26,PayNo2,PayNo3,PayNo4,
PayNo27,PayNo42,PayNo32,PayNo33,PayNo34,PayNo35,PayNo43,
PayNo37,PayNo38,PayNo39,PayNo40,PayNo13,PayNo14,PayNo15,PayNo16
PayNo5,PayNo7,PayNo8,PayNo9,PayNo10,PayNo21+PayNo22+PayNo23+PayNo24+PayNo29 as 公司成本,
PayNo28+PayNo30 as 个人成本,
PayNo11,PayNo12,PayNo43,PayNo18,PayNo20,PayNo24,PayNo28,PayNo29,PayNo30,ComeSourceNo
InComeTax,PayNo36,b.MyField1 as 入职结算差,b.MyField2 as 离职结算差 from PayMonthData a left join AttMonthData b on
a.EmpID=b.EmpID and a.YYMM=b.YYMM left join PerEmployee c on a.EmpID=c.EmpID left join bbview on c.PartName=bbview.PartName
where a.YYMM='{YYMM}' and PerField3 is not null and ComeSourceNo='08' and PayNo20>=0
order by PerField3,PartName
DECLARE @t table(id int, PerField3 varchar(2))
INSERT INTO @t
SELECT 1,'01' UNION ALL
SELECT 2,'02' UNION ALL
SELECT 3,'03' UNION ALL
SELECT 4,'' UNION ALL
SELECT 5,NULL
DECLARE @PerField3 varchar(2) -- 参数
DECLARE @i int
SET @i = 1
WHILE @i <= 4
BEGIN
-- 模拟不同的参数
IF @i > 3
SET @PerField3 = NULL
ELSE
SET @PerField3 = '0'+CONVERT(varchar(2),@i)
SELECT *
FROM @t
WHERE PerField3!='' and PerField3 is not null
AND ( @PerField3 IS NULL
OR PerField3 = @PerField3
)
SET @i = @i + 1
END
id PerField3
----------- ---------
1 01
id PerField3
----------- ---------
2 02
id PerField3
----------- ---------
3 03
id PerField3
----------- ---------
1 01
2 02
3 03