摘要:按月份方式检查数据是否连续(2)
接上一话题,按月份方式检查数据是否连续,检查出不连续的具体月份数据.
代码如下:
declare @tab table(project_name varchar(50)/*小区*/,ym varchar(10)/*月份,格式为yyyymm*/,increase float/*价格环比增长率*/);
insert into @tab(project_name,ym,increase) values
('某小区','200911','-0.000437162'),('某小区','200912','-0.000437353'),('某小区','201001','-0.000437544'),
('某小区','201002','-0.00509823'),('某小区','201003','-0.00512436'),('某小区','201004','-0.00163201'),
('某小区','201005','-0.00163468'),('某小区','201006','-0.00163736'),('某小区','201007','-0.00164004'),
('某小区','201008','-0.00164274'),('某小区','201009','-0.00164544'),('某小区','201010','-0.00164815'),
('某小区','201011','0.00154091'),('某小区','201012','0.00153854'),('某小区','201101','0.00153618'),
('某小区','201102','0.00153382'),('某小区','201103','0.00739988'),('某小区','201104','0.00734552'),
('某小区','201105','0.00729196'),('某小区','201106','-0.00497764'),('某小区','201107','-0.00500254'),
('某小区','201108','-0.00502769'),('某小区','201109','-0.0050531'),('某小区','201110','-0.00507876'),
--('某小区','201111','-0.00510469'),('某小区','201112','-0.00513088'),('某小区','201201','0.011223'),
('某小区','201202','0.0110985'),('某小区','201203','-0.0291962');
--select * from @tab order by project_name,ym
--select project_name,count(*) as total_count
--,datediff(month,convert(datetime,stuff(min(ym),5,0,'-')+'-01'),convert(datetime,stuff(max(ym),5,0,'-')+'-01'))+1 as diff_month_value
--from @tab
--group by project_name
--having count(*)-datediff(month,convert(datetime,stuff(min(ym),5,0,'-')+'-01'),convert(datetime,stuff(max(ym),5,0,'-')+'-01'))-1!=0
select *
from
(
select a.*,b.ym as previous_ym,min(a.ym) over(partition by a.project_name) as min_ym
from @tab as a outer apply
(select * from @tab where project_name=a.project_name
and datediff(month,convert(datetime,stuff(ym,5,0,'-')+'-01'),convert(datetime,stuff(a.ym,5,0,'-')+'-01'))=1
) as b
) as result
where result.min_ym<>result.ym and result.previous_ym is null
----------执行结果如下-----------------------------------------------------
