February 20, 2018 at 7:55 am
I have a query that gives a total count and then there is a sub query that diplays whether the records contains the word "Note" and displays the text accordingly . I was wondering if there is a way to optimize this query into one, since I'm calling the same table record twice, or do I need it keep it separate because I don't want to group those two columns?
Select Count(rowID) as XmlEventsCount,
(Select case when exists (Select 11 from TicketEvents x where x.Status='NOTE' and x.RowID=1136394)
THEN 'Yes' Else 'NO' END) SentToProvider
From TicketEvents x
Where x.RowID=1136394
Please advise. Just trying to see if the above query can be consolidated or if there is a better way to write this query.
Thanks
February 20, 2018 at 8:11 am
Please will you post some table DDL, sample data and expected results? Your query will return the same row count in every row, which I suspect isn't what you want.
John
February 20, 2018 at 10:24 am
When doing a subquery in the SELECT clause or as part of a CROSS APPLY, you typically want it to be correlated to the outer query. Using the same alias for the outer query table and subquery table confuses the issue also, they should be different. As John mentioned, we can't really help you much with this without knowing the table definition, indexes, sample data, and expected result.
February 20, 2018 at 10:45 am
Select Count(*) as XmlEventsCount,
Max(case when x.Status = 'NOTE' THEN 'Yes' ELSE 'No' END) as SentToProvider
From TicketEvents x
Where x.RowID=1136394
Group By x.RowID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply