SQL Script to loop
hi guys,
at moment want send email each supplier outstanding orders. have got loop setup correctly in ssis have issue script.
here's script below.
use db
select *
from
(
select d.email,
o.identity,
stuff((select ',' + stats + ' ' + parts
orderits
orderid = o.identity
, datediff(d, current_timestamp , duedate) = 8
xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')
items
orders o
inner join traders t
on o.traderid = t.id
inner join email d
on t.id = d.traderid
group o.identitfication, d.email
) x
where x.items not null
so @ moment getting seperate rows per order. when run loop email per order. how concatenate orders 1 email per supplier, order info.
so output looks @ moment
email order items
test@123.com 123 stamp, letter etc
test@123.com 124 pen pencil
test@123.com 125 ruler
so ideally want single line email order column , items columns joined looks following..
email order items
test123.com 123,124 125 stamp, letter etc pen pencil ruler
any ideas? thanks
i think this
use [dbname]
;with cte1 ( select * ( select d.email, o.identity [order], stuff((select ',' + stats + ' ' + parts orderits orderid = o.identity , datediff(d, current_timestamp , duedate) = 8 xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') items orders o inner join traders t on o.traderid = t.id inner join email d on t.id = d.traderid group o.identitfication, d.email ) x x.items not null ) select email, stuff((select ',' + [order] cte1 email = c.email xml path(''),type).value('.','nvarchar(max)'),1,1,'') [order], stuff((select ' ' + [items] cte1 email = c.email xml path(''),type).value('.','nvarchar(max)'),1,1,'') [items] (select distinct email cte1)c
please mark answer if helps solve issue visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/vmblogs
SQL Server > SQL Server Integration Services
Comments
Post a Comment