Hi Linger,
Please refer Below code.
SQL
StoredProcedure
CREATE PROCEDURE [Total_MealLog]
@Start VARCHAR(10),
@End VARCHAR(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 'S/N',*
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 = 'MOBOL') MOBOL,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOBOL')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOBOL') MOBOLCOST
FROM MealLog ml
WHERE Time1 >= @Start and Time1 <= @End
GROUP BY Time1, Company, Exact
)t
END
Code
<div>
<table align="center" border="1px" style="width: 1111px">
<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
MEAL TICKET CATEGORY FROM
<asp:Label ID="Label1121" runat="server"></asp:Label>
TO
<asp:Label ID="Label1131" runat="server"></asp:Label>
<br />
<br />
<br />
<br />
<br />
<table id='tblDetails' border='1' style="width: 1350px; font-family: Cambria; font-size: 15px; font-size: small; font-weight: normal;">
<tr>
<td class="style8" style="font-size: x-small">S/N</td>
<td class="style9" style="font-size: x-small">QUERYDATE</td>
<td class="style19" style="font-size: x-small">ARPN</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
<td style="font-size: x-small">
<b>ARPN BULK</td>
<td style="font-size: x-small" class="style21"><strong>ARPNBULKCOST</strong>
</b>
</td>
<td class="style19" style="font-size: x-small">AGY</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
<td class="style19" style="font-size: x-small">CRMT</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
<td class="style19" style="font-size: x-small">DLG</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
<td style="font-size: x-small"><b>DELOG BULK
</b>
</td>
<td style="font-size: x-small"><strong>DELOGBULKCOS</strong>T
</td>
<td class="style19" style="font-size: x-small">RDLG</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
<td class="style19" style="font-size: x-small">TUSKG</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
<td class="style20" style="font-size: x-small">TUSKG BULK</td>
<td style="font-size: x-small"><b>TUSKGBULKCOST
</b>
</td>
<td class="style19" style="font-size: x-small">VIST</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
<td class="style19" style="font-size: x-small">MOPOL</td>
<td class="style20" style="font-size: x-small"><b>COST </b> </td>
</tr>
<%
SqlCommand scz = new SqlCommand("Total_MealLog", dbConn);
scz.CommandType = CommandType.StoredProcedure;
scz.Parameters.AddWithValue("@Start", Session["D"]);
scz.Parameters.AddWithValue("@Start", Session["D1"]);
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["ARPN"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["ARPNCOST"].ToString() + "</td> " +
"<td class='style5'>" + sdz["ARPNBULK"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["ARPNBULKCOST"].ToString() + "</td> " +
"<td class='style5'>" + sdz["AGGREY"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["AGGREYCOST"].ToString() + "</td>" +
"<td class='style5'>" + sdz["CRIMETECH"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["CRIMETECHCOST"].ToString() + "</td>" +
"<td class='style5'>" + sdz["DELOG"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["DELOGCOST"].ToString() + "</td> " +
"<td class='style5'>" + sdz["DELOGBULK"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["DELOGBULKCOST"].ToString() + "</td> " +
"<td class='style5'>" + sdz["RDELOG"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["RDELOGCOST"].ToString() + "</td>" +
"<td class='style5'>" + sdz["TUSKEEGEE"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["TUSKEEGEECOST"].ToString() + "</td>" +
"<td class='style5'>" + sdz["TUSKEEGEEBULK"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["TUSKEEGEEBULKCOST"].ToString() + "</td>" +
"<td class='style5'>" + sdz["VISITOR"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["VISITORCOST"].ToString() + "</td>" +
"<td class='style5'>" + sdz["MOPOL"].ToString() + "</td>" +
"<td class='style5'>" + "₦" + sdz["MOPOLCOST"].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["ARPN"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label1'>" + "₦" + sdz["ARPNCOST"].ToString() + "</span></td>" +
"<td class='style6'><span style='font-weight:bold;color:#3366CC;' id='Label200'>" + sdz["ARPNBULK"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label201'>" + "₦" + sdz["ARPNBULKCOST"].ToString() + "</span></td>" +
"<td class='style13'><span style='font-weight:bold;color:#3366CC;' id='Label3'>" + sdz["AGGREY"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label4'>" + "₦" + sdz["AGGREYCOST"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label7'>" + sdz["CRIMETECH"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label8'>" + "₦" + sdz["CRIMETECHCOST"].ToString() + "</span></td>" +
"<td class='style6'><span style='font-weight:bold;color:#3366CC;' id='Label9'>" + sdz["DELOG"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label10'>" + "₦" + sdz["DELOGCOST"].ToString() + "</span></td>" +
"<td class='style6'><span style='font-weight:bold;color:#3366CC;' id='Label202'>" + sdz["DELOGBULK"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label203'>" + "₦" + sdz["DELOGBULKCOST"].ToString() + "</span></td>" +
"<td class='style13'><span style='font-weight:bold;color:#3366CC;' id='Label11'>" + sdz["RDELOG"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label12'>" + "₦" + sdz["RDELOGCOST"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label13'>" + sdz["TUSKEEGEE"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label14'>" + "₦" + sdz["TUSKEEGEECOST"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label204'>" + sdz["TUSKEEGEEBULK"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label205'>" + "₦" + sdz["TUSKEEGEEBULKCOST"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label15'>" + sdz["VISITOR"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label16'>" + "₦" + sdz["VISITORCOST"].ToString() + "</span></td>" +
"<td class='style22'><span style='font-weight:bold;color:#3366CC;' id='Label206'>" + sdz["MOPOL"].ToString() + "</span></td>" +
"<td class='style21'><span style='font-weight:bold;color:#3366CC;' id='Label207'>" + "₦" + sdz["MOPOLCOST"].ToString() + "</span></td>" +
"</tr>");
}
}
dbConn.Close();%>
</table>
<asp:Label ID="LabelF" runat="server" ForeColor="#3366CC"></asp:Label>
<br />
<br />
<br />
<br />
<br />
<table border="1" style="width: 100%;">
<tr>
<td class="style16">SUB TOTAL FOR ARPN+AGGREY+RDELOG</td>
<td class="style15">₦<asp:Label ID="Label114" runat="server" Font-Bold="True" Font-Size="Large"></asp:Label>
</td>
</tr>
</table>
<br />
<br />
<table border="1" style="width: 100%;">
<tr>
<td class="style16">GRAND TOTAL </td>
<td class="style15">₦<asp:Label ID="Label60" 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 ARPNCost = $("[id*=tblDetails] tr:last-child").find('td').eq(3).text().replace('₦', '');
var ARPNBULKCost = $("[id*=tblDetails] tr:last-child").find('td').eq(5).text().replace('₦', '');
var AGGREYCost = $("[id*=tblDetails] tr:last-child").find('td').eq(7).text().replace('₦', '');
var CRIMETECHCost = $("[id*=tblDetails] tr:last-child").find('td').eq(9).text().replace('₦', '');
var DELOGCost = $("[id*=tblDetails] tr:last-child").find('td').eq(11).text().replace('₦', '');
var DELOGBULKCost = $("[id*=tblDetails] tr:last-child").find('td').eq(13).text().replace('₦', '');
var RDELOGCost = $("[id*=tblDetails] tr:last-child").find('td').eq(15).text().replace('₦', '');
var TUSKEEGEECost = $("[id*=tblDetails] tr:last-child").find('td').eq(17).text().replace('₦', '');
var TUSKEEGEEBULKCost = $("[id*=tblDetails] tr:last-child").find('td').eq(19).text().replace('₦', '');
var VISITORCost = $("[id*=tblDetails] tr:last-child").find('td').eq(21).text().replace('₦', '');
var MOPOLCost = $("[id*=tblDetails] tr:last-child").find('td').eq(23).text().replace('₦', '');
$('[id*=Label60]').html(parseInt(ARPNCost) + parseInt(ARPNBULKCost) + parseInt(AGGREYCost) + parseInt(CRIMETECHCost) + parseInt(DELOGCost) + parseInt(DELOGBULKCost) + parseInt(RDELOGCost) + parseInt(TUSKEEGEECost) + parseInt(TUSKEEGEEBULKCost) + parseInt(VISITORCost) + parseInt(MOPOLCost));
$('[id*=Label114]').html(parseInt(ARPNCost) + parseInt(ARPNBULKCost) + parseInt(DELOGCost) + parseInt(DELOGBULKCost) + parseInt(RDELOGCost));
});
</script>
