Sql-server – Query to send mail to multiple users with their corresponding tickets

sql serversql-server-2008-r2

I have a table which has a list of ticket owners and and tickets which are assigned to them which are of high priority.

For example:

enter image description here

I need to send mail from SQL Server using sp_send_dbmail to John with all the tickets assigned to him in a single mail. This is a huge table so I want send mails to users with their assigned tickets in single mail instead of sending one mail per ticket. Any help would be appreciated.

Best Answer

Below is how you do it ...

  • Create table and add processed column

        create table dbo.Tickets (Ticket bigint, [Owner] varchar(50))
go
insert into dbo.Tickets 
select 14675, 'John'
union all 
select 38759, 'John'
union all
select 45879,'Catheline'
union all
select 23980, 'Mark'
union all
select 16754, 'John'

--- add a processed column so that next time the db mail job runs, it wont send out dupe emails
alter table dbo.Tickets add processed bit not null default(0)
go

  • Use below query

    -- query to get comma seperated values
select [Owner], Tickets = stuff ((select  N', '+ convert(nvarchar(max),innerT.Ticket)  
                                    from dbo.Tickets innerT
                                    where innerT.[Owner] = outerT.[Owner]
                                    group by innerT.Ticket -- get rid of dupes
                                    order by innerT.Ticket -- just in case you want order
                                FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
from dbo.Tickets outerT
group by [Owner]

    enter image description here

  • Use above query to send HTML email using database mail.

  • Update the dbo.Tickets table and set processed = 1 for the owners that you emailed the tickets.

Note: you can use case statements based on the owners and email them seperately.

This is a pseudo code that will help you 80% in what you want to achieve.

Related Question