SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Alternative for Subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Indsqlbeginner
Starting Member

21 Posts

Posted - 12/29/2013 :  03:59:42  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/29/2013 :  12:48:17  Show Profile  Reply with Quote


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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/29/2013 :  13:06:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/30/2013 :  03:01:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Aren't you confusing my solution with Visakh's name?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Indsqlbeginner
Starting Member

21 Posts

Posted - 12/30/2013 :  03:54:15  Show Profile  Reply with Quote
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
Go to Top of Page

Indsqlbeginner
Starting Member

21 Posts

Posted - 12/30/2013 :  03:55:53  Show Profile  Reply with Quote
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


Edited by - Indsqlbeginner on 12/30/2013 03:56:27
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/30/2013 :  08:45:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000