TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Amit Mohanty
15
51.1k
5.8m
Query not working.
Jul 11 2019 2:26 AM
--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?
Attachment:
SqlScript.zip
Reply
Answers (
6
)
Income, Expense Query in SQL Server.
How to use Cloud migration in sql server