Hi
I wasn't sure if you wanted something like this
select name, surname, job_title from employees
where dept ='Admin'
union
select name, surname, job_title from employees
where dept = 'Finance'
order by name
or if you actually want a stored proc example like this:
CREATE PROCEDURE [dbo].[SP_ORDER_ACTIVITY] @startdate as datetime, @enddate as datetime
AS
select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], sum(po.quantity * po.value)[PO Amount]
from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
UNION
select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], 0
from order_group g, order_header h, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no not in (select order_no from purchase_header where order_no is not null)
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
and g.order_group_no not in
(select g.order_group_no from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id)
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
order by g.order_group_no
GO
unfortunately I can't help you with crystal reports since we don't use crystal reports but maybe if you posted your union statement someone could have a look at it.
