34,838
社区成员




select product,'0.1~0.21' as Dept,sty,StyID,per from products
where
isnull(per,'')<>'' and charindex('G',Per)>0 and cast((replace(Per,'G','.')) as numeric(18,3))<0.22
union all
select product,'0.22~0.99',sty,StyID,per from products
where
isnull(per,'')<>'' and charindex('G',Per)>0
and cast((replace(Per,'G','.')) as numeric(18,3))>=0.22
and cast((replace(Per,'G','.')) as numeric(18,3))<=0.99
union all
select product,'1G~9.9G',sty,StyID from products
where
isnull(per,'')<>'' and charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))<10
and (left(Per,charindex('G',Per)-1))>=1
union all
select product,'10G~100K',sty,StyID,per from products
where Per like '%K%'
union all
select product,'10G~100K',sty,StyID,per from products
where
isnull(per,'')<>'' and
charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))>=10
union all
select product,'100KG',sty,StyID,per from products
where Per like'%KG%'
create procedure P_ViewPro @st datetime,@en datetime
as
SELECT V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept, dbo.V_ProViewCore.sty,
dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per,
SUM(ISNULL(V_WorkSheetView.Anum, 0) + ISNULL(V_WorkSheetView.Bnum, 0)
+ ISNULL(V_WorkSheetView.Cnum, 0)) AS InNum,
SUM(ISNULL(WorkSheet_1.Anum, 0) + ISNULL(WorkSheet_1.Bnum, 0)
+ ISNULL(WorkSheet_1.Cnum, 0)) AS OutNum
FROM dbo.MakeItems INNER JOIN
V_WorkSheetView ON dbo.MakeItems.[Lot No] = V_WorkSheetView.[Lot No] INNER JOIN
dbo.V_ProViewCore ON
dbo.MakeItems.product = dbo.V_ProViewCore.product INNER JOIN
V_WorkSheetView WorkSheet_1 ON
dbo.MakeItems.[Lot No] = WorkSheet_1.[Lot No]
WHERE (V_WorkSheetView.orderid = '1') AND (WorkSheet_1.orderid = '6')
and (isnull(V_WorkSheetView.StarDate,'1900-01-01') between @st and @en)
GROUP BY V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept,
dbo.V_ProViewCore.sty, dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per
go
select * from V_WorkSheetView where V_WorkSheetView.StarDate between @st and @en
select * from V_WorkSheetView where V_WorkSheetView.StarDate between @st and @en
select product,'0.1~0.21' as Dept,sty,StyID,per from products
where charindex('G',Per)>0 and cast((replace(Per,'G','.')) as numeric(18,3))<0.22
union all
select product,'0.22~0.99',sty,StyID,per from products
where charindex('G',Per)>0
and cast((replace(Per,'G','.')) as numeric(18,3))>=0.22
and cast((replace(Per,'G','.')) as numeric(18,3))<=0.99
union all
select product,'1G~9.9G',sty,StyID from products
where charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))<10
and (left(Per,charindex('G',Per)-1))>=1
union all
select product,'10G~100K',sty,StyID,per from products
where Per like '%K%'
union all
select product,'10G~100K',sty,StyID,per from products
where
charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))>=10
union all
select product,'100KG',sty,StyID,per from products
where Per like'%KG%'
--Invalid length parameter passed to the substring function
create procedure P_ViewPro @stdatetime,@en datetime
as
SELECT V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept, dbo.V_ProViewCore.sty,
dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per,
SUM(ISNULL(V_WorkSheetView.Anum, 0) + ISNULL(V_WorkSheetView.Bnum, 0)
+ ISNULL(V_WorkSheetView.Cnum, 0)) AS InNum,
SUM(ISNULL(WorkSheet_1.Anum, 0) + ISNULL(WorkSheet_1.Bnum, 0)
+ ISNULL(WorkSheet_1.Cnum, 0)) AS OutNum
FROM dbo.MakeItems INNER JOIN
V_WorkSheetView ON dbo.MakeItems.[Lot No] = V_WorkSheetView.[Lot No] INNER JOIN
dbo.V_ProViewCore ON
dbo.MakeItems.product = dbo.V_ProViewCore.product INNER JOIN
V_WorkSheetView WorkSheet_1 ON
dbo.MakeItems.[Lot No] = WorkSheet_1.[Lot No]
WHERE (V_WorkSheetView.orderid = '1') AND (WorkSheet_1.orderid = '6')
and (V_WorkSheetView.StarDate between @st and @en)
GROUP BY V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept,
dbo.V_ProViewCore.sty, dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per
go
exec P_ViewPro '2007-12-01','2007-12-27'