CODEWITHSUNDEEP
sql-server
user1143726
user1143726

Reputation: 175

Find missing months from the result set obtained from query

I have a set of results returning from myTable as below

2011-05-02 00:00:00.000 
2011-08-01 00:00:00.000
2011-09-01 00:00:00.000
2011-10-03 00:00:00.000
2011-11-01 00:00:00.000
2011-12-01 00:00:00.000

I want to write a query to return 2 since 2 months are missing from the resultset.So that I can raise an error to break the code.

In case the dataset looks like this

        2011-05-02 00:00:00.000
        2011-05-02 00:00:00.000
        2011-05-02 00:00:00.000
        2011-05-02 00:00:00.000          
        2011-08-01 00:00:00.000
        2011-08-01 00:00:00.000 
        2011-08-01 00:00:00.000
        2011-08-01 00:00:00.000

Again return 2 since 2 months are missing

Upvotes: 0

Views: 34

Answers (1)

T I
T I

Reputation: 9933

try

declare @min date
declare @max date

select @min = min(date_col), @max = max(date_col)
from tbl

;with cte as (
  select distinct m = month(date_col), y = year(date_col)
  from tbl
)
, cal as (
  select m = month(d), y = year(d)
  from (
    select d = dateadd(m, row_number() over(order by (select 1)) - 1, @min)
    from master.dbo.spt_values
  ) t
  where d <= @max
)

select count(1)
from cal
where not exists(
  select 1
  from cte
  where cte.m = cal.m
  and cte.y = cal.y
)

SQL Fiddle

Upvotes: 2

Related Questions