Replace NULL with zero in SQL Server

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Jan 07, 2022 05:47 AM
Forum: SQL Server
Answer: 1
Views: 1370

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

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.) Lastly there is always 0 in the column before the Total column,which is caused by the "s/n".

CREATE PROCEDURE [dbo].[Total_MealLog]
    @Start VARCHAR(10),
    @End VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;
     
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 'S/N',*
    INTO #Data
        FROM
        (
            SELECT DISTINCT DATENAME(dw, Time1)+', ' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPN,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN')*(SELECT MAX(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPNCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULK,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULKCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREY,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREYCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECH,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECHCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOG,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOGCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULK,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULKCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOG,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOGCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEE,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEECOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULK,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULKCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITOR,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITORCOST,
                (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOL,
                (SELECT COUNT(VALUE) 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

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

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Jan 07, 2022 10:40 AM
Report

Use below procedure. Use ISNULL to set zero.

SQL

CREATE 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 COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN'),0) ARPN
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN')*(SELECT MAX(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN'),0) ARPNCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK'),0) ARPNBULK
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK'),0) ARPNBULKCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY'),0) AGGREY
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY'),0) AGGREYCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH'),0) CRIMETECH
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH'),0) CRIMETECHCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG'),0) DELOG
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG'),0) DELOGCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK'),0) DELOGBULK
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK'),0) DELOGBULKCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG'),0) RDELOG
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG'),0) RDELOGCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE'),0) TUSKEEGEE
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE'),0) TUSKEEGEECOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK'),0) TUSKEEGEEBULK
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK'),0) TUSKEEGEEBULKCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR'),0) VISITOR
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR'),0) VISITORCOST
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL'),0) MOPOL
			,ISNULL((SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL')*(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 with zero in SQL Server

Company

Explore

Follow us

Contact Us