February 13, 2014 at 10:40 pm
Hello All Good Evening,
Could you please help me with this query, how can i write this query without sub query, or how can write this query another ways
please help me
select planno, status1, count(*) Counts from
(
select a.ValetNO PlanNo ,
case
when JoinCode in ('00', '01', '02') then 'Actcess'
when JoinCode in ('20', '21', '22', '23','38', '39') then
'Secured'else 'Other' end Status1 ---, COUNT (*)
from dbo.ppt a(NOLOCK) left join dbo.acts b on a.P_ID = b.P_ID and a.ValetNO = b.ValetNO
--group by a.ValetNO
)
a group by planno, status1
order by 2
Thank you in Advance
dhani
February 14, 2014 at 12:59 am
Why is the subquery a problem?
It can be written without it, but probably won't change how the query executes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2014 at 4:08 am
You can use CTE if you dont want to use subquery...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 14, 2014 at 4:00 pm
Please try code below. I didn't have any data to test it first, of course.
SELECT
a.ValetNO AS PlanNo,
cross_apply_1.Status1,
COUNT(*) AS Counts
FROM dbo.ppt a WITH (NOLOCK)
LEFT OUTER JOIN dbo.acts b ON
b.P_ID = a.P_ID AND
b.ValetNO = a.ValetNO
CROSS APPLY (
SELECT
CASE
WHEN b.JoinCode in ('00', '01', '02') THEN 'Actcess'
WHEN b.JoinCode in ('20', '21', '22', '23','38', '39') THEN 'Secured'
ELSE 'Other'
END AS Status1
) AS cross_apply_1
GROUP BY
a.ValetNO,
cross_apply_1.Status1
ORDER BY
Status1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 14, 2014 at 4:38 pm
Hello Thank you for your reply,
just want to know any otherway to do this,
also would like to know effecient way to do it
Thank you
Dhani
February 16, 2014 at 6:33 pm
I have 2 questions:
1. Where is the subquery? I see a derived table.
2. Are you sure you want to use NOLOCK? There's a lot of potential gotchas in there.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply