Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: 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 help

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Either 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".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    Quote Originally Posted by blindman
    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?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •