试试下面的,你可以更改系统日期看效果. create table tb(start datetime,[end] datetime, title as (case when start>getdate() then '准促销' when start<=getdate() and [end]>=getdate() then '促销' when datediff(d,[end],getdate())<2 then '回报日' when datediff(d,[end],getdate())<3 then '终止日' when datediff(d,[end],getdate())>=3 then '无效' end) ) go insert into tb(start,[end]) select '2011-06-09','2011-06-11' union all select '2011-06-08','2011-06-10' union all select '2011-06-10','2011-06-11' go select * from tb /* start end title ----------------------- ----------------------- ------ 2011-06-09 00:00:00.000 2011-06-11 00:00:00.000 促销 2011-06-08 00:00:00.000 2011-06-10 00:00:00.000 促销 2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 准促销(3 行受影响) */ go drop table tb
一:楼上(qianjin036a)貌似只显示了一天的啊,我用的 T=CASE WHEN,感觉太麻烦, 例如:Title =CASE WHEN start IS NULL THEN '准促销'... ,每个条件都要显示5天(5列)啊! 二.5天从当前实际日期算起。(如:今天:2011-06-09,所以上面的T+N,是从6.9开始算,明天6.10则为第一天.依此算)
要这样? create table tb(start datetime,[end] datetime) insert into tb select '2011-06-09','2011-06-11' union all select '2011-06-08','2011-06-10' union all select '2011-06-10','2011-06-11' go select *, (case when start>getdate() then '准促销' when start<=getdate() and [end]>=getdate() then '促销' when datediff(d,[end],getdate())<2 then '回报日' when datediff(d,[end],getdate())<3 then '终止日' when datediff(d,[end],getdate())>=3 then '无效' end) as [T+0], (case when start>dateadd(d,1,getdate()) then '准促销' when start<=dateadd(d,1,getdate()) and [end]>=dateadd(d,1,getdate()) then '促销' when datediff(d,[end],dateadd(d,1,getdate()))<2 then '回报日' when datediff(d,[end],dateadd(d,1,getdate()))<3 then '终止日' when datediff(d,[end],dateadd(d,1,getdate()))>=3 then '无效' end) as [T+1], (case when start>dateadd(d,2,getdate()) then '准促销' when start<=dateadd(d,2,getdate()) and [end]>=dateadd(d,2,getdate()) then '促销' when datediff(d,[end],dateadd(d,2,getdate()))<2 then '回报日' when datediff(d,[end],dateadd(d,2,getdate()))<3 then '终止日' when datediff(d,[end],dateadd(d,2,getdate()))>=3 then '无效' end) as [T+2], (case when start>dateadd(d,3,getdate()) then '准促销' when start<=dateadd(d,3,getdate()) and [end]>=dateadd(d,3,getdate()) then '促销' when datediff(d,[end],dateadd(d,3,getdate()))<2 then '回报日' when datediff(d,[end],dateadd(d,3,getdate()))<3 then '终止日' when datediff(d,[end],dateadd(d,3,getdate()))>=3 then '无效' end) as [T+3], (case when start>dateadd(d,4,getdate()) then '准促销' when start<=dateadd(d,4,getdate()) and [end]>=dateadd(d,4,getdate()) then '促销' when datediff(d,[end],dateadd(d,4,getdate()))<2 then '回报日' when datediff(d,[end],dateadd(d,4,getdate()))<3 then '终止日' when datediff(d,[end],dateadd(d,4,getdate()))>=3 then '无效' end) as [T+4] from tb /* start end T+0 T+1 T+2 T+3 T+4 ----------------------- ----------------------- ------ ------ ------ ------ ------ 2011-06-09 00:00:00.000 2011-06-11 00:00:00.000 促销 促销 回报日 回报日 终止日 2011-06-08 00:00:00.000 2011-06-10 00:00:00.000 促销 回报日 回报日 终止日 无效 2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 准促销 促销 回报日 回报日 终止日(3 行受影响) */ go drop table tb
恩,是您这样的意思。好像有些瑕疵:因为‘回报日’和‘终止日’在每行里[每个起止时间段]各只会出现一次['准促销'和'促销'才可能会出现多次]。 (PS:另外:好像可以在每个判断前首先加上:WHEN start IS NULL THEN '准促销' .)
create table tb(start datetime,[end] datetime) insert into tb select '2011-06-09','2011-06-11' union all select '2011-06-08','2011-06-10' union all select '2011-06-10','2011-06-11' go declare @dt datetime set @dt=convert(varchar(10),getdate(),120) select *, (case when start>@dt then '准促销' when start<=@dt and [end]>=@dt then '促销' when datediff(d,[end],@dt)<=1 then '回报日' when datediff(d,[end],@dt)<=2 then '终止日' when datediff(d,[end],@dt)>2 then '无效' end) as [T+0], (case when start>dateadd(d,1,@dt) then '准促销' when start<=dateadd(d,1,@dt) and [end]>=dateadd(d,1,@dt) then '促销' when datediff(d,[end],dateadd(d,1,@dt))<=1 then '回报日' when datediff(d,[end],dateadd(d,1,@dt))<=2 then '终止日' when datediff(d,[end],dateadd(d,1,@dt))>2 then '无效' end) as [T+1], (case when start>dateadd(d,2,@dt) then '准促销' when start<=dateadd(d,2,@dt) and [end]>=dateadd(d,2,@dt) then '促销' when datediff(d,[end],dateadd(d,2,@dt))<=1 then '回报日' when datediff(d,[end],dateadd(d,2,@dt))<=2 then '终止日' when datediff(d,[end],dateadd(d,2,@dt))>2 then '无效' end) as [T+2], (case when start>dateadd(d,3,@dt) then '准促销' when start<=dateadd(d,3,@dt) and [end]>=dateadd(d,3,@dt) then '促销' when datediff(d,[end],dateadd(d,3,@dt))<=1 then '回报日' when datediff(d,[end],dateadd(d,3,@dt))<=2 then '终止日' when datediff(d,[end],dateadd(d,3,@dt))>2 then '无效' end) as [T+3], (case when start>dateadd(d,4,@dt) then '准促销' when start<=dateadd(d,4,@dt) and [end]>=dateadd(d,4,@dt) then '促销' when datediff(d,[end],dateadd(d,4,@dt))<=1 then '回报日' when datediff(d,[end],dateadd(d,4,@dt))<=2 then '终止日' when datediff(d,[end],dateadd(d,4,@dt))>2 then '无效' end) as [T+4] from tb /* start end T+0 T+1 T+2 T+3 T+4 ----------------------- ----------------------- ------ ------ ------ ------ ------ 2011-06-09 00:00:00.000 2011-06-11 00:00:00.000 促销 促销 促销 回报日 终止日 2011-06-08 00:00:00.000 2011-06-10 00:00:00.000 促销 促销 回报日 终止日 无效 2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 准促销 促销 促销 回报日 终止日(3 行受影响) */ go drop table tb
create table tb(start datetime,[end] datetime,
title as (case when start>getdate() then '准促销'
when start<=getdate() and [end]>=getdate() then '促销'
when datediff(d,[end],getdate())<2 then '回报日'
when datediff(d,[end],getdate())<3 then '终止日'
when datediff(d,[end],getdate())>=3 then '无效'
end)
)
go
insert into tb(start,[end])
select '2011-06-09','2011-06-11' union all
select '2011-06-08','2011-06-10' union all
select '2011-06-10','2011-06-11'
go
select * from tb
/*
start end title
----------------------- ----------------------- ------
2011-06-09 00:00:00.000 2011-06-11 00:00:00.000 促销
2011-06-08 00:00:00.000 2011-06-10 00:00:00.000 促销
2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 准促销(3 行受影响)
*/
go
drop table tb
create table tb(start datetime,[end] datetime)
insert into tb
select '2011-06-09','2011-06-11' union all
select '2011-06-08','2011-06-10' union all
select '2011-06-10','2011-06-11'
go
select *,
(case when start>getdate() then '准促销'
when start<=getdate() and [end]>=getdate() then '促销'
when datediff(d,[end],getdate())<2 then '回报日'
when datediff(d,[end],getdate())<3 then '终止日'
when datediff(d,[end],getdate())>=3 then '无效' end) as [T+0],
(case when start>dateadd(d,1,getdate()) then '准促销'
when start<=dateadd(d,1,getdate()) and [end]>=dateadd(d,1,getdate()) then '促销'
when datediff(d,[end],dateadd(d,1,getdate()))<2 then '回报日'
when datediff(d,[end],dateadd(d,1,getdate()))<3 then '终止日'
when datediff(d,[end],dateadd(d,1,getdate()))>=3 then '无效' end) as [T+1],
(case when start>dateadd(d,2,getdate()) then '准促销'
when start<=dateadd(d,2,getdate()) and [end]>=dateadd(d,2,getdate()) then '促销'
when datediff(d,[end],dateadd(d,2,getdate()))<2 then '回报日'
when datediff(d,[end],dateadd(d,2,getdate()))<3 then '终止日'
when datediff(d,[end],dateadd(d,2,getdate()))>=3 then '无效' end) as [T+2],
(case when start>dateadd(d,3,getdate()) then '准促销'
when start<=dateadd(d,3,getdate()) and [end]>=dateadd(d,3,getdate()) then '促销'
when datediff(d,[end],dateadd(d,3,getdate()))<2 then '回报日'
when datediff(d,[end],dateadd(d,3,getdate()))<3 then '终止日'
when datediff(d,[end],dateadd(d,3,getdate()))>=3 then '无效' end) as [T+3],
(case when start>dateadd(d,4,getdate()) then '准促销'
when start<=dateadd(d,4,getdate()) and [end]>=dateadd(d,4,getdate()) then '促销'
when datediff(d,[end],dateadd(d,4,getdate()))<2 then '回报日'
when datediff(d,[end],dateadd(d,4,getdate()))<3 then '终止日'
when datediff(d,[end],dateadd(d,4,getdate()))>=3 then '无效' end) as [T+4]
from tb
/*
start end T+0 T+1 T+2 T+3 T+4
----------------------- ----------------------- ------ ------ ------ ------ ------
2011-06-09 00:00:00.000 2011-06-11 00:00:00.000 促销 促销 回报日 回报日 终止日
2011-06-08 00:00:00.000 2011-06-10 00:00:00.000 促销 回报日 回报日 终止日 无效
2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 准促销 促销 回报日 回报日 终止日(3 行受影响)
*/
go
drop table tb
(PS:另外:好像可以在每个判断前首先加上:WHEN start IS NULL THEN '准促销' .)
insert into tb
select '2011-06-09','2011-06-11' union all
select '2011-06-08','2011-06-10' union all
select '2011-06-10','2011-06-11'
go
declare @dt datetime
set @dt=convert(varchar(10),getdate(),120)
select *,
(case when start>@dt then '准促销'
when start<=@dt and [end]>=@dt then '促销'
when datediff(d,[end],@dt)<=1 then '回报日'
when datediff(d,[end],@dt)<=2 then '终止日'
when datediff(d,[end],@dt)>2 then '无效' end) as [T+0],
(case when start>dateadd(d,1,@dt) then '准促销'
when start<=dateadd(d,1,@dt) and [end]>=dateadd(d,1,@dt) then '促销'
when datediff(d,[end],dateadd(d,1,@dt))<=1 then '回报日'
when datediff(d,[end],dateadd(d,1,@dt))<=2 then '终止日'
when datediff(d,[end],dateadd(d,1,@dt))>2 then '无效' end) as [T+1],
(case when start>dateadd(d,2,@dt) then '准促销'
when start<=dateadd(d,2,@dt) and [end]>=dateadd(d,2,@dt) then '促销'
when datediff(d,[end],dateadd(d,2,@dt))<=1 then '回报日'
when datediff(d,[end],dateadd(d,2,@dt))<=2 then '终止日'
when datediff(d,[end],dateadd(d,2,@dt))>2 then '无效' end) as [T+2],
(case when start>dateadd(d,3,@dt) then '准促销'
when start<=dateadd(d,3,@dt) and [end]>=dateadd(d,3,@dt) then '促销'
when datediff(d,[end],dateadd(d,3,@dt))<=1 then '回报日'
when datediff(d,[end],dateadd(d,3,@dt))<=2 then '终止日'
when datediff(d,[end],dateadd(d,3,@dt))>2 then '无效' end) as [T+3],
(case when start>dateadd(d,4,@dt) then '准促销'
when start<=dateadd(d,4,@dt) and [end]>=dateadd(d,4,@dt) then '促销'
when datediff(d,[end],dateadd(d,4,@dt))<=1 then '回报日'
when datediff(d,[end],dateadd(d,4,@dt))<=2 then '终止日'
when datediff(d,[end],dateadd(d,4,@dt))>2 then '无效' end) as [T+4]
from tb
/*
start end T+0 T+1 T+2 T+3 T+4
----------------------- ----------------------- ------ ------ ------ ------ ------
2011-06-09 00:00:00.000 2011-06-11 00:00:00.000 促销 促销 促销 回报日 终止日
2011-06-08 00:00:00.000 2011-06-10 00:00:00.000 促销 促销 回报日 终止日 无效
2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 准促销 促销 促销 回报日 终止日(3 行受影响)
*/
go
drop table tb