Replace NULL value with Zero in SQL Server

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Jan 12, 2022 03:17 AM
Forum: SQL Server
Answer: 1
Views: 1815

I have this report that is working perfectly well but i have two(2) challenges after i modified the procedure .

1.) No zero(0) by the naira where there is no data it just show ₦, it is suppose to be ₦0 when there is no value in the database.

2.) No zero(0) for the invidual company column e.g ARPN,ARPNBULK,AGGREY,DELOG,DELOGBULK e.t.c when there is no value in the database.There is suppose to zero(0) there,but now the column is blank.

MY CODE

ALTER PROCEDURE [dbo].[Total_MealLog] 
    @Start VARCHAR(10), 
    @End VARCHAR(10) 
AS 
BEGIN 
    SET NOCOUNT ON; 
        
    SELECT CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS VARCHAR(100)) 'S/N',* 
    INTO #Data 
        FROM 
        ( 
            SELECT DISTINCT DATENAME(dw, Time1)+', ' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPN, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN')*(SELECT MAX(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPNCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULK, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULKCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREY, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREYCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECH, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECHCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOG, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOGCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULK, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULKCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOG, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOGCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEE, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEECOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULK, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULKCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITOR, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITORCOST, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOL, 
                (SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOLCOST 
            FROM MealLog ml 
            WHERE Time1 >= @Start and Time1 <= @End 
            GROUP BY Time1, Company, Exact 
        )t 
            
    SELECT * FROM #Data 
    UNION ALL 
    SELECT '','Total',SUM(ARPN),SUM(ARPNCOST),SUM(ARPNBULK),SUM(ARPNBULKCOST), 
    SUM(AGGREY),SUM(AGGREYCOST),SUM(CRIMETECH),SUM(CRIMETECHCOST),SUM(DELOG),SUM(DELOGCOST), 
SUM(DELOGBULK),SUM(DELOGBULKCOST),SUM(RDELOG),SUM(RDELOGCOST),SUM(TUSKEEGEE),SUM(TUSKEEGEECOST), 
    SUM(TUSKEEGEEBULK),SUM(TUSKEEGEEBULKCOST),SUM(VISITOR),SUM(VISITORCOST),SUM(MOPOL),SUM(MOPOLCOST) 
    FROM #Data 
     
    DROP TABLE #Data 
END

PLEASE HELP ME SIR

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Jan 12, 2022 03:18 AM
Report

Hi lingers,

Use ISNULL function.

Refer below query.

SQL

ALTER PROCEDURE [dbo].[Total_MealLog]  
    @Start VARCHAR(10),  
    @End VARCHAR(10)  
AS  
BEGIN  
    SET NOCOUNT ON;  
       
    SELECT CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS VARCHAR(100)) 'S/N',*  
    INTO #Data  
        FROM  
        (  
            SELECT DISTINCT DATENAME(dw, Time1)+', ' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN'),'0') ARPN,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN'),'0')*ISNULL((SELECT MAX(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN'),'0') ARPNCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK'),'0') ARPNBULK,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK'),'0') ARPNBULKCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY'),'0') AGGREY,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY'),'0') AGGREYCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH'),'0') CRIMETECH,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH'),'0') CRIMETECHCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG'),'0') DELOG,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG'),'0') DELOGCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK'),'0') DELOGBULK,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK'),'0') DELOGBULKCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG'),'0') RDELOG,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG'),'0') RDELOGCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE'),'0') TUSKEEGEE,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE'),'0') TUSKEEGEECOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK'),'0') TUSKEEGEEBULK,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK'),'0') TUSKEEGEEBULKCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR'),'0') VISITOR,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR'),'0') VISITORCOST,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL'),'0') MOPOL,  
                ISNULL((SELECT SUM(Exact) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL'),'0')*ISNULL((SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL'),'0') MOPOLCOST  
            FROM MealLog ml  
            WHERE Time1 >= @Start and Time1 <= @End  
            GROUP BY Time1, Company, Exact  
        )t  
           
    SELECT * FROM #Data  
    UNION ALL  
    SELECT '','Total',SUM(ARPN),SUM(ARPNCOST),SUM(ARPNBULK),SUM(ARPNBULKCOST),  
    SUM(AGGREY),SUM(AGGREYCOST),SUM(CRIMETECH),SUM(CRIMETECHCOST),SUM(DELOG),SUM(DELOGCOST),  
SUM(DELOGBULK),SUM(DELOGBULKCOST),SUM(RDELOG),SUM(RDELOGCOST),SUM(TUSKEEGEE),SUM(TUSKEEGEECOST),  
    SUM(TUSKEEGEEBULK),SUM(TUSKEEGEEBULKCOST),SUM(VISITOR),SUM(VISITORCOST),SUM(MOPOL),SUM(MOPOLCOST)  
    FROM #Data  
    
    DROP TABLE #Data  
END

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Replace NULL value with Zero in SQL Server

Company

Explore

Follow us

Contact Us