Hi Lingers,
Please refer below sample.
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
WHERE Time1 >= @D and Time1 <= @D1
) 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 />
<br />
<br />
<br />
<br />
REPORT FOR ALL WORK TICKET CATEGORY FROM
<asp:Label ID="Label112" runat="server"></asp:Label>
TO
<asp:TextBox runat="server" ID="txtDateTime" />
<asp:Label ID="Label113" runat="server"></asp:Label>
<br />
<br />
<br />
<br />
<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;
scz.Parameters.AddWithValue("@D", Session["D"].ToString());
scz.Parameters.AddWithValue("@D1", Session["D1"].ToString());
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 />
<br />
<br />
<br />
<br />
<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>
<br />
<br />
<br />
<br />
<br />
</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>
Code
protected void Page_Load(object sender, EventArgs e)
{
Session["D"] = "2021-11-15";
Session["D1"] = "2021-11-16";
}
Screenshot
