t-sql 2012 change query

Question

Post reply

t-sql 2012 change query

wendy elizabeth

SSCoach

Points: 16915
More actions
April 27, 2016 at 3:54 pm

#316093

In a new t-sql 2012 query, I got the correct results to display by using several temp tables. However I am wondering if there is another way to come up with the same results and not use temp tables.
Here is the existing t-sql 2012 query:
Declare @personID int = 123999
SELECT t.testID ,t.name ,t.code ,ts.personID ,ts.date ,ts.scaleScore ,ts.result
INTO #ELDASummary
FROM Test t
join TestScore ts
on t.testID = ts.testID
and t.code = 'XXX2'
where personID = @personID
order by t.testID
select Test.parentID,Test.testID,Test.name
INTO #ELDACrossReference
FROM Test
join #ELDASummary on Test.parentID = #EldaSummary.testID
select #ELDACrossReference.parentID, #ELDACrossReference.testID,TestScore.personID,#ELDACrossReference.name ,TestScore.scaleScore,TestScore.result
into #ELDADetail
from TestScore TestScore
join #ELDACrossReference on #ELDACrossReference.testID = TestScore.testID
where personID = @personID
order by parentID,testID
select #ELDASummary.personID as personID,#ELDADetail.parentID as ELDADetailparentID, #ELDADetail.testID as ELDADetailtestID,#ELDASummary.name as ELDASummaryname
,#ELDASummary.Code as ELDASummaryCode,#ELDASummary.date as ELDASummarydate ,#ELDASummary.scaleScore as ELDASummaryscaleScore
,#ELDASummary.result as ELDASummaryresult,#ELDADetail.name as ELDADetailname
,#ELDADetail.scalescore as ELDADetailscalescore,#ELDADetail.result as ELDADetailresult
from #ELDASummary
join #ELDADetail on #ELDADetail.parentID = #ELDASummary.testid
order by personID,ELDADetailparentID, ELDADetailtestID
drop table #ELDASummary
drop table #ELDACrossReference
drop table #ELDADetail
Thus could you tell me how to rewrite this query using a cte, using several joins, subquery, and/or another way to accomplish the same result?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63594 More actions · Answer 1

Got table definitions? Sample data? (Create table and insert scripts)... can be completely fake, just representative.

Lowell SSC Guru Points: 323546 More actions · Answer 2

CTE's would work, as well as subqueries(same thing,really , just different format.

here's my version of your post as CTE's:

DECLARE @personID INT = 123999;

WITH ELDASummary

AS (SELECT t.testID,

t.NAME,

t.code,

ts.personID,

ts.date,

ts.scaleScore,

ts.result

FROM Test t

JOIN TestScore ts

ON t.testID = ts.testID

AND t.code = 'XXX2'

WHERE personID = @personID),

ELDACrossReference

AS (SELECT Test.parentID,

Test.testID,

Test.NAME

FROM Test

JOIN ELDASummary

ON Test.parentID = EldaSummary.testID),

ELDADetail

AS (SELECT ELDACrossReference.parentID,

ELDACrossReference.testID,

TestScore.personID,

ELDACrossReference.NAME,

TestScore.scaleScore,

TestScore.result

FROM TestScore TestScore

JOIN ELDACrossReference

ON ELDACrossReference.testID = TestScore.testID

WHERE personID = @personID)

SELECT ELDASummary.personID AS personID,

ELDADetail.parentID AS ELDADetailparentID,

ELDADetail.testID AS ELDADetailtestID,

ELDASummary.NAME AS ELDASummaryname,

ELDASummary.Code AS ELDASummaryCode,

ELDASummary.date AS ELDASummarydate,

ELDASummary.scaleScore AS ELDASummaryscaleScore,

ELDASummary.result AS ELDASummaryresult,

ELDADetail.NAME AS ELDADetailname,

ELDADetail.scalescore AS ELDADetailscalescore,

ELDADetail.result AS ELDADetailresult

FROM ELDASummary

JOIN ELDADetail

ON ELDADetail.parentID = ELDASummary.testid

ORDER BY personID,

ELDADetailparentID,

ELDADetailtestID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

wendy elizabeth SSCoach Points: 16915 More actions · Answer 3

What format is the most efficient for executing the best? subqueries, temp tables, or cte?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

wendy elizabeth (4/28/2016)
What format is the most efficient for executing the best? subqueries, temp tables, or cte?

It depends. The best you can do is test the different options and see which works best regarding speed, scalability, use of resources, etc.

Once you've done the testing, test some more.

Alan Burstein SSC Guru Points: 61152 More actions · Answer 5

Those ORDER BY clauses in your SELECT INTO queries aren't doing you any favors.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001