I have this report that sums up all the ticket used per day. Now the challenge is that it does not sum up all the exact per day instead it picks only one of the exact column for that day which is not what i want.
That is from my table i have 1+1+1 = 3 exact for 2021-12 -16 but it is just showing result for 1.
CREATE TABLE [dbo].[MealLog](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[Emp_No] [nvarchar](250) NOT NULL,
[Meal_Cat] [nvarchar](250) NOT NULL,
[Department] [nvarchar](250) NOT NULL,
[Company] [nvarchar](250) NOT NULL,
[Reciept_No] [int] NOT NULL,
[Value] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[Time1] [date] NOT NULL,
[Exact] [int] NULL,
CONSTRAINT [PK_MealLog] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[MealLog] ON
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1123, N' SETH', N'NAND/181', N'X', N'PRODUCTION', N'NAND', 10000002, 600, CAST(N'2018-07-06T09:49:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1124, N'DANIEL', N'NAND/228', N'X', N'PRODUCTION', N'NAND', 10000003, 600, CAST(N'2018-07-06T09:53:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1125, N'JIM ', N'NAND/164', N'X', N'PRODUCTION', N'NAND', 10000004, 600, CAST(N'2018-07-06T09:55:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1126, N'OLA', N'LOG/006', N'X', N'PRODUCTION', N'LOGGER', 10000005, 500, CAST(N'2018-07-06T10:01:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1127, N' Samson ', N'LOG/162', N'2X', N'QUALITY ASSURANCE', N'LOGGER', 10000006, 500, CAST(N'2018-07-06T10:02:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1128, N'Micheal ', N'LOG/034', N'2X', N'PRODUCTION', N'LOGGER', 10000007, 500, CAST(N'2018-07-06T10:03:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1129, N'Adele', N'SEC/080', N'X', N'PRODUCTION', N'SECURITY', 10000008, 400, CAST(N'2018-07-06T10:04:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1130, N'JOSEPH ', N'SEC/176', N'X', N'PRODUCTION', N'SECURITY', 10000009, 400, CAST(N'2018-07-06T10:05:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1131, N'ISMAIL', N'SEC171', N'2X', N'PRODUCTION', N'SECURITY', 10000010, 400, CAST(N'2018-07-06T10:06:00.000' AS DateTime), CAST(N'2018-12-16' AS Date), 1)
<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>
REPORT FOR ALL WORK TICKET CATEGORY FROM
<asp:Label ID="Label1121" runat="server"></asp:Label>
TO
<asp:Label ID="Label1131" runat="server"></asp:Label>
<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>
<%
SqlCommand scz = new SqlCommand("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 >= ''" + (Session["D"].ToString()) + "'' and Time1 <= ''" + (Session["D1"].ToString()) + "'') t pivot ( MAX(Exact) FOR Company IN ([LOGGER],[NAND],[SECURITY])) piv) SELECT CAST([S/N] AS VARCHAR(10)) [S/N],QUERYDATE,NAND,NANDCOST,LOGGER,LOGGERCOST,SECURITY,SECURITYCOST FROM cte UNION SELECT ''TOTAL'','''',SUM(CAST(NAND AS BIGINT)),SUM(CAST(NANDCOST AS BIGINT)),SUM(CAST(LOGGER AS BIGINT)),SUM(CAST(LOGGERCOST AS BIGINT)),SUM(CAST(SECURITY AS BIGINT)),SUM(CAST(SECURITYCOST AS BIGINT)) FROM cte' EXEC (@DynamicPivotQuery)", dbConn);
scz.CommandType = CommandType.Text;
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>
</form>
</body>
