stopa stopa - 5 months ago 20
SQL Question

SQL Server - Using ISNULL to eliminate NULL reading

I have completed a query, and it runs fine. Now I am working to modify it so that the output shows

[$0]
instead of
NULL
.

Does anyone know exactly how to to that?

This is what I did:

Select I.Store
, I.ISNULL([Inventory $ TOTAL],0)
, S.ISNULL([Sales $ TOTAL],0)
, R.ISNULL([Receipts $ TOTAL],0)
From
( Select Store
, '$'+Cast(Sum(QTY*Cost) As Varchar(20)) [Inventory $ TOTAL]
From Inventory Group By Store
) I
Left Outer Join
( Select Store
, '$'+Cast(Sum(QTY*Unit_Price) As Varchar(20)) [Sales $ TOTAL]
From Sales Group By Store
) S
On S.Store = I.Store
Left Outer Join
( Select Store
, '$'+Cast(Sum(QTY*Unit_Cost) As Varchar(20)) [Receipts $ TOTAL]
From Receipts Group By Store
) R
On R.Store = I.Store


-- Here is the Output

Store Inventory TOTAL SALES TOTAL RECEIPTS TOTAL
01 $852.94 $12371.41 $1015.16
02 $4192.21 $3714.74 NULL
03 $215.73 NULL NULL

Answer
Select  I.Store
      , '$'+ ISNULL(Cast(Sum(I.QTY * I.Cost)       As Varchar(20)) , '0') AS [Inventory $   TOTAL]
      , '$'+ ISNULL(Cast(Sum(S.QTY * S.Unit_Price) As Varchar(20)) , '0') AS [Sales $  TOTAL]
      , '$'+ ISNULL(Cast(Sum(R.QTY * R.Unit_Cost)  As Varchar(20)) , '0') AS [Receipts $  TOTAL]
From Inventory I
Left Outer Join Sales    S On S.Store = I.Store 
Left Outer Join Receipts R On R.Store = I.Store 
GROUP BY I.Store
Comments