Get the latest record added to an audit table and send email

Question

I am trying to get the latest record which is added to an audit table and send an email to some people in my organisation with the details of the record added but not sure how to do this.

I am using Sharepoint SSRS report to show the data using the following query -

-- for records which have updated BUSTYPE
SELECT UPPER(caa.Comments) + ' Record' 'SQLAction'
    , c.BUS_TYPE AS CurrentBusType
    , caa.BUS_TYPE AS PrevBusType
    , caa.LastModified
    , c.CUSTOMERID
    , c.NAME
    , c.ADDRESS1
    , c.ADDRESS2
    , c.CITY
    , c.STATE
    , c.ZIP, 
FROM CUSTOMER_Audit AS caa
INNER JOIN CUSTOMER AS c ON caa.CUSTOMERID = c.CUSTOMERID 
where ISNULL(caa.BUS_TYPE,'') != ISNULL(c.BUS_TYPE,'')
AND (ISNULL(caa.BUS_TYPE,'') = '' OR ISNULL(c.BUS_TYPE,'') = '')
AND ISNULL(c.BUS_TYPE,'') = ''
AND DATEDIFF(dd, CONVERT(date, caa.LastModified), GETDATE()) <= 30 -- last 30 days filter

UNION

-- for records which are deleted
SELECT UPPER(caa.Comments) + ' Record' 'SQLAction'
    ,'' AS CurrentBusType
    , caa.BUS_TYPE AS PrevBusType
    , caa.LastModified
    , caa.CUSTOMERID
    , caa.NAME
    , caa.ADDRESS1
    , caa.ADDRESS2
    , caa.CITY
    , caa.STATE
    , caa.ZIP 
FROM CUSTOMER_Audit AS caa
WHERE caa.Comments LIKE '%Deleted%'
AND DATEDIFF(dd, CONVERT(date, caa.LastModified), GETDATE()) <= 30
ORDER BY caa.LastModified DESC

How can I send email for only the last records which are added after the previous email was sent with some records.

The new email should not send the updated/deleted records which have already been sent in the previous email.

I tried using snapshot in Sharepoint but it is sending all the snapshots with the same records which have already been sent (along with the new records if there are any). Don't know if I am doing something wrong there.

Or, should I adjust my SQL query to only show the latest records? But how can I do that?

My SQL Server is 2008 R2.

Answer 1

A very simple solution is to create a table which records the last record displayed on a report e.g.

CREATE TABLE dbo.ReportHistory (
    -- Following is a way to identify the report, could be plain text
    -- Could be the ID of the report in SSRS
  Report varchar(128)
  , LastIdQueried bigint
)

Then each time you run the report you update this table. And is assuming you pull your results via a stored procedure.

However this is problematic in that if you run the report manually from SSRS, without emailing the results out, you'll not be notified about them.

Therefore what I tend to do in a situation like this is create my own snapshot, triggered off an agent job at a time of my choosing, and then display the data from this static snapshot. My schema for this snapshot (simplified) is:

CREATE TABLE dbo.ReportInstance (
    Id bigint NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED,
    DateOfReportRun datetime2(0) NULL,
    -- Following is a way to identify the report, could be plain text
    -- Could be the ID of the report in SSRS
    Report varchar(128) NULL
);

CREATE TABLE dbo.ReportInstanceItem (
    Id bigint NOT NULL IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
    ReportInstance bigint NULL REFERENCES dbo.ReportInstance(Id),
    -- Store the ids from the target table which are part of this report
    RecordId bigint NULL
);