• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 803
  • Last Modified:

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))

0
ETKN
Asked:
ETKN
  • 2
1 Solution
 
ExpertAdminCommented:
This should yield a negative number when appropriate:

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

M@
0
 
ExpertAdminCommented:
oops....

select  '<b>Subtotal:</b> ' +  (CAST(COALESCE(Query1,0) - COALESCE(Query2,0) AS varchar(10))) AS Totals


My test values got left in there before.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now