34,576
社区成员
发帖
与我相关
我的任务
分享
DECLARE @variables1 DECIMAL(15,2)
DECLARE @variables2 DECIMAL(15,2)
--供应商准时交货的次数
SELECT @variables1=(
SELECT COUNT(DISTINCT (CG001+CG002+CG003))--采购单别,采购单号,采购单序号
,CG004 --供应商
FROM PURTD CG LEFT JOIN JH ON CG001=JH006 AND CG002=JH007
WHERE CG004<JH004 AND CG005>20161201 --预交货日<进货日期
GROUP BY CG004
)
--总交货次数
SELECT @variables2=(
SELECT COUNT(DISTINCT (CG001+CG002+CG003)),CG004
FROM PURTD CG LEFT JOIN JH ON CG001=JH006 AND CG002=JH007
WHERE CG005>20161201
GROUP BY CG004
)
--
select cast(ISNULL(cast((@variables1 / Case When @variables2=0 then 1 else
@variables2 end)*100 as decimal(15,2)),0) as varchar(20)) As '准时交货率'
;WITH T AS (
SELECT CG001,CG002,CG003,CG004,
CG005,JH004,
CASE WHEN JH004 IS NULL THEN
0
WHEN CG005 > JH004 THEN
1
ELSE
0
END ZS
FROM PURTD CG
LEFT JOIN JH
ON CG001=JH006
AND CG002=JH007
AND CG003=JH008
WHERE CG005>20161201
)
SELECT CG004,
COUNT(*) AS 总交货次数,
COUNT(*)-COUNT(JH004) AS 未交货次数,
COUNT(JH004) AS 已交货次数,
SUM(ZS) AS 准时交货次数,
SUM(ZA)*100.0 / COUNT(*) AS 准时交货率
FROM T
GROUP BY CG004