[Solved] SQL Query Error: Incorrect syntax near the keyword AS in ASP.Net

lingers
 
on Oct 28, 2021 05:48 AM
1894 Views

 How to sum a column to existing sql query

SqlCommand scz = new SqlCommand(" SELECT  Datename(dw, Time1)+', '+CONVERT(VARCHAR(12), Time1, 107) as Query_Date, (SELECT COUNT(Exact*) FROM MealLog as c WHERE c.Time1= clog.Time1 AND Company='NAND') AS NAND,N_MCOST=SUM(CASE WHEN Company='NAND' THEN 1 ELSE 0 END)* MAX(CASE WHEN Company='NAND' THEN Value ELSE 0 END) FROM MealLog AS clog WHERE Time1  >= '" + (Session["D"].ToString()) + "' and Time1 <= '" + (Session["D1"].ToString()) + "'  GROUP BY Time1 ORDER BY Time1 ASC   ", dbConn);
 

 

Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.

 

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 (1, N'Halland', N'67891', N'2X', N'ENGINEERING', N'LOG', 10000327, 300, CAST(N'2018-06-24T16:20:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2, N'Halland', N'67892', N'2X', N'ENGINEERING', N'LOG', 10000328, 300, CAST(N'2018-06-24T16:20:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (3, N'Halland', N'67893', N'2X', N'ENGINEERING', N'LOG', 10000329, 300, CAST(N'2018-06-24T16:20:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (4, N'Halland', N'67894', N'2X', N'ENGINEERING', N'ARREY', 10000330, 500, CAST(N'2018-06-24T16:21:00.000' AS DateTime), CAST(N'2020-12-02' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (5, N'Halland', N'67895', N'2X', N'ENGINEERING', N'KEEGEE', 10000331, 500, CAST(N'2018-06-24T16:21:00.000' AS DateTime), CAST(N'2020-12-03' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (6, N'Halland', N'67896', N'2X', N'ENGINEERING', N'NAND', 10000332, 600, CAST(N'2020-12-03T16:21:00.000' AS DateTime), CAST(N'2020-12-03' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1002, N'Halland', N'67897', N'2X', N'ENGINEERING', N'NAND', 10000333, 600, CAST(N'2018-06-25T18:02:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1004, N'Tyrese Test', N'12348', N'X', N'PRODUCTION', N'NAND', 10000334, 600, CAST(N'2020-12-07T00:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1005, N'Tyrese Test', N'12349', N'X', N'PRODUCTION', N'NAND', 10000335, 600, CAST(N'2020-12-07T00:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1006, N'Tyrese Test', N'12341', N'X', N'PRODUCTION', N'NAND', 10000336, 600, CAST(N'2020-12-07T00:25:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1007, N'Tyrese Test', N'12342', N'X', N'PRODUCTION', N'NAND', 10000337, 600, CAST(N'2020-12-07T00:27:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1008, N'Tyrese Test', N'12343', N'X', N'PRODUCTION', N'NAND', 10000338, 600, CAST(N'2020-12-07T00:30:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1009, N'Tyrese Test', N'12344', N'X', N'PRODUCTION', N'NAND', 10000339, 600, CAST(N'2020-12-07T00:30:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1010, N'Tyrese Test', N'12345', N'X', N'PRODUCTION', N'NAND', 10000340, 600, CAST(N'2020-12-07T00:31:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1011, N'Tyrese Test', N'12346', N'X', N'PRODUCTION', N'NAND', 10000341, 600, CAST(N'2020-12-07T00:32:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1012, N'Tyrese Test', N'12347', N'X', N'PRODUCTION', N'NAND', 10000342, 600, CAST(N'2020-12-07T00:36:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1013, N'Tyrese Test', N'12348', N'X', N'PRODUCTION', N'NAND', 10000343, 600, CAST(N'2020-12-07T00:39:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1014, N'Tyrese Test', N'12349', N'X', N'PRODUCTION', N'NAND', 10000344, 600, CAST(N'2020-12-07T00:42:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2004, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000345, 600, CAST(N'2020-12-07T13:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2005, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000346, 600, CAST(N'2020-12-07T13:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2006, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000347, 600, CAST(N'2020-12-07T13:24:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2007, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000348, 600, CAST(N'2020-12-07T13:28:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2008, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000349, 600, CAST(N'2020-12-07T13:56:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2009, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000350, 600, CAST(N'2020-12-07T13:57:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2010, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000351, 600, CAST(N'2020-12-07T13:59:00.000' AS DateTime), CAST(N'2021-10-29' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2011, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000352, 600, CAST(N'2020-12-07T14:14:00.000' AS DateTime), CAST(N'2021-10-30' AS Date), 1)

SET IDENTITY_INSERT [dbo].[MealLog] OFF
GO

PLEASE  HELP

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 09, 2021 09:54 AM

Refer the query.

SELECT DATENAME(dw, clog.Time1)+', '+CONVERT(VARCHAR(12), clog.Time1, 107) as Query_Date,
COUNT(Exact) AS NAND,
Exact = SUM(Exact),
N_MCOST = SUM(Exact) * MAX(Value)
FROM MealLog clog
WHERE clog.Company = 'NAND'
GROUP BY clog.Time1
ORDER BY clog.Time1 ASC

Output

Query_Date NAND Exact N_MCOST
Thursday, Dec 03, 2020 1 1 600
Friday, Dec 04, 2020 1 1 600
Monday, Dec 07, 2020 17 17 10200
Friday, Oct 29, 2021 1 1 600
Saturday, Oct 30, 2021 1 1 600