日期:2014-05-17 浏览次数:20590 次
with tb(a,b,c)as(
select '201001',1655,297.9 union
select '201002',1655,297.9 union
select '201003',1655,297.9 union
select '201004',1655,297.9 union
select '201005',1655,297.9 union
select '201006',1655,297.9 union
select '201007',1655,297.9 union
select '201008',1655,297.9 union
select '201009',1655,297.9 union
select '201010',1655,297.9 union
select '201011',1656,297.9 union
select '201012',1655,297.9)
,tc as (select * from tb where a in(
select a from tb a where
exists(select 1 from tb where (convert(int,a.a)-1=a or convert(int,a)-a.a=1) and a.b!=b )
union all
select top 1 A from tb
union all
select top 1 a from tb order by a desc)
),td as(
select A,d=case when (select MIN(a) from tc where a.b=b and a.c=c and a.a<a )=
(select min(a) from tc where a.a<a) then (select MIN(a) from tc where a.b=b and a.c=c and a.a<a )
else null end,
b,c from tc a
)
select a+isnull('~'+d,''),b,
c*isnull((DATEDIFF(month,convert(datetime,a+'01'),CONVERT(datetime,d+'01'))+1),1)
from td
where a not in(select isnull(d,'') from td)