sql server 怎么根据部门过滤查询出所有的工序
create proc proc_test
@begTime varchar(100),
@endTime varchar(100),
@BM varchar(100)
as
select a.[FID],a.[FJHDate] as [计划时间],b.[FQty] as [周计划数量],b.[FZWMS] as [中文描述],c.[Forderinterid],
c.[Fsourceentryid],d.[FName] as 产品名称,convert(datetime, null) as [下单日期],convert(datetime, null) as [审核日期],
convert(varchar(100), null) as [销售单据编号],convert(varchar(100), null) as [装柜数量],convert(datetime, null) as [验货日期],
convert(datetime, null) as [装柜日期],convert(datetime, null) as [交货日期],SUBSTRING(e.[FNumber],0,2) as [部门],
if 部门=1
begin
max(case when a.[fdgx]=57412 then day(a.[FJHDate]) else ' ' end) as [裁剪],
max(case when a.[fdgx]=57413 then day(a.[FJHDate]) else ' ' end) as [车缝],
max(case when a.[fdgx]=57414 then day(a.[FJHDate]) else ' ' end) as [木工],
max(case when a.[fdgx]=57415 then day(a.[FJHDate]) else ' ' end) as [裁棉],
max(case when a.[fdgx]=57416 then day(a.[FJHDate]) else ' ' end) as [喷胶],
max(case when a.[fdgx]=57417 then day(a.[FJHDate]) else ' ' end) as [枪钉],
max(case when a.[fdgx]=57418 then day(a.[FJHDate]) else ' ' end) as [装配],
max(case when a.[fdgx]=57419 then day(a.[FJHDate]) else ' ' end) as [配料],
max(case when a.[fdgx]=57420 then day(a.[FJHDate]) else ' ' end) as [包装],
max(case when a.[fdgx]=57421 then day(a.[FJHDate]) else ' ' end) as [硬皮],
max(case when a.[fdgx]=57422 then day(a.[FJHDate]) else ' ' end) as [内销],
end
else if 部门=2
begin
max(case when a.[fdgx]=57424 then day(a.[FJHDate]) else ' ' end) as [车工],
max(case when a.[fdgx]=57425 then day(a.[FJHDate]) else ' ' end) as [枪工],
max(case when a.[fdgx]=57426 then day(a.[FJHDate]) else ' ' end) as [裁丝棉],
max(case when a.[fdgx]=57427 then day(a.[FJHDate]) else ' ' end) as [充棉],
max(case when a.[fdgx]=57428 then day(a.[FJHDate]) else ' ' end) as [裁棉],
max(case when a.[fdgx]=57429 then day(a.[FJHDate]) else ' ' end) as [胶棉],
max(case when a.[fdgx]=57430 then day(a.[FJHDate]) else ' ' end) as [裁皮],
max(case when a.[fdgx]=57431 then day(a.[FJHDate]) else ' ' end) as [木工],
max(case when a.[fdgx]=57432 then day(a.[FJHDate]) else ' ' end) as [组装],
max(case when a.[fdgx]=57433 then day(a.[FJHDate]) else ' ' end) as [包装],
end
else if 部门=3
begin
max(case when a.[fdgx]=57435 then day(a.[FJHDate]) else ' ' end) as [成品包装],
max(case when a.[fdgx]=57436 then day(a.[FJHDate]) else ' ' end) as [雕刻],
max(case when a.[fdgx]=57437 then day(a.[FJHDate]) else ' ' end) as [倒边],
max(case when a.[fdgx]=57438 then day(a.[FJHDate]) else ' ' end) as [打砂],
max(case when a.[fdgx]=57439 then day(a.[FJHDate]) else ' ' end) as [火焰抛光],
max(case when a.[fdgx]=57440 then day(a.[FJHDate]) else ' ' end) as [变形],
max(case when a.[fdgx]=57441 then day(a.[FJHDate]) else ' ' end) as [打孔],
max(case when a.[fdgx]=60082 then day(a.[FJHDate]) else ' ' end) as [成品抛光],
max(case when a.[fdgx]=60083 then day(a.[FJHDate]) else ' ' end) as [质检包装],
max(case when a.[fdgx]=60084 then day(a.[FJHDate]) else ' ' end) as [组装],
max(case when a.[fdgx]=60085 then day(a.[FJHDate]) else ' ' end) as [喷漆],
max(case when a.[fdgx]=60086 then day(a.[FJHDate]) else ' ' end) as [包装],
max(case when a.[fdgx]=60087 then day(a.[FJHDate]) else ' ' end) as [成品包装2],
end
else
begin
print '输入有误,请输入正确的部门[1,2,3]!'
end
into #T1
from U_JJ_ZGXJHEntry b
left join U_JJ_ZGXJH a on a.[FID] = b.[FID]
left join ICMO c on b.[FICMONo] = c.[FBillNo]
left join t_ICItemCore d on b.[FitemID] = d.[FitemID]
left join t_Item_3007 e on b.[FitemID] = e.[FitemID]
where a.[FJHDate] between @begTime and @endTime and 部门=@BM
group by a.[FID],a.[FJHDate],b.[FQty],b.[FZWMS],c.[Forderinterid],c.[Fsourceentryid],d.[FName]
GO
根据部门过滤,列出所有属于该部门的工序,要把工序列转行。求大神改造......
----