A Better/Faster/Smarter Way Please

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)

A Better/Faster/Smarter Way Please

Author

Topic

budski
Starting Member

7 Posts

Posted - 2010-05-06 : 16:30:39

I have written many sql queries over the course of the last several years but I've got one that the answer seems right in front of me but I just can't see it. So any input on the following problem would be greatly appreciated.

I have two tables Managers and Sales


ManagerID   Month      Year     Region    
    1         3        2004     East
    2         4        2004     East
    1         6        2004     East


Sales        Month     Year     Region     ManagerID
3,500         3        2004     East     
1,100         4        2004     East
4,650         5        2004     East
5,989         6        2004     East

The task is to update the ManagerID value in the sales table with the correct value from the Managers table based on region, month and year. The managers table contains a record for each change in manager assignments which means you can't do a simple update based on joining Region, Month and Year because there won't be a record in the managers table for every region and year.

The only solution I came up with is using loops and cursors which does work but these are rather large pools of data (30+ million rows) so I believe cursors and loops aren't a viable alternative.

FYI! This is an old legacy system so changing the schema or things such as that aren't an option

Here's hoping some generous person out there can give me a couple of hints

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-06 : 16:37:44

What should be the ManagerID for this row?

4,650         5        2004     East

budski
Starting Member

7 Posts

Posted - 2010-05-06 : 16:42:35

2
since the last transaction in the Managers table was for April

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-06 : 22:02:44

Try this:


UPDATE Sales
   SET ManagerID = (SELECT TOP(1) M.ManagerID
                      FROM Managers AS M
                     WHERE M.Region = Sales.Region
                       AND M.Year <= Sales.Year
                       AND M.Month <= Sales.Month
                     ORDER BY M.Year DESC, M.Month DESC);

budski
Starting Member

7 Posts

Posted - 2010-05-07 : 09:22:31

I like it. I really like it. I'll give it a try in a little bit although to me it looks like it will work perfectly. Thank you Thank you Thank you. I really didn't won't to implement loops and cursors for data pools this large. For what it's worth I will give credit in the comments to this forum for resolving the situation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources