CODEWITHSUNDEEP
sqlsql-server
Kris Edison
Kris Edison

Reputation: 119

Checking for missing data in SQL

I am having a hard time with this not knowing if there's a solution for this.

I am trying to detect missing hourly data. Sample:

Table HRLY_DATA:

NAME          HOUR
Me            0
Me            1
Me            2
Me            3
Me            6
Me            7
You           0
You           1
You           2
You           3
You           4
You           5
You           6
You           7

As you can see, [HOUR] data of Me is missing 4 and 5. I want a query that will output:

NAME          HOUR
Me            4
Me            5

For now, here's what I've got:

SELECT d.NAME, HR FROM HRs c
LEFT OUTER JOIN
   (
   SELECT distinct a.NAME
   FROM HRLY_DATA a
   INNER JOIN
      (
      SELECT NAME FROM
         (
         SELECT NAME, count(*) as CNT
         FROM
            (
            SELECT DISTINCT NAME, HOUR
            FROM HRLY_DATA
            ) as i
         GROUP BY NAME
         ) as ii
      WHERE CNT < 8
      ) as b
   ON a.NAME=b.NAME
   ) as d
ON c.HR=d.HOUR
WHERE d.HOUR IS NULL

HRs

HR
0
1
2
3
4
5
6
7

I am getting this output:

NAME          HR
NULL          4
NULL          5

Data for HOUR will range only from 0 - 7..

BTW, I am using SQL SERVER/ MSSQL for this.

:(

Sorry if I can't explain my problem clearly. :(

Upvotes: 1

Views: 903

Answers (1)

TechDo
TechDo

Reputation: 18629

Please try:

select 
    distinct x.NAME, number HOUR
From 
    master.dbo.spt_values cross join HRLY_DATA x
where number between 0 and 7
except
select NAME, HOUR FROM HRLY_DATA

Since table HR contains data 0-7, try:

select 
    distinct NAME, HR
From 
    @HR cross join HRLY_DATA 
except
select * from HRLY_DATA

Upvotes: 3

Related Questions