Create SQL job that verifies daily entry of data into a table?

Question

Writing my first SQL query to run specifically as a SQL Job and I'm a little out of my depth. I have a table within a SQL Server 2005 Database which is populated each day with data from various buildings. To monitor the system better, I am attempting to write a SQL Job that will run a query (or stored procedure) to verify the following:

- At least one row of data appears each day per building

My question has two main parts;

1. How can I verify that data exists for each building? While there is a "building" column, I'm not sure how to verify each one. I need the query/sp to fail unless all locations have reported it. Do I need to create a control table for the query/sp to compare against? (as the number of building reporting in can change)

2. How do I make this query fail so that the SQL Job fails? Do I need to wrap it in some sort of error handling code?

Table:

Employee   RawDate                     Building 
Bob        2010-07-22 06:04:00.000     2
Sally      2010-07-22 01:00:00.000     9
Jane       2010-07-22 06:04:00.000     12
Alex       2010-07-22 05:54:00.000     EA
Vince      2010-07-22 07:59:00.000     30

Note that the building column has at least one non-numeric value. The range of buildings that report in changes over time, so I would prefer to avoid hard-coding of building values (a table that I can update would be fine).

Should I use a cursor or dynamic SQL to run a looping SELECT statement that checks for each building based on a control table listing each currently active building?

Any help would be appreciated.

Edit: spelling

Answer 1

You could create a stored procedure that checks for missing entries. The procedure could call raiserror to make the job fail. For example:

if OBJECT_ID('CheckBuildingEntries') is null
    exec ('create procedure CheckBuildingEntries as select 1')
go
alter procedure CheckBuildingEntries(
    @check_date datetime)
as
declare @missing_buildings int

select  @missing_buildings = COUNT(*)
from    Buildings as b
left join
        YourTable as yt
on      yt.Building = b.name
        and dateadd(dd,0, datediff(dd,0,yt.RawDate)) = 
            dateadd(dd,0, datediff(dd,0,@check_date))
where   yt.Building is null

if @missing_buildings > 0
    begin
    raiserror('OMG!', 16, 0)
    end
go

An example scheduled job running at 4AM to check yesterday's entries:

declare @yesterday datetime
set @yesterday = dateadd(day, -1, GETDATE())
exec CheckBuildingEntries @yesterday

If an entry was missing, the job would fail. You could set it up to send you an email.

Test tables:

create table Buildings (id int identity, name varchar(50))
create table YourTable (Employee varchar(50), RawDate datetime, 
                        Building varchar(50))

insert into Buildings (name)
          select '2'
union all select '9'
union all select '12'
union all select 'EA'
union all select '30'

insert into YourTable (Employee, RawDate, Building)
           select 'Bob', '2010-07-22 06:04:00.000', '2'
union all select 'Sally', '2010-07-22 01:00:00.000', '9'
union all select 'Jane', '2010-07-22 06:04:00.000', '12'
union all select 'Alex', '2010-07-22 05:54:00.000', 'EA'
union all select 'Vince', '2010-07-22 07:59:00.000', '30'

Answer 2

Recommendations:

• Do use a control table for the buildings - you may find that one already exists, if you use the Object Explorer in SQL Server Management Studio

• Don't use a cursor or dynamic SQL to run a loop - use set based commands instead, possibly something like the following:

  SELECT BCT.Building, COUNT(YDT.Building) Build
  FROM   dbo.BuildingControlTable BCT
  LEFT JOIN dbo.YourDataTable YDT 
  ON BCT.Building = YDT.Building AND 
  CAST(FLOOR( CAST( GETDATE() AS FLOAT ) - 1 ) AS DATETIME ) =
      CAST(FLOOR( CAST( YDT.RawDate AS FLOAT ) ) AS DATETIME )
  GROUP BY BCT.Building