1

I have a table in SQL Server like

ID        Date
1          1/12/2016
1          2/12/2016
.....................
....................
.....................
1         31/12/2016
2          1/12/2016
2          2/12/2016

For each ID of the table there are 31 entries of date. That means for each day of month there are supposed to entries for each ID. Now For some errors, There are missing entries of some dates for some IDs. Suppose for ID-3, '12/12/2016' date is missing. The missing dates are totally random.

Now how can find out which ID has which missing dates. I means I need a result which can show ID with their missing dates from that table for December 2016 month.

Rifat Rahman
  • 129
  • 1
  • 1
  • 7
  • select * from table where Date = NULL; Is this you want? – Muhammad Saqlain Mar 04 '17 at 10:29
  • No. Because there are no Null Values for any ID. If any date is missing then the whole row is missing too. If ID-3's '12/12/2016's data is missing. Then for December Month's data it would show 30 entries. Where for all normal data- it will show 31 entries. – Rifat Rahman Mar 04 '17 at 10:33

2 Answers2

1

Explanation:

  1. Generate all dates for a month. Thanks to this answer Get All Dates of Given Month and Year in SQL Server
  2. Cross join it with distinct id and use name all_possible_comb for it. This will have all 31 days for all id.
  3. Now right join it with your table and filter the nulls to get missing records.

http://sqlfiddle.com/#!6/952d04/12

DECLARE @month AS INT = 12
DECLARE @Year AS INT = 2016

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a),
all_dates as (SELECT datefromparts(@year,@month,N) date FROM tally
WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))),
all_possible_Comb as (select * from all_dates 
       cross join (select distinct id as id from table1) t)
select a.* from table1 t
right join all_possible_comb a
on t.id=a.id and t.date=a.date
where t.id is null
Community
  • 1
  • 1
Utsav
  • 7,914
  • 2
  • 17
  • 38
0

The shortest query you can write assuming few things like, you don't need the days that no id is present(This will omit the holidays) (#table1 is your table)

select distinct a.date,b.id from #table1 a cross join (select distinct id
from #table1) b where convert(varchar,a.date,103)+convert(varchar,b.id) not 
in (select convert(varchar,date,103)+convert(varchar,id) from #table1)

In case you want data from and to date, you can just specify where date between conditions like 

select distinct a.date,b.id from #table1 a cross join (select distinct id
from #table1) b where convert(varchar,a.date,103)+convert(varchar,b.id) not 
in (select convert(varchar,date,103)+convert(varchar,id) from #table1) and 
date between '2016-12-01' and '2016-12-31'
Pream
  • 517
  • 4
  • 10