Val

New Member
Hi,
I've created a process that take data from a database and create a collection like that in fig1.
This data must by taken from Outlook for send emails to those recipients and remind them that the activities must be completed within a certain date.
The process works fine, but Outlook send one mail to each recepient for each activity and it's not what we want to do.
I need to group for each recepient all the activities that must be completed like that in fig2 and send to them only one mail with all the expiring activities.
I think I need to create another collection but I have no idea how to group the data in the mail column. Can someone help me?
Thanks
 

Attachments

  • Fig1.JPG
    44 KB · Views: 83
  • Fig2.JPG
    38.9 KB · Views: 74

Val

New Member
A small update. I found a way to solve it but I still need help. The solution can be found using a SQL query that I don't know in deep.
Unfortunately I wrote two separate queries and I need to join both in only one, because the 2nd include all the records and the last row must to be excluded. I've added also what I need to obtain
Is there something that can help me?
 

Attachments

  • 1stquery.JPG
    28.3 KB · Views: 63
  • Result of 1st query.JPG
    35.2 KB · Views: 50
  • 2ndQuery.JPG
    29.7 KB · Views: 42
  • Result of 2nd query.JPG
    23.5 KB · Views: 35
  • What I must obtain.JPG
    28.3 KB · Views: 31

bot1robot

Member
@Val

  1. Take 3 data items , FirstName , LastName,EmailAddress
  2. Sort your collection in Fig1 based on FirstName, LastName,EmailAddress , hence your data will be similar to figure 2 with unique activity , completion date and duplicate first name , last name and email address.
  3. Now loop through collection derived in step 2 and store values of fields first ,last,email in data item diFirstName,diLastName,diEmailAddress
  4. Take decision stage and check
  5. If collection.First <>diFirstName and collection.Last<>diLastName and collection.Email<>diEmail then
, append activity name , completion date in mail body.
6. else
move to next row in collection.

7.End

let me know if you find any difficulty.
 

sivagelli

Well-Known Member
Try this-

Select T1.[NAME], T1. , T2.Activity, T1.[CompletionDate]
From
(Query1) T1
Left join
(Query2) T2
On T1.[EMAIL]=T2.[EMAIL]

Place the queries in the braces above.
 
Last edited:

Val

New Member
Done! But I still have some errors:

USE RPAUF;
SELECT task.[Nome], task., ST2.[Activity], task.[CompletionDate]
FROM [RPAUF].[dbo].[task]
LEFT Join
(SELECT Main.Email,
LEFT(Main.Task,Len(Main.Task)-1) As Activity
FROM
(
SELECT DISTINCT ST2.Email,
(
SELECT ST1.Activity + ',' AS [text()]
FROM dbo.Task ST1
WHERE ST1.Email = ST2.Email
ORDER BY ST1.Email
FOR XML PATH ('')
)
FROM dbo.Task ST2
)
ON task.[EMail]=ST1.[EMail]

The error is:
Incorrect syntax near the keyword 'ON'.
 

sivagelli

Well-Known Member
Val, in the query i suggested in place of Query1 paste the entire sql query from 1stQuery.jpg that you have attached, similarly paste the query in 2ndQuery.jpg in place of Query2. That should work ideally.
 

Val

New Member
You are right.
But I've reached the same result with this modified query:

SELECT
Main.Email,
Main.Name,
Main.CompletionDate,
LEFT(Main.Task,Len(Main.Task)-1) As Activity
FROM (

SELECT DISTINCT ST2.Email, ST2.Name, ST2.CompletionDate, (

SELECT ST1.Activity + ', ' AS [text()]
FROM dbo.task ST1
WHERE
ST1.Email = ST2.Email

and

CONVERT(varchar,ST1.CompletionDate,111) = CONVERT(varchar,GETDATE()+7,111)

ORDER BY ST1.Email

FOR XML PATH ('')

) [Task]

FROM dbo.Task ST2

WHERE CONVERT(varchar,ST2.CompletionDate,111) = CONVERT(varchar,GETDATE()+7,111)

) [Main]"
 
Top