Im trying to make this into one select statement witout using
variables..

declare @sum1 int
declare @sum2 int
declare @sum3 int

select @sum1 = sum(CLIENT_PLANNED_SERVICE.TotalUnits)from
client_planned_service


select @sum2 = sum(RECORDED_SERVICE.UNITS)
from recorded_service
where (RECORDED_SERVICE.SERVICE_STATUS_MONIKER)is null or
RECORDED_SERVICE.SERVICE_STATUS_MONIKER='FB07BA368 454433A989B840BDCE7340F'
group by RECORDED_SERVICE.SERVICE_STATUS_MONIKER

set @sum3 = @sum1 - @sum2

select @sum3

is there a way? When I try this..

select (sum(CLIENT_PLANNED_SERVICE.TotalUnits) -
sum(RECORDED_SERVICE.UNITS)) as summary
from recorded_service, client_planned_service
where (RECORDED_SERVICE.SERVICE_STATUS_MONIKER)is null or
RECORDED_SERVICE.SERVICE_STATUS_MONIKER='FB07BA368 454433A989B840BDCE7340F'
group by RECORDED_SERVICE.SERVICE_STATUS_MONIKER

I get two records (because of the 2 condifitions in the where
clause)...I need one..

thanks

-Jim

"Jim" <jim.ferris@motorola.com> wrote in message
news:729757f9.0404260857.7b61b3dc@posting.google.c om...
> Im trying to make this into one select statement witout using
> variables..
>
> declare @sum1 int
> declare @sum2 int
> declare @sum3 int
>
> select @sum1 = sum(CLIENT_PLANNED_SERVICE.TotalUnits)from
> client_planned_service
>
>
> select @sum2 = sum(RECORDED_SERVICE.UNITS)
> from recorded_service
> where (RECORDED_SERVICE.SERVICE_STATUS_MONIKER)is null or
> RECORDED_SERVICE.SERVICE_STATUS_MONIKER='FB07BA368 454433A989B840BDCE7340F'
> group by RECORDED_SERVICE.SERVICE_STATUS_MONIKER
>
> set @sum3 = @sum1 - @sum2
>
> select @sum3
>
> is there a way? When I try this..
>
> select (sum(CLIENT_PLANNED_SERVICE.TotalUnits) -
> sum(RECORDED_SERVICE.UNITS)) as summary
> from recorded_service, client_planned_service
> where (RECORDED_SERVICE.SERVICE_STATUS_MONIKER)is null or
> RECORDED_SERVICE.SERVICE_STATUS_MONIKER='FB07BA368 454433A989B840BDCE7340F'
> group by RECORDED_SERVICE.SERVICE_STATUS_MONIKER
>
> I get two records (because of the 2 condifitions in the where
> clause)...I need one..
>
> thanks
>
> -Jim

Here's one possible way:

select sum(Total) as 'GrandTotal'
from
(
select sum(CLIENT_PLANNED_SERVICE.TotalUnits) as 'Total'
from client_planned_service
union all
select sum(RECORDED_SERVICE.UNITS)
from recorded_service
where (RECORDED_SERVICE.SERVICE_STATUS_MONIKER)is null or
RECORDED_SERVICE.SERVICE_STATUS_MONIKER='FB07BA368 454433A989B840BDCE7340F'
group by RECORDED_SERVICE.SERVICE_STATUS_MONIKER
) dt

Simon