查询一定时段内按时交付的供应商交付次数问题．．．．（在线等．．）分不够可以再加．

[弘毅致远]

有表结构如下:
POID ITEM_NO VDRN_NO DATE_1 DATE_2 DATE_3
P001 PCB01 S001 2004-02-13 2004-03-13 2004-03-14
P002 PCB02 S001 2004-01-10 2004-04-20 2004-03-10
P003 PCB03 S001 2005-10-11 2006-01-14 2005-11-23
POO4 PCB01 S002 2005-02-20 2005-04-14 2005-04-20
P005 PCB04 S002 2006-01-14 2006-02-25 2006-02-20
P006 PCB04 S002 2006-03-23 2006-05-23 2006-05-20
P007 PCB05 S002 2006-04-20 2006-05-20 2006-05-19
上表中POID--采购单号,ITEM_NO---物料号,VDRN_N0--供应商号,DATE_1--下单日期,DATE_2--应交付日期,DATE_3--实际交付日期..我想实现查询向供应商采购的次数和按时次数及分析按时率.
现在我只实现了查询向某供应商采购的次数,按时次数不知道怎么查....若按上表查询结果应如下:
VDRN_NO PURTIME ONTIMENUM ONTIMERATE
SOO1 3 2 66.66%
S002 4 3 75.00%

具体怎么样实现呢????

[弘毅致远]

前面那段sql语句是这样写的:
select vdrn_no,purtime=count(*) from table group by vdrn_cn order by vdrn_no
实现了向供应商采购的次数,但不知道怎么得出按时的次数,还用count好像不行,另两个日期不好运算..

[hhhdyj]

CREATE TABLE tb([POID] varchar(10), [ITEM_NO] varchar(10), [VDRN_NO] varchar(10), [DATE_1] DATETIME, [DATE_2] DATETIME, [DATE_3] DATETIME)
INSERT INTO tb
SELECT 'P001', 'PCB01', 'S001', '2004-02-13', '2004-03-13', '2004-03-14'
UNION ALL SELECT 'P002', 'PCB02', 'S001', '2004-01-10', '2004-04-20', '2004-03-10'
UNION ALL SELECT 'P003', 'PCB03', 'S001', '2005-10-11', '2006-01-14', '2005-11-23'
UNION ALL SELECT 'POO4', 'PCB01', 'S002', '2005-02-20', '2005-04-14', '2005-04-20'
UNION ALL SELECT 'P005', 'PCB04', 'S002', '2006-01-14', '2006-02-25', '2006-02-20'
UNION ALL SELECT 'P006', 'PCB04', 'S002', '2006-03-23', '2006-05-23', '2006-05-20'
UNION ALL SELECT 'P007', 'PCB05', 'S002', '2006-04-20', '2006-05-20', '2006-05-19'

SELECT VDRN_NO, COUNT(1) AS PURTIME, SUM(CASE WHEN DATE_2 >= DATE_3 THEN 1.0 ELSE 0.0 END) AS ONTIMENUM,
CAST(SUM(CASE WHEN DATE_2 >= DATE_3 THEN 1.0 ELSE 0.0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS VARCHAR) + '%' AS ONTIMERATE
FROM TB GROUP BY VDRN_NO

DROP TABLE tb

[九斤半]

CREATE TABLE TEST(POID NCHAR(4),ITEM_NO NCHAR(5),VDRN_NO NCHAR(4),DATE_1 DATETIME,DATE_2 DATETIME,DATE_3 DATETIME)
INSERT TEST
SELECT 'P001','PCB01','S001','2004-02-13','2004-03-13','2004-03-14' UNION ALL
SELECT 'P002','PCB02','S001','2004-01-10','2004-04-20','2004-03-10' UNION ALL
SELECT 'P003','PCB03','S001','2005-10-11','2006-01-14','2005-11-23' UNION ALL
SELECT 'POO4','PCB01','S002','2005-02-20','2005-04-14','2005-04-20' UNION ALL
SELECT 'P005','PCB04','S002','2006-01-14','2006-02-25','2006-02-20' UNION ALL
SELECT 'P006','PCB04','S002','2006-03-23','2006-05-23','2006-05-20' UNION ALL
SELECT 'P007','PCB05','S002','2006-04-20','2006-05-20','2006-05-19'
SELECT * FROM TEST

SELECT
VDRN_NO,
COUNT(*) AS PURTIME,
SUM(CASE WHEN DATEDIFF(DAY,DATE_2,DATE_3)<=0 THEN 1 ELSE 0 END) AS ONTIMENUM,
SUM(CASE WHEN DATEDIFF(DAY,DATE_2,DATE_3)<=0 THEN 1 ELSE 0 END)/COUNT(*) AS ONTIMERATE
FROM TEST
GROUP BY VDRN_NO

DROP TABLE TEST

[marco08]

--借用上面兄弟的數據
select VDRN_NO,
PURTIME=count(*),
ONTIMENUM=sum(case when DATE_3<DATE_2 then 1 end),
ONTIMERATE=cast(sum(case when DATE_3<DATE_2 then 1 end) as decimal)/ cast(count(*) as decimal)
from TEST
group by VDRN_NO

[中国风]

declare @TEST TABLE (POID NCHAR(4),ITEM_NO NCHAR(5),VDRN_NO NCHAR(4),DATE_1 DATETIME,DATE_2 DATETIME,DATE_3 DATETIME)
INSERT @TEST
SELECT 'P001','PCB01','S001','2004-02-13','2004-03-13','2004-03-14' UNION ALL
SELECT 'P002','PCB02','S001','2004-01-10','2004-04-20','2004-03-10' UNION ALL
SELECT 'P003','PCB03','S001','2005-10-11','2006-01-14','2005-11-23' UNION ALL
SELECT 'POO4','PCB01','S002','2005-02-20','2005-04-14','2005-04-20' UNION ALL
SELECT 'P005','PCB04','S002','2006-01-14','2006-02-25','2006-02-20' UNION ALL
SELECT 'P006','PCB04','S002','2006-03-23','2006-05-23','2006-05-20' UNION ALL
SELECT 'P007','PCB05','S002','2006-04-20','2006-05-20','2006-05-19'
--select count(*)from @test
select vdrn_no,PURTIME=(select count(POID) from @test where VDRN_NO=a.VDRN_NO) ,
记录=count(ITEM_NO),
ONTIMERATE= (select cast(sum(case when DATE_2!<DATE_3 then 1 else 0 end)as decimal(15,2))
from @test where VDRN_NO=a.VDRN_NO)/(select count(POID) from @test where VDRN_NO=a.VDRN_NO)
from @test a
where DATE_1=(select max(DATE_1) from @test where ITEM_NO=a.ITEM_NO )
group by vdrn_no


（所影响的行数为 7 行）

vdrn_no PURTIME 记录 ONTIMERATE
------- ----------- ----------- ----------------------------
S001 3 2 .6666666666666
S002 4 3 .7500000000000

（所影响的行数为 2 行）

[中国风]

select vdrn_no,PURTIME=(select count(POID) from @test where VDRN_NO=a.VDRN_NO) ,
ONTIMENUM=count(ITEM_NO),
ONTIMERATE= (select cast(sum(case when DATE_2!<DATE_3 then 1 else 0 end)as decimal(15,2))
from @test where VDRN_NO=a.VDRN_NO)/(select count(POID) from @test where VDRN_NO=a.VDRN_NO)
from @test a
where DATE_1=(select max(DATE_1) from @test where ITEM_NO=a.ITEM_NO )
group by vdrn_no

（所影响的行数为 7 行）

vdrn_no PURTIME ONTIMENUM ONTIMERATE
------- ----------- ----------- ----------------------------
S001 3 2 .6666666666666
S002 4 3 .7500000000000

（所影响的行数为 2 行）

[弘毅致远]

TO:YiZhiNet(九斤半)
你的那个求按时率好像不行....谢谢各位高手....向大家学习ING.....

[弘毅致远]

TO:roy_88(论坛新星_燃烧你的激情！！

[弘毅致远]

TO:roy_88(论坛新星_燃烧你的激情！！
select vdrn_no,PURTIME=(select count(POID) from @test where VDRN_NO=a.VDRN_NO) ,
ONTIMENUM=count(ITEM_NO),
ONTIMERATE= (select cast(sum(case when DATE_2!<DATE_3 then 1 else 0 end)as decimal(15,2))
from @test where VDRN_NO=a.VDRN_NO)/(select count(POID) from @test where VDRN_NO=a.VDRN_NO)
from @test a
where DATE_1=(select max(DATE_1) from @test where ITEM_NO=a.ITEM_NO )
group by vdrn_no

其中test前面的@什么意思呀?为什么我编译的时候必需得把那个@去掉才行....不然报错....

[yu_galaxy]

select VDRN_NO,PURTIME,ONTIMENUM,convert(varchar(20),ONTIMENUM/PURTIME*100)+'%' as ONTIMERATE from((select VDRN_NO,count(*) as PURTIME from 表) as A inner join(
select VDRN_NO,count(*) as ONTIMENUM from 表 where DATE_3 <=DATE_2 ) as B on A.VDRN_NO=B.VDRN_NO)

[dawugui]

CREATE TABLE TEST(POID NCHAR(4),ITEM_NO NCHAR(5),VDRN_NO NCHAR(4),DATE_1 DATETIME,DATE_2 DATETIME,DATE_3 DATETIME)
INSERT TEST
SELECT 'P001','PCB01','S001','2004-02-13','2004-03-13','2004-03-14' UNION ALL
SELECT 'P002','PCB02','S001','2004-01-10','2004-04-20','2004-03-10' UNION ALL
SELECT 'P003','PCB03','S001','2005-10-11','2006-01-14','2005-11-23' UNION ALL
SELECT 'POO4','PCB01','S002','2005-02-20','2005-04-14','2005-04-20' UNION ALL
SELECT 'P005','PCB04','S002','2006-01-14','2006-02-25','2006-02-20' UNION ALL
SELECT 'P006','PCB04','S002','2006-03-23','2006-05-23','2006-05-20' UNION ALL
SELECT 'P007','PCB05','S002','2006-04-20','2006-05-20','2006-05-19'

SELECT A.VDRN_NO , A.PURTIME , B.ONTIMENUM , CAST(CAST(B.ONTIMENUM*1.00/A.PURTIME AS DECIMAL(18,4))*100 AS VARCHAR(7)) + '%' AS ONTIMERATE FROM
(SELECT VDRN_NO , COUNT(*) AS PURTIME FROM TEST GROUP BY VDRN_NO) A ,
(SELECT VDRN_NO , COUNT(*) AS ONTIMENUM FROM TEST WHERE DATE_3 <= DATE_2 GROUP BY VDRN_NO) B
WHERE A.VDRN_NO = B.VDRN_NO

DROP TABLE TEST

--结果
VDRN_NO PURTIME ONTIMENUM ONTIMERATE
------- ----------- ----------- ----------
S001 3 2 66.6700%
S002 4 3 75.0000%

（所影响的行数为 2 行）