Link to home
Start Free TrialLog in
Avatar of ETKN
ETKN

asked on

Coalesce

PART 2 of question I asked earilier.

What do I do with 'select  '<b>Subtotal:</b> ' + convert(varchar,(@Query1 - @Query2))'.  How do I use COALESCE when I need to subtract. If @query1 is zero then I should get a negative number


ALTER          PROCEDURE dbo.spGettotal
  @RptDate DateTime
AS

set nocount on

Declare @Query1 int
Declare @Query2 int
 
   Select @Query1 = Sum(Total)
     from (select distinct Total, billtype,unitid
             from tblBilling
            where convert(char(8), RptDate, 112) = @RptDate -1)as TMP

Select @Query2 = Sum(Totals)
     from (select distinct Batch, Totals
             from tblHospital
            where RptDate= @RptDate and Type LIKE '%KIP)as TMP

select 'Claims: '  + Cast(COALESCE(@Query1,0) As varchar(10)) as Totals
union
select 'Hospital Claims: ' + Cast(COALSCE(@Query2,0) As varchar(10)) as Totals
union
select  '<b>Subtotal:</b> ' + convert(varchar,(@Query1 - @Query2))

Avatar of ExpertAdmin
ExpertAdmin

This should yield a negative number when appropriate:

select  '<b>Subtotal:</b> ' +  CAST(COALESCE(NULL,0) - COALESCE(30,0) AS varchar(10))

M@
ASKER CERTIFIED SOLUTION
Avatar of ExpertAdmin
ExpertAdmin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial