Query not working.

Amit Mohanty

7y1.1k

--Query 1
DECLARE @StartDate DATE='2019-06-01'
DECLARE @EndDate DATE='2019-06-30'
DECLARE @AllEmpIds NVARCHAR(MAX)='2001,2005,2011,2031,2118,2030,2032'
SELECT TOP 1 ISNULL(SUM(R.fldRowTotal),0) AS totalServiceSaleBasedPrice,R.fldEmployeeId FROM TblTicketsSummary S
JOIN TblTicketsRow R ON S.fldTicketID = R.fldTicketID WHERE S.fldTicketStatus = 'Closed' and S.fldClientID IS NOT NULL
AND CONVERT(VARCHAR(10), S.fldDateClosed, 120) >= CONVERT(VARCHAR(10), @StartDate, 120) AND
CONVERT(VARCHAR(10), S.fldDateClosed, 120) <= CONVERT(VARCHAR(10), @EndDate, 120) AND R.fldPS IN ('S','M') AND
CAST(R.fldEmployeeId AS NVARCHAR(50)) IN (@AllEmpIds) GROUP BY R.fldEmployeeId
ORDER BY totalServiceSaleBasedPrice DESC
--Query 2
SELECT TOP 1 ISNULL(SUM(R.fldRowTotal),0) AS totalServiceSaleBasedPrice,R.fldEmployeeId FROM TblTicketsSummary S
JOIN TblTicketsRow R ON S.fldTicketID = R.fldTicketID WHERE S.fldTicketStatus = 'Closed' and S.fldClientID IS NOT NULL
AND CONVERT(VARCHAR(10), S.fldDateClosed, 120) >= CONVERT(VARCHAR(10), '2019-06-01', 120) AND
CONVERT(VARCHAR(10), S.fldDateClosed, 120) <= CONVERT(VARCHAR(10), '2019-06-30', 120) AND R.fldPS IN('S','M') AND
CAST(R.fldEmployeeId AS NVARCHAR(50)) IN (2001,2005,2011,2031,2118,2030,2032) GROUP BY R.fldEmployeeId
ORDER BY totalServiceSaleBasedPrice DESC

When i run query1 it gives no result where as query2 gives some value.

What is wrong in query1?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar7y ago
You Cast it in between and I had already declared as a varchar datatype in place of date
Amit Mohanty7y ago
You are correct @Jenith. But the query is
DECLARE @StartDate DATE='2019-06-01'
DECLARE @EndDate DATE='2019-06-30'
DECLARE @AllEmpIds NVARCHAR(MAX)='2001,2005,2011,2031,2118,2030,2032'

DECLARE @SQLQRY NVARCHAR(MAX)
SET @SQLQRY='SELECT TOP 1 ISNULL(SUM(R.fldRowTotal),0) AS totalServiceSaleBasedPrice,R.fldEmployeeId FROM TblTicketsSummary S
JOIN TblTicketsRow R ON S.fldTicketID = R.fldTicketID WHERE S.fldTicketStatus = ''Closed'' and S.fldClientID IS NOT NULL
AND CONVERT(VARCHAR(10), S.fldDateClosed, 120) >= CONVERT(VARCHAR(10), '''+CAST(@StartDate AS VARCHAR)+''', 120) AND
CONVERT(VARCHAR(10), S.fldDateClosed, 120) <= CONVERT(VARCHAR(10), '''+CAST(@EndDate AS VARCHAR)+''', 120) AND R.fldPS IN (''S'',''M'') AND
CAST(R.fldEmployeeId AS NVARCHAR(50)) IN ('+@AllEmpIds+') GROUP BY R.fldEmployeeId
ORDER BY totalServiceSaleBasedPrice DESC'

EXEC SP_EXECUTESQL @SQLQRY
Jenith Thakkar7y ago
If you want to pass AllEmpId using variable than you have to convert that query to dynamic sql query and execute it

DECLARE @StartDate VARCHAR(32)='2019-06-01'
DECLARE @EndDate VARCHAR(32)='2019-06-30'
DECLARE @AllEmpIds NVARCHAR(MAX)='2001,2005,2011,2031,2118,2030,2032'
declare @sql nvarchar(Max)
Set @sql='SELECT TOP 1 ISNULL(SUM(R.fldRowTotal),0) AS totalServiceSaleBasedPrice,R.fldEmployeeId FROM TblTicketsSummary S
JOIN TblTicketsRow R ON S.fldTicketID = R.fldTicketID WHERE S.fldTicketStatus = ''Closed'' and S.fldClientID IS NOT NULL
AND CONVERT(VARCHAR(10), S.fldDateClosed, 120) >= CONVERT(VARCHAR(10), '+@StartDate+', 120) AND
CONVERT(VARCHAR(10), S.fldDateClosed, 120) <= CONVERT(VARCHAR(10), '+@EndDate+', 120) AND R.fldPS IN (''S'',''M'') AND
CAST(R.fldEmployeeId AS NVARCHAR(50)) IN ('+@AllEmpIds+') GROUP BY R.fldEmployeeId
ORDER BY totalServiceSaleBasedPrice DESC'

exec sp_executesql @sql

try this.....
Amit Mohanty7y ago
Got my solutions. issue in in convertion of

CAST(R.fldEmployeeId AS NVARCHAR(50)) IN (@AllEmpIds)
Amit Mohanty7y ago
Thanx for replying @Faisal

But there is no issue with date conversion. Its working fine. Bcoz in the below query its working.
SELECT ISNULL(SUM(R.fldRowTotal),0.00) as totalGiftCardSaleBasedPrice FROM TblTicketsSummary S JOIN TblTicketsRow R
ON S.fldTicketID = R.fldTicketID WHERE S.fldClientID IS NOT NULL AND
CONVERT(VARCHAR(10), S.fldDateClosed, 120) >= CONVERT(VARCHAR(10), @StartDate, 120) and
CONVERT(VARCHAR(10), S.fldDateClosed, 120) <= CONVERT(VARCHAR(10), @EndDate, 120)
Faisal Pathan7y ago
I think issue is about date conversion " CONVERT(VARCHAR(10), @StartDate, 120)", please print and compare date