Posted on 2006-06-05
Last Modified: 2008-02-01
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

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
select 'Hospital Claims: ' + Cast(COALSCE(@Query2,0) As varchar(10)) as Totals
select  '<b>Subtotal:</b> ' + convert(varchar,(@Query1 - @Query2))

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

    LVL 7

    Accepted Solution


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

    My test values got left in there before.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    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.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now