--SQL 语句如下
CREATE TABLE #TMP
(
ID VARCHAR(10)
PERIOD VARCHAR(7),
NUM INT
)
INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3
SELECT * FROM #TMP
DROP TABLE #TMP
CREATE TABLE #TMP
(
ID VARCHAR(10),
PERIOD VARCHAR(7),
NUM INT
)
INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3
SELECT
ID ,PERIOD,
ISNULL((SELECT NUM FROM #TMP WHERE ID=a.ID AND DATEDIFF(m,PERIOD+'-01',a.PERIOD+'-01')=1),0)*a.NUM AS NUM
FROM #TMP AS a
DROP TABLE #TMP
CREATE TABLE #TMP
(
ID VARCHAR(10),
PERIOD VARCHAR(7),
NUM INT
)
INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3
go
;with cte as(
select *,0 as flg from #TMP a where not exists(select 1 from #TMP where ID=a.id and period<a.period)
union all
select id,CONVERT(varchar(7),dateadd(mm,1,period+'-01'),120),num,flg+1 from cte where CONVERT(varchar(7),dateadd(mm,1,period+'-01'),120)<=CONVERT(varchar(7),GETDATE(),120)
)select ID,PERIOD,NUM*flg num from cte
/*
ID PERIOD num
---------- ------- -----------
GB001 2011-09 0
GB002 2011-10 0
GB002 2011-11 3
GB001 2011-10 4
GB001 2011-11 8
*/
go
SELECT * FROM #TMP
DROP TABLE #TMP
SELECT DATEADD(m,number, CAST(period+'-01' as datetime))
,DATEDIFF(m, CAST(period+'-01' as datetime),DATEADD(m,number, CAST(period+'-01' as datetime))) *NUM
FROM #TMP ,master..spt_values where type='P' AND DATEPART(M , DATEADD(m,number, CAST(period+'-01' as datetime)) ) <=datepart(M,GETDATE())
AND DATEPART(YYYY , DATEADD(m,number, CAST(period+'-01' as datetime)) ) <=datepart(YYYY ,GETDATE())
CREATE TABLE #TMP
(
ID VARCHAR(10),
PERIOD VARCHAR(7),
NUM INT
)
INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3
--SELECT * FROM #TMP
declare @t datetime
set @t='2011-09-01'
;with f as
(
select
PERIOD=convert(varchar(7),dif,120)
from
(
select dif=dateadd(month,number,@t) from master..spt_values where type='p'
)t
where dif<getdate()
),
f1 as
(
select * from f cross join (select distinct ID from #TMP)b
)
select
a.*,
DATEDIFF(mm,b.PERIOD+'-01',a.PERIOD+'-01')*b.num
from
f1 a left join #TMP b
on
a.ID=b.ID
DROP TABLE #TMP
/*PERIOD ID
------- ---------- -----------
2011-09 GB001 0
2011-10 GB001 4
2011-11 GB001 8
2011-09 GB002 -3
2011-10 GB002 0
2011-11 GB002 3
(6 行受影响)
*/
CREATE TABLE #TMP
(
ID VARCHAR(10),
PERIOD VARCHAR(7),
NUM INT
)
INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3
SELECT
ID,
PERIOD=CONVERT(VARCHAR(7),DATEADD(m,b.langid,a.PERIOD+'-01'),120),
NUM=b.langid*NUM
FROM #TMP AS a,master.sys.syslanguages AS b
WHERE DATEDIFF(m,a.PERIOD+'-01',DATEADD(m,-b.langid,GETDATE()))>=0
ORDER BY 1,2
/*
ID PERIOD NUM
GB001 2011-09 0
GB001 2011-10 4
GB001 2011-11 8
GB002 2011-10 0
GB002 2011-11 3
*/
CREATE TABLE #TMP
(
ID VARCHAR(10),
PERIOD VARCHAR(7),
NUM INT
)
INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3
--SELECT * FROM #TMP
declare @t datetime
set @t='2011-09-01'
;with f as
(
select
PERIOD=convert(varchar(7),dif,120)
from
(
select dif=dateadd(month,number,@t) from master..spt_values where type='p'
)t
where dif<getdate()
),
f1 as
(
select * from f cross join (select distinct ID from #TMP)b
)
select
a.*,
DATEDIFF(mm,b.PERIOD+'-01',a.PERIOD+'-01')*b.num
from
f1 a left join #TMP b
on
a.ID=b.ID
where
DATEDIFF(mm,b.PERIOD+'-01',a.PERIOD+'-01')>=0
DROP TABLE #TMP
/*PERIOD ID
------- ---------- -----------
2011-09 GB001 0
2011-10 GB001 4
2011-11 GB001 8
2011-10 GB002 0
2011-11 GB002 3
(5 行受影响)
*/
