计算客户每种商品最后一次订货与第二次订货之间的间隔日期是多少？

[lovelbb]

销售表：saletable （clientno:客户编号，goodsno商品编号，dhdate 订购日期）
销费周期表：Zqtable （zq_clientno:客户编号,zq_goodsno:商品编号,zq_zq：周期）,请大家帮帮忙，看看怎么能高效计算出来，客户及业务单据量都比较大。先谢谢大家！
create table saletable (proofno nvarchar(14),clientno nvarchar(4),goodsno nvarchar(2),dhdate datetime)
create table Zqtable(zq_clientno nvarchar(4),zq_goodsno nvarchar(2),zq_zq integer)
insert into saletable(proofno,clientno,goodsno,dhdate )
select 'DH200703110001','0003','01','2007-03-11' union
select 'DH200703120002','0001','02','2007-03-12' union
select 'DH200703130003','0001','01','2007-03-13' union
select 'DH200703010004','0001','03','2007-03-01' union
select 'DH200703110001','0002','01','2007-02-11' union
select 'DH200703120005','0001','02','2007-03-12' union
select 'DH200703130003','0003','01','2007-02-13' union
select 'DH200703010004','0001','03','2007-03-01'

[lovelbb]

最后一次订货与倒数第二次订货的日期间隔，贴字上写的不清楚，不第二次，是倒数第二次。：）

[mengmou]

--楼主是这个意思吗？
create table saletable (proofno nvarchar(14),clientno nvarchar(4),goodsno nvarchar(2),dhdate datetime)
create table Zqtable(zq_clientno nvarchar(4),zq_goodsno nvarchar(2),zq_zq integer)

insert into saletable(proofno,clientno,goodsno,dhdate )
select 'DH200703110001','0003','01','2007-03-11' union
select 'DH200703120002','0001','02','2007-03-12' union
select 'DH200703130003','0001','01','2007-03-13' union
select 'DH200703010004','0001','03','2007-03-01' union
select 'DH200703110001','0002','01','2007-02-11' union
select 'DH200703120005','0001','02','2007-03-12' union
select 'DH200703130003','0003','01','2007-02-13' union
select 'DH200703010004','0001','03','2007-03-01'

select _s.clientno,_s.goodsno,datediff(day,min(dhdate),max(dhdate)) as 最后一次订货与倒数第二次订货的间隔
from saletable _s
where proofno in(
select top 2 proofno
where _s.clientno = clientno
and _s.goodsno = goodsno order by dhdate desc)
group by _s.clientno,_s.goodsno
order by _s.clientno,_s.goodsno

drop table saletable,Zqtable

/*
clientno goodsno 最后一次订货与倒数第二次订货的间隔
-------- ------- -----------------
0001 01 0
0001 02 0
0001 03 0
0002 01 0
0003 01 26

（所影响的行数为 5 行）
*/

[lovelbb]

多谢呀！这么快就回复了，向您学习！十分感谢！

[mengmou]

呵呵。