Solved

# Coalesce

Posted on 2006-06-05
800 Views
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
Question by:ETKN

LVL 7

Expert Comment

This should yield a negative number when appropriate:

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

M@
0

LVL 7

Accepted Solution

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.