Grouping using two stored procedures

Hi,

I am creating a set of reports in Crystal showing emails sent and delivered from users within our organisation.

Each report uses a different stored procedure.

Report 1 shows emails sent:

Sender: Person in our org
Recipient: Person outside our org
Number: number of emails sent

Report 2 shows emails recived:

Sender: person outside our org
Recipient: Person in our org
Number: Number of emails received

In crystal, the reports are grouped around the sender for report 1, and the recipient for group 2 (therefore the reports are grouped around the person in our organisation).

Now I need to create a report showing the details of the two reports combined into one, but this creates a problem when i try to group. i need to distinguish between who is in our organisation and who is it, and then to group by them.

does anyone have any idea how this may be done?

i want the report to look like this:

Person in our organisation:
----------
Sent: bob@.yahoo.co.uk 26
sally@.hotmail.com 4
peter@.msn.com 12
Subtotal: 42

Received: fred@.company.co.uk 45
vicky@.hotmail.com 10
Subtotal 55

Total 97

and so on for each person.

Many thanks if you can helpEither create it as two subreports in Crystal, or use a UNION query to create a single dataset from both SQL statements. With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".|||With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".

how do i do this?

at the moment, the most i can come up with is

select * from vw_sent
union
select * from vw_received

and that's where my question comes from really, how to determine what addresses in each result are @.mydomain.co.uk and then to group by those. Becuase the resultset of this query is:

Sender Recipient Number
person@.mydomain.co.uk person@.hotmail.com 5
otherperson@.mydomain.co.uk person@.hotmail.com 2
otherperson@.hotmail.com person@.mydomain.co.uk 10

so how do i add an extra field in my resultset to show which email address is in my domain?|||select 'SENT' as Direction, * from vw_sent
union
select 'RECEIVED' as Direction, * from vw_received

...though you really should enumerate your field names instead of using *, especially in an UNION query.