前两天公司同事需求，根据日期向表中插入欠缺的日期，其他字段内容取前一个日期行的内容，其中一个字段设置为0
ifobject_id('test','U') IS NOT NULL
DROPTABLE test
createtable test
([date]varchar(200),
categoryvarchar(200),
ytd int,
mtd int)
insertinto test
SELECT *
FROM(values('201505','A',1,1),
('201509','A',1,1),
('201503','B',1,1),
('201504','B',1,1),
('201508','B',1,1)
)ASA([DATE],CATEGORY,YTD,MTD)
一种方式：利用循环
DECLARE@MIN_DATE VARCHAR(6)
SELECT@MIN_DATE=CAST(MIN(RIGHT([DATE],2)) AS INT) FROM TEST
WHILE(@MIN_DATE<=12)
BEGIN
INSERTINTO test
SELECTCAST(B.date AS INT)+1,
B.category,
B.YTD,
0
FROM TEST
RIGHTJOIN (SELECT *,
LEFT([DATE],4) AS [YEAR],
CAST(RIGHT([DATE],2) ASINT)+1 AS [MONTH]
FROMTEST) B
ONLEFT(TEST.[date],4)=B.[YEAR]
ANDCAST(RIGHT(TEST.[date],2) AS INT)=B.[MONTH]
ANDTEST.category=B.category
WHERETEST.category IS NULL
SET@MIN_DATE=@MIN_DATE+1
DELETETEST WHERE RIGHT(date,2)>12
END
select *
from test
order by category,
[date]
第二种方法：
declare @mindate varchar(6),@maxdate varchar(6)
select @mindate=min([date])from test
select @maxdate=max([date])from test
;
with dt([date]) as
(select @mindate
union all
select convert(varchar(6),cast(dt.[date] as int)+1) as [date]
from dt
where convert(varchar(6),cast(dt.[date] as int)+1) <=@maxdate
)
,
category as
(select *
from dt
cross join (select distinct
category
from test) as a
)
,
cte as
(select category.[date],
category.category,
isnull(test.ytd,0) as ytd,
isnull(test.mtd,0) as mtd
from test
right join category
on test.[date]=category.[date]
and test.category=category.category
)
select cte.[date],
cte.category,
isnull((select max(ytd) from cte as c where c.category=cte.category and c.[date]<=cte.[date]),0) as ytd,
cte.mtd
from cte
order by cte.category,
cte.[date]
