0

I'm working on creating an exception report that will look for issues across the database and display in in a dashboard.

Things like 5 files where Date of Birth is missing, 2 files where Age is greater than 100 years, 10 clients with status Open and a file close date exists...

really an unlimited number of exceptions. I'm hoping to put each possible exception into a table, along with the select query required to generate the data.

The web design would then be an alert box that shows all the non-zero exceptions found.

What would be the most efficient way to go about doing this in SQL Server?

user2826824
  • 13
  • 1

1 Answers1

1


Hope you are new to sql. Thats fine
Create a table for exceptions and select the data from the table which ever has an exception. For example you can have a query as,

 INSERT INTO EXCEPTION_TABLE
       SELECT * FROM TABLE_NAME 
       WHERE DOB IS NULL OR
             AGE > 100 OR
             .......   OR  ----------- add all your exception possiblities
             .......
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
  • Hope you got it. Any issues let me know, will help you – Jim Macaulay Sep 13 '16 at 19:12
  • Certainly more helpful (and appropriate) than ajeh's comment – John Cappelletti Sep 13 '16 at 19:17
  • Thank you for the response and that is sort of where I am right now. I was reading about prepared statements - this was mysql centric though http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496 – user2826824 Sep 13 '16 at 19:22
  • Would it be feasible to have a table with headings: Exception Query then data DOD Null,"Select * from Clients..." so that the maintenance of the exceptions and associated queries can be done in a separate interface. I wasn't planning on making the table full of exceptions public so not too worried about injection, but there are a lot of them, and wanted to make the back end less cluttered. – user2826824 Sep 13 '16 at 19:32