我想计算一个关于超期天数
订单号 到期日期 提货日期 超期天数
a 2008-1-01 2008-1-11 10
a 2008-1-01 2008-1-15 4
a 2008-1-01 2008-1-17 2
a 2008-1-01 2008-1-20 3想了很久都不知道怎么计算,定义了几个变量来保存日期,然后判断也不行.
我是在Crystal Reports里面做的,不知道在SQL语句里面能否实现 Dim a as DateTime
Dim a1 as DateTime
Dim a3 as DateTimeif a = cDate("1111-1-11") and a1 = CDate("1111-1-11") and not isnull({testCQCZ.出库日期}) then
a = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
elseif a <> CDate("1111-1-11") and a1 = CDate("1111-1-11") and not isnull({testCQCZ.出库日期})then
a1 = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a - a1)<0 and not isnull({testCQCZ.出库日期}) then
a = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a - a1)>0 and not isnull({testCQCZ.出库日期}) then
a1 = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
end ifif a <> CDate("1111-1-11") and {testCQCZ.出库日期}>{testCQCZ.到期日期} and a1 = CDate("1111-1-11") then
formula = {?日期} - {testCQCZ.到期日期}
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a3 - a)>0 then
formula = a3 - a
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a3 - a1)>0 then
formula = a3 - a1
end if
订单号 到期日期 提货日期 超期天数
a 2008-1-01 2008-1-11 10
a 2008-1-01 2008-1-15 4
a 2008-1-01 2008-1-17 2
a 2008-1-01 2008-1-20 3想了很久都不知道怎么计算,定义了几个变量来保存日期,然后判断也不行.
我是在Crystal Reports里面做的,不知道在SQL语句里面能否实现 Dim a as DateTime
Dim a1 as DateTime
Dim a3 as DateTimeif a = cDate("1111-1-11") and a1 = CDate("1111-1-11") and not isnull({testCQCZ.出库日期}) then
a = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
elseif a <> CDate("1111-1-11") and a1 = CDate("1111-1-11") and not isnull({testCQCZ.出库日期})then
a1 = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a - a1)<0 and not isnull({testCQCZ.出库日期}) then
a = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a - a1)>0 and not isnull({testCQCZ.出库日期}) then
a1 = {testCQCZ.出库日期}
a3 = {testCQCZ.出库日期}
end ifif a <> CDate("1111-1-11") and {testCQCZ.出库日期}>{testCQCZ.到期日期} and a1 = CDate("1111-1-11") then
formula = {?日期} - {testCQCZ.到期日期}
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a3 - a)>0 then
formula = a3 - a
elseif a <> CDate("1111-1-11") and a1 <> CDate("1111-1-11") and (a3 - a1)>0 then
formula = a3 - a1
end if
a 2008-1-01 2008-1-11 10
a 2008-1-01 2008-1-15 4
a 2008-1-01 2008-1-17 2
a 2008-1-01 2008-1-20 3
是想根据前三列得到超期天数?
insert @tb
select 'a','2008-1-01','2008-1-11'
union all select 'a','2008-1-01','2008-1-15'
union all select 'a','2008-1-01','2008-1-17'
union all select 'a','2008-1-01','2008-1-20'select * ,超期天数=datediff(day,isnull((select max(提货日期) from @tb where 提货日期<a.提货日期),到期日期),提货日期)
from @tb a
insert into @t select 'a','2008-1-01','2008-1-11'
insert into @t select 'a','2008-1-01','2008-1-15'
insert into @t select 'a','2008-1-01','2008-1-17'
insert into @t select 'a','2008-1-01','2008-1-20'select px=(select count(1) from @t where 订单号=a.订单号 and 提货日期<=a.提货日期),* into # from @t aselect a.*,超期天数=datediff(dd,isnull(c.提货日期,a.到期日期),a.提货日期) from # a left join # c
on a.px=c.px+1
insert into tb values('a', '2008-1-01', '2008-1-11', 0 )
insert into tb values('a', '2008-1-01', '2008-1-15', 0 )
insert into tb values('a', '2008-1-01', '2008-1-17', 0 )
insert into tb values('a', '2008-1-01', '2008-1-20', 0 )
goselect 订单号,到期日期,提货日期,
超期天数 = case when (select top 1 提货日期 from tb where 订单号 = t.订单号 and 提货日期 < t.提货日期 order by 提货日期 desc) is null then datediff(day,到期日期,提货日期)
else datediff(day , (select top 1 提货日期 from tb where 订单号 = t.订单号 and 提货日期 < t.提货日期 order by 提货日期 desc),提货日期) end
from tb tdrop table tb/*
订单号 到期日期 提货日期 超期天数
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
a 2008-01-01 00:00:00.000 2008-01-11 00:00:00.000 10
a 2008-01-01 00:00:00.000 2008-01-15 00:00:00.000 4
a 2008-01-01 00:00:00.000 2008-01-17 00:00:00.000 2
a 2008-01-01 00:00:00.000 2008-01-20 00:00:00.000 3(所影响的行数为 4 行)
*/
insert into tb values('a', '2008-1-01', '2008-1-11', 0 )
insert into tb values('a', '2008-1-01', '2008-1-15', 0 )
insert into tb values('a', '2008-1-01', '2008-1-17', 0 )
insert into tb values('a', '2008-1-01', '2008-1-20', 0 )
goselect 订单号,到期日期,提货日期,
超期天数 = datediff(day, isnull((select top 1 提货日期 from tb where 订单号 = t.订单号 and 提货日期 < t.提货日期 order by 提货日期 desc),到期日期),提货日期)
from tb tdrop table tb/*
订单号 到期日期 提货日期 超期天数
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
a 2008-01-01 00:00:00.000 2008-01-11 00:00:00.000 10
a 2008-01-01 00:00:00.000 2008-01-15 00:00:00.000 4
a 2008-01-01 00:00:00.000 2008-01-17 00:00:00.000 2
a 2008-01-01 00:00:00.000 2008-01-20 00:00:00.000 3(所影响的行数为 4 行)
*/