Combine queries - 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 2005 Forums

Transact-SQL (2005)

Combine queries

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2010-08-30 : 14:12:36

Hi all,
here in the forum I've found a solution for the first part of my question and it works very well. But my problem is now: I have 2 same queries and I guess I could combine them into 1 so the script will be faster. Would this be possible?


UPDATE MyTable
SET CurrYear = 
		(SELECT Min(CurrYear) as CurrYear From MyTable as MSearch
			WHERE (MSearch.myID = M.MyID AND MSearch.MyDate > M.MyDate And MSearch.Type = 1) ,
CurrMonth = 
		(SELECT Min(CurrMonth) as CurrMonth From MyTable as MSearch
			WHERE (MSearch.myID = M.MyID AND MSearch.MyDate > M.MyDate And MSearch.Type = 1) 
FROM MyTable as M Where M.Type = 3 AND M.CurrMonth Is NULL

As you see in bold, the 2 queries are the same. I've tried several combinations to combine these 2 selects into 1 but was not successfull.
Many thanks in advance!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-30 : 14:47:03

Give it a try:

update m
set CurrYear = dt.CurrYear, CurrMonth = dt.CurrMonth
from MyTable as m
join
(
select CurrYear, CurrMonth, myID,
row_number() over (partiton by myID order by MyDate DESC) as rnum
from MyTable
where [Type] = 1
) as dt
on m.myID = dt.myID and dt.rnum=1 and m.Type=3 and m.CurrMonth is null

edit: changed ASC to DESC

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-30 : 14:48:30

It's not clear what you're trying to do, but this will get you pointed in the right direction

Jim


UPDATE mt 
SET 
   CurrYear  =  a.CurrYear
  ,CurrMonth =  a.Currmonth

FROM
   MyTable Mt
INNER JOIN
		(select myID,Min(CurrYear) as CurrYear,Min(CurrMonth) as CurrMonth
         from MyTable    
	     where  Type = 1)
         group by  myID,myDate   
        ) a 
ON
    mt.myid =  a.id
WHERE 
mt.Type = 3 AND mt.CurrMonth Is NULL

Everyday I learn something that somebody else already knew

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2010-08-30 : 15:19:33

Thanks jimf and webfred, I'll play around with it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources