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))
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))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select '<b>Subtotal:</b> ' + CAST(COALESCE(NULL,0) - COALESCE(30,0) AS varchar(10))
M@