Hi Lingers,
You need to use ISNULL function to the columns you want to check the zero.
IF the expression is NULL then ISNULL will replace the null with Zero.
Please refer below updated code.
Procedure
CREATE PROCEDURE GetData
AS
BEGIN
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
SET @DynamicPivotQuery =N'
;WITH cte AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ''S/N'',
Datename(dw, Time1)+'', '' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,
NAND, NAND * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''NAND'') NANDCOST,
LOGGER, LOGGER * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''LOGGER'') LOGGERCOST,
SECURITY, SECURITY * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''SECURITY'') SECURITYCOST
FROM (SELECT Time1,Exact,Company from MealLog) t
pivot ( MAX(Exact) FOR Company IN ([LOGGER],[NAND],[SECURITY])) piv)
SELECT CAST([S/N] AS VARCHAR(10)) [S/N],QUERYDATE,
ISNULL(NAND,0) NAND,ISNULL(NANDCOST,0) NANDCOST,
ISNULL(LOGGER,0) LOGGER,ISNULL(LOGGERCOST,0) LOGGERCOST,
ISNULL(SECURITY,0) SECURITY,ISNULL(SECURITYCOST,0) SECURITYCOST
FROM cte
UNION SELECT ''TOTAL'','''',
SUM(CAST(ISNULL(NAND,0) AS BIGINT)),SUM(CAST(ISNULL(NANDCOST,0) AS BIGINT)),
SUM(CAST(ISNULL(LOGGER,0) AS BIGINT)),SUM(CAST(ISNULL(LOGGERCOST,0) AS BIGINT)),
SUM(CAST(ISNULL(SECURITY,0) AS BIGINT)),SUM(CAST(ISNULL(SECURITYCOST,0) AS BIGINT))
FROM cte'
EXEC (@DynamicPivotQuery)
END
HTML
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center" border="1px" style="width: 811px">
<tr>
<td align="center" class="style4" style="font-size: large; color: #006699; font-family: Cambria; font-weight: bold;">
<table style="width: 100%; height: 7px; color: #FFFFFF; background-color: #000099;" bgcolor="#0066CC">
<tr>
<td style="background-color: #336699" class="style5" align="right">
<asp:LinkButton ID="LinkButton2" runat="server" ForeColor="White"
PostBackUrl="H1.aspx">Home</asp:LinkButton>
| <asp:LinkButton
ID="LinkButton1" runat="server" ForeColor="White"
PostBackUrl="Lgin.aspx">Sign out</asp:LinkButton>
</td>
</tr>
</table>
<br />
REPORT FOR ALL WORK TICKET CATEGORY FROM
<asp:Label ID="Label112" runat="server"></asp:Label>
TO
<asp:Label ID="Label113" runat="server"></asp:Label>
<br />
<table id='tblDetails' border='1' style="width: 1012px; font-family: Cambria; font-size: 15px; font-size: small; font-weight: normal;">
<tr>
<td class="style8">S/N</td>
<td class="style9">QUERYDATE</td>
<td class="style19">NAND</td>
<td class="style20"><b>COST </b> </td>
<td class="style19">LOGGER</td>
<td class="style20"><b>COST </b> </td>
<td class="style19">SECURITY</td>
<td class="style20"><b>COST </b> </td>
</tr>
<%
SqlConnection dbConn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
SqlCommand scz = new SqlCommand("GetData", dbConn);
scz.CommandType = CommandType.StoredProcedure;
dbConn.Open();
SqlDataReader sdz = scz.ExecuteReader();
while (sdz.Read())
{
if (!string.IsNullOrEmpty(sdz["QUERYDATE"].ToString()))
{
Response.Write(@"<tr>" +
"<td class='style2'>" + sdz["S/N"].ToString() + "</td>" +
"<td class='style2'>" + sdz["QUERYDATE"].ToString() + "</td>" +
"<td class='style5'>" + sdz["NAND"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["NANDCOST"].ToString() + "</td> " +
"<td class='style5'>" + sdz["LOGGER"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["LOGGERCOST"].ToString() + "</td>" +
"<td class='style5'>" + sdz["SECURITY"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["SECURITYCOST"].ToString() + "</td>" +
"</tr>");
}
else
{
Response.Write(@"<tr style='font-size: small; font-weight: normal; font-style: normal; font-family: Arial, Helvetica, sans-serif'>" +
"<td class='style22'></td>" +
"<td class='style23'>Total</td>" +
"<td class='style6'><span style='font-weight:bold;color:#3366CC;' id='Label2'>" + sdz["NAND"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label1'>" + "₦" + sdz["NANDCOST"].ToString() + "</span></td>" +
"<td class='style13'><span style='font-weight:bold;color:#3366CC;' id='Label3'>" + sdz["LOGGER"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label4'>" + "₦" + sdz["LOGGERCOST"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label7'>" + sdz["SECURITY"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label8'>" + "₦" + sdz["SECURITYCOST"].ToString() + "</span></td>" +
"</tr>");
}
}
dbConn.Close();%>
</table>
<asp:Label ID="LabelF" runat="server" ForeColor="#3366CC"></asp:Label>
<br />
<asp:Label ID="Label60" runat="server" ForeColor="#3366CC"></asp:Label>
<br />
<table border="1" style="width: 100%;">
<tr>
<td class="style16">GRAND TOTAL FOR NANDCOST+LOGGERCOST+SECURITYCOST</td>
<td class="style15">₦<asp:Label ID="Label114" runat="server" Font-Bold="True" Font-Size="Large"></asp:Label>
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
var nandCost = $("[id*=tblDetails] tr:last-child").find('td').eq(3).text().replace('₦', '');
var loggerCost = $("[id*=tblDetails] tr:last-child").find('td').eq(5).text().replace('₦', '');
var securityCost = $("[id*=tblDetails] tr:last-child").find('td').eq(7).text().replace('₦', '');
$('[id*=Label114]').html(parseInt(nandCost) + parseInt(loggerCost) + parseInt(securityCost));
});
</script>
</form>
</body>
</html>
Screenshot
