是不是根据dt1的时间和当前的时间比较得到差值
然后与dt2中的dayt比较根据不同的范围得到不同的z1
--测试数据
declare @tb_A table(tid int,updatetime datetime,price int)
declare @tb_B table(tid int,pp varchar(10),dayt int,zl decimal(10,2))
insert into @tb_A(tid,updatetime,price) values(1,'2010-04-01 00:00:00.000',50)
insert into @tb_A(tid,updatetime,price) values(2,'2010-04-02 00:00:00.000', 90)
insert into @tb_A(tid,updatetime,price) values(3,'2008-06-01 00:00:00.000', 100)
insert into @tb_A(tid,updatetime,price) values(4,'2008-11-01 00:00:00.000', 30)
insert into @tb_B(tid,pp,dayt,zl) values(1,'yy',30,0.8)
insert into @tb_B(tid,pp,dayt,zl) values(2,'yy',60,0.7)
insert into @tb_B(tid,pp,dayt,zl) values(3,'yy',90,0.6)
select (case when datediff(day,getdate(),a.updatetime)-b.dayt>30 then
0.8 else zl end) as zl,a.tid into #tt
From @tb_A a,@tb_B b where a.tid=b.tid
select a.price*b.zl as price from @tb_a a ,#tt b where a.tid=b.tid
drop table #tt
/*
id updatetime price dt1表中的updatetime和现在时间差
----------- ----------------------- ----------- -------------------------------
1 2010-04-01 00:00:00.000 50 139
2 2010-04-02 00:00:00.000 90 138
3 2008-06-01 00:00:00.000 100 808
4 2008-11-01 00:00:00.000 30 655
*/
/*
id pp dayt zl
----------- -------- ----------- ---------------------------------------
1 yy 30 0.8
2 yy 60 0.7
3 yy 90 0.6
*/
dt2中的zl由dt1表中的updatetime和现在时间差值再与dt2中的dayt比较得出(如小于30的为0.8)
------------
有命中的dayt吗
--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(id int, updatetime datetime, price int)
insert into #1
select 1, '2010-04-01 00:00:00.000', 50 union all
select 2, '2010-04-02 00:00:00.000', 90 union all
select 3, '2008-06-01 00:00:00.000', 100 union all
select 4, '2008-11-01 00:00:00.000', 30
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id int, pp varchar(8), dayt int, zl numeric(2,1))
insert into #2
select 1, 'yy', 30, 0.8 union all
select 2, 'yy', 60, 0.7 union all
select 3, 'yy', 90, 0.6
update #1 set price = price *
case
when datediff(day,updatetime,getdate()) < 30 then 0.8
when datediff(day,updatetime,getdate()) > 90 then 0.6
else 0.7
end
select * from #1
/*
id updatetime price
----------- ----------------------- -----------
1 2010-04-01 00:00:00.000 30
2 2010-04-02 00:00:00.000 54
3 2008-06-01 00:00:00.000 60
4 2008-11-01 00:00:00.000 18
*/
