Alternative for Subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Alternative for Subquery

Author

Topic

Indsqlbeginner
Starting Member

21 Posts

Posted - 2013-12-29 : 03:59:42

Hello Team

Below is the sample query which is being used in productioin.

since we are using sql server 2008,i believe that the below query can be optimized to give the better performance. can you please help me with the optimized one for sql server 2008 as to boost up the query speed.

select distinct
empid,
empfromdate
from tblemployee ,
(
select max(empfromdate) as curentdate,
empid as Empno
from tblemployee where dtFromdate < dateadd(dd,1,'2013-12-28')
group by empid
) as temp
where empid=Empno and empfromdate =curentdate
Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-29 : 12:48:17


SELECT *
from tblemployee e
WHERE dtFromdate < dateadd(dd,1,'2013-12-28')
AND NOT EXISTS (SELECT 1
FROM tblemployee 
WHERE empid = e.empid 
AND empfromdate > e.empfromdate
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-29 : 13:06:18

Isn't this enough?

SELECT		EmpID,
		MAX(EmpFromDate) AS CurrentDate
FROM		dbo.tblEmployee
WHERE		dtFromDate < '20131229'
GROUP BY	EmpID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-30 : 03:01:35

Aren't you confusing my solution with Visakh's name?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Indsqlbeginner
Starting Member

21 Posts

Posted - 2013-12-30 : 03:54:15

quote:
Originally posted by SwePeso

Aren't you confusing my solution with Visakh's name?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Yep.sorry for that.

i also want to fetch around 10 columns in the same table based on the latest date details availabe for employee.that was the reason teh query has been written like that ( kindly refer my query).. can you kindly provide an sql 2008 version equilavelt for the same.Thanks

Indsqlbeginner
Starting Member

21 Posts

Posted - 2013-12-30 : 03:55:53

quote:
Originally posted by visakh16

SELECT *
from tblemployee e
WHERE dtFromdate < dateadd(dd,1,'2013-12-28')
AND NOT EXISTS (SELECT 1
FROM tblemployee
WHERE empid = e.empid
AND empfromdate > e.empfromdate
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi Visakh,

this query returns duplicate records of the employee.can i request you to double check and let me know.Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-30 : 08:45:43

quote:
Originally posted by Indsqlbeginner

can you kindly provide an sql 2008 version equilavelt for the same.Thanks

WITH cteSource(EmpID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, rn)
AS (
	SELECT	EmpID,
		Col1,
		Col2,
		Col4,
		Col5,
		Col6,
		Col7,
		Col8,
		Col9,
		Col10,
		ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY EmpFromDate DESC) AS rn
	FROM	dbo.tblEmployee
	WHERE	dtFromDate < '20131229'
)
SELECT	EmpID,
	Col1,
	Col2,
	Col4,
	Col5,
	Col6,
	Col7,
	Col8,
	Col9,
	Col10
FROM	cteSource
WHERE	rn = 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources