TS_NO 开始日期 浮动周期
UE1112 20010121 季
UE1243 20050909 半年
UE1244 20080530 季
UE8908 20080709 年求 TS_NO的20090930的下一个浮动日如UE8908 的20090930的下一个浮动日 是20100709一条语句完成!
UE1112 20010121 季
UE1243 20050909 半年
UE1244 20080530 季
UE8908 20080709 年求 TS_NO的20090930的下一个浮动日如UE8908 的20090930的下一个浮动日 是20100709一条语句完成!
20100709
20110709 那么求解:在20090930这个时点的NEXT的浮动日期应该是20100709
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-22 20:22:30
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (TS_NO varchar(6),开始日期 datetime,浮动周期 varchar(4))
INSERT INTO @tb
SELECT 'UE1112','20010121','季' UNION ALL
SELECT 'UE1243','20050909','半年' UNION ALL
SELECT 'UE1244','20080530','季' UNION ALL
SELECT 'UE8908','20080709','年'--SQL查询如下:DECLARE @TS_NO varchar(20),@DATE datetime;
SELECT @TS_NO = 'UE8908',@DATE = '20090930';SELECT
CASE WHEN 开始日期 > @DATE THEN
'此日期前还没有开始'
WHEN 浮动周期 = '季' THEN
CONVERT(varchar(10),DATEADD(month,3,CAST(RTRIM(YEAR(@DATE))
+SUBSTRING(CONVERT(varchar(8),开始日期,112),5,4) AS datetime)),120)
WHEN 浮动周期 = '半年' THEN
CONVERT(varchar(10),DATEADD(month,6,CAST(RTRIM(YEAR(@DATE))
+SUBSTRING(CONVERT(varchar(8),开始日期,112),5,4) AS datetime)),120)
WHEN 浮动周期 = '年' THEN
CONVERT(varchar(10),DATEADD(year,1,CAST(RTRIM(YEAR(@DATE))
+SUBSTRING(CONVERT(varchar(8),开始日期,112),5,4) AS datetime)),120)
END AS 下个浮动周期
FROM @tb
WHERE TS_NO = @TS_NO;
set @time='20090930'
select 浮动周期=(case when @time between '20010121' and '20050909' then dateadd(qq,1,@time)
when @time between '20050910' and '20080530' then dateadd(yy,1,@time)
when @time between '20080601' and '20080709' then dateadd(qq,1,@time)
when @time> '20080709' then dateadd(yy,1,@time) end)
from
tb
-----
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-22 20:33:59
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TS_NO] varchar(6),[开始日期] datetime,[浮动周期] varchar(4))
insert [tb]
select 'UE1112','20010121','季' union all
select 'UE1243','20050909','半年' union all
select 'UE1244','20080530','季' union all
select 'UE8908','20080709','年'
--------------开始查询--------------------------
declare @time datetime
set @time='20090930'
select distinct 浮动周期=(case when @time between '20010121' and '20050909' then dateadd(qq,1,@time)
when @time between '20050910' and '20080530' then dateadd(mm,6,@time)
when @time between '20080601' and '20080709' then dateadd(qq,1,@time)
when @time> '20080709' then dateadd(yy,1,@time) end)
from
tb
----------------结果----------------------------
/*浮动周期
-----------------------
2010-09-30 00:00:00.000(1 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-22 20:33:59
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TS_NO] varchar(6),[开始日期] datetime,[浮动周期] varchar(4))
insert [tb]
select 'UE1112','20010121','季' union all
select 'UE1243','20050909','半年' union all
select 'UE1244','20080530','季' union all
select 'UE8908','20080709','年'
--------------开始查询--------------------------
declare @time datetime
set @time='20090930'
select distinct 浮动周期=(case when @time between '20010121' and '20050909' then convert(varchar(10),dateadd(qq,1,@time),112)
when @time between '20050910' and '20080530' then convert(varchar(10),dateadd(mm,6,@time),112)
when @time between '20080601' and '20080709' then convert(varchar(10),dateadd(qq,1,@time),112)
when @time> '20080709' then convert(varchar(10),dateadd(yy,1,@time),112) end)
from
tb
----------------结果----------------------------
/*浮动周期
----------
20100930(1 行受影响)
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(TS_NO nvarchar(20),开始日期 datetime, 浮动周期 nvarchar(20))
goinsert into tb select
'UE1112','20010121' ,N'季' union all select
'UE1243','20050909' ,N'半年' union all select
'UE1244','20080530' ,N'季' union all select
'UE8908','20080709' ,N'年'
go
DECLARE @TS_NO varchar(20),@DATE datetime;
DECLARE @NEXTDATE DATETIME;SELECT @TS_NO = 'UE8908',@DATE = '20090930';select @NEXTDATE= case 浮动周期 when N'季' then DATEADD(MONTH,1,开始日期)
when N'半年' then DATEADD(MONTH ,6,开始日期)
when N'年' then DATEADD(YEAR ,1,开始日期) end
from tb where TS_NO=@TS_NOWhile @NEXTDATE<@DATE
select @NEXTDATE=case 浮动周期 when N'季' then DATEADD(MONTH,1,@NEXTDATE)
when N'半年' then DATEADD(MONTH ,6,@NEXTDATE)
when N'年' then DATEADD(YEAR ,1,@NEXTDATE) end
from tb where TS_NO=@TS_NO
SELECT 下一个浮动日=@NEXTDATE/*
下一个浮动日
-----------------------
2010-07-09 00:00:00.000(1 行受影响)*/
if object_id('tb')is not null drop table tb
go
create table tb(TS_NO varchar(10), 开始日期 datetime, 浮动周期 varchar(5))
insert tb select
'UE1112', '20010121' , '季' union all select
'UE1243', '20050909' ,'半年' union all select
'UE1244', '20080530', '季' union all select
'UE8908' , '20080709', '年'
select min(dateadd(mm,case when 浮动周期='年' then 12
when 浮动周期='季' then 3
when 浮动周期='半年' then 6
end * number
,开始日期)
)
from tb,master..spt_values s
where ts_no='UE8908' and type='p' and number>0
and
dateadd(mm,case when 浮动周期='年' then 12
when 浮动周期='季' then 3
when 浮动周期='半年' then 6
end * number
,开始日期) >'20090930'
-----------------------
2010-07-09 00:00:00.000(1 行受影响)
--
-- if OBJECT_ID('#tb') is not null
-- drop table #tb
--gocreate table #tb(TS_NO nvarchar(20),开始日期 datetime, 浮动周期 nvarchar(20))
goinsert into #tb select
'UE1112','20010121' ,N'季' union all select
'UE1243','20050909' ,N'半年' union all select
'UE1244','20080530' ,N'季' union all select
'UE8908','20080709' ,N'年'
goselect *, DateAdd(month,( DATEDIFF(month,开始日期,'2009-10-30')- DATEDIFF(month,开始日期,'2009-10-30') % T2.MonthSpan + T2.MonthSpan),开始日期) AS Result
from #tb T
inner join
(select TS_NO, case 浮动周期 when N'季' then 3
when N'半年' then 6
when N'年' then 12 end AS MonthSpan
from #tb) AS T2 ON T.ts_no=T2.ts_nodrop table #tbTS_NO 开始日期 浮动周期 TS_NO MonthSpan Result
-------------------- ------------------------------------------------------ -------------------- -------------------- ----------- ------------------------------------------------------
UE1112 2001-01-21 00:00:00.000 季 UE1112 3 2010-01-21 00:00:00.000
UE1243 2005-09-09 00:00:00.000 半年 UE1243 6 2010-03-09 00:00:00.000
UE1244 2008-05-30 00:00:00.000 季 UE1244 3 2009-11-30 00:00:00.000
UE8908 2008-07-09 00:00:00.000 年 UE8908 12 2010-07-09 00:00:00.000(所影响的行数为 4 行)