Find Duplicate records in a table and send email notification

  • Hi,

    In my sql server database, there is table called Ticket_NUM and there are few fields like

    TicketDate, TicketNum,UploadDate,Amount, Name, DOB

    I want to find if there any duplicate records based on these columns(TicketDate, TicketNum,UploadDate,Amount) loaded for current date and time in the table , if yes find those records and send email notification to user by attaching duplicate records.

    Is there any process which can keep running and check every 10 minute against this table to find duplicate records for current date and time and send email notification to users.

  • Yes, write a query to identify any such duplicates.  Put it in a job and use Database Mail to notify the user if duplicates are found.

    I have to ask, would it not be easier to prevent duplicates from getting in the table in the first place?  You can do that with a primary key or unique constraint.

    John

  • Thanks John for reply.

    Yes due to some reason we can't stop loading duplicate at first place. we can't modify the table schema as it used by other processes.

    How I can use attachment and send email through job.Any help with sql script can be helpful.

  • Work your way through this, and post back if there's anything in particular you don't understand.

    John

  • Hi, I have setup sql job and used below query to get the data and then send email as attachment

     

    select @query = 'SET NOCOUNT ON;select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num

    group by TicketDate, TicketNum,UploadDate,Amount

    having count(*)>1 and TICKETNUM is not null

    and Name in (''VS Day Cash'',''VS Non Cash'')

    and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23) order by DATE desc;SET NOCOUNT OFF'

    exec msdb..sp_send_dbmail @recipients = @recip,

    @subject = @subject,

    @body = @body,

    @query = @query,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = @attch,

    @query_result_width = 400

     

    I am getting error while running the sql job.

    The exception is " Incorrect Syntax near VS"

     

    I am suing double quote , not sure where is the issue in syntax.

    Can someone please assist with correct sql.

  • You've put this in a job step, right?  Please will you post the whole code from the job step?  I suspect that what you've posted is only a snippet, since otherwise you'd get an error about variables not having been declared.

    You might consider only sending the e-mail if duplicates are found, otherwise the users you're sending it to may start to get annoyed!

    John

  • Yes I have put in job step.

    full script as requested.

     

    declare @subject varchar(255)

    , @query nvarchar(4000)

    , @body varchar(255)

    , @sep varchar(1)

    , @att varchar(50)

    , @recip varchar(255)

    select @recip = 's.smith@xyz.com;'

    select @body = ''

    select @subject = 'Duplicate Data - ' + convert(varchar, getdate()-1, 106)

    select @sep = char(9)

    select @att = 'Duplicate Data' + convert(varchar,getdate()-1, 112) + '.csv'

    declare @cnt int

    Select @cnt= count(*)

    from

    (

    select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num

    group by TicketDate, TicketNum,UploadDate,Amount

    having count(*)>1 and TICKETNUM is not null

    and Name in(''VS Day Cash'',''VS Non Cash'')

    and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23)

    ) newtable

    Print @cnt

    IF @cnt >1

    Begin

    select @query = 'SET NOCOUNT ON;select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num

    group by TicketDate, TicketNum,UploadDate,Amount

    having count(*)>1 and TICKETNUM is not null

    and Name in(''VS Day Cash'',''VS Non Cash'')

    and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23) order by DEALDATE desc;SET NOCOUNT OFF'

    exec msdb..sp_send_dbmail @recipients = @recip,

    @subject = @subject,

    @body = @body,

    @query = @query,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = @att,

    @query_result_separator = @sep,

    @query_result_width = 400

    END

    ELSE

    Begin

    select @subject = 'No Duplicate Found'

    exec msdb..sp_send_dbmail @recipients = @recip,

    @subject = @subject,

    @body = @body,

    @query = @query,

    @query_result_separator = @sep,

    @query_result_width = 400

    END

  • You need to take the double quotes out of the first query - since the query itself is not enclosed in quotes, they only need to be single.

    John

  • Sorry, It is single quote only in first query but still getting same exception.

  • So the code you posted isn't the code that's in your job step?  Please will you post the (whole) code that's giving the error?

    John

  • No need for double single quotes. Try this instead.

    AND Name IN ('VS DAY Cash', 'VS Non Cash')

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi Both,

    the code is same as I posted.

    However if I use the single quote in second query then also same issue.

     

    again posting the same code which is giving error

    declare @subject varchar(255)

    , @query nvarchar(4000)

    , @body varchar(255)

    , @sep varchar(1)

    , @att varchar(50)

    , @recip varchar(255)

    select @recip = 's.smith@xyz.com;'

    select @body = ''

    select @subject = 'Duplicate Data - ' + convert(varchar, getdate()-1, 106)

    select @sep = char(9)

    select @att = 'Duplicate Data' + convert(varchar,getdate()-1, 112) + '.csv'

    declare @cnt int

    Select @cnt= count(*)

    from

    (

    select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num

    group by TicketDate, TicketNum,Name,UploadDate,Amount,DOB

    having count(*)>1 and TICKETNUM is not null

    and Name in('VS Day Cash','VS Non Cash')

    and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23)

    ) newtable

    Print @cnt

    IF @cnt >1

    Begin

    select @query = 'SET NOCOUNT ON;select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num

    group by TicketDate, TicketNum,Name,UploadDate,Amount,DOB

    having count(*)>1 and TICKETNUM is not null

    and Name in(''VS Day Cash'',''VS Non Cash'')

    and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23) order by DEALDATE desc;SET NOCOUNT OFF'

    exec msdb..sp_send_dbmail @recipients = @recip,

    @subject = @subject,

    @body = @body,

    @query = @query,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = @att,

    @query_result_separator = @sep,

    @query_result_width = 400

    END

    ELSE

    Begin

    select @subject = 'No Duplicate Found'

    exec msdb..sp_send_dbmail @recipients = @recip,

    @subject = @subject,

    @body = @body,

    @query = @query,

    @query_result_separator = @sep,

    @query_result_width = 400

    END

  • The only thing I can think of, in that case, is that you have things in your HAVING clause that ought to be in a WHERE clause.

    SELECT
    TicketDate
    ,TicketNum
    ,ploadDate
    ,Amount
    ,Name
    ,DOB
    FROM Ticket_Num
    WHERE TicketNum IS NOT NULL
    AND Name IN ('VS Day Cash','VS Non Cash')
    AND CAST(UploadDate AS date) = CAST(GETDATE() AS date) -- date arithmetic is more efficient than character conversions
    GROUP BY
    TicketDate
    ,TicketNum
    ,ploadDate
    ,Amount
    ,Name
    ,DOB
    HAVING COUNT(*) > 1;

    John

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply