Row offset from JOIN condition

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

General SQL Server Forums

New to SQL Server Programming

Row offset from JOIN condition

Author

Topic

raulcleary
Starting Member

14 Posts

Posted - 2008-09-01 : 18:55:59

I'm trying to use a cumulative result from one table where the date is one row before the row matching on the JOIN's a.date=b.date. Not all dates are present, so I can't assume the previous day will exist. I figure I need to use an offset on b.date in the formula (e.g. b.date[-1] or something). Here's the query as it is, which works except using the current date's row in TGlobalHistory instead of the date before.

INSERT INTO TNaiveError(date, error) 
SELECT TRatings.date, 
       SUM(POW(TRatings.rating-TGlobalHistory.csum/TGlobalHistory.ccount,2))/COUNT(TRatings.rating) 
FROM TRatings 
JOIN TGlobalHistory 
     ON TGlobalHistory.date = TRatings.date 
GROUP BY TRatings.date;

Thanks!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-02 : 02:42:02

SQL 2000 or SQL 2005?

--
Gail Shaw
SQL Server MVP

raulcleary
Starting Member

14 Posts

Posted - 2008-09-02 : 03:53:05

quote:
Originally posted by GilaMonster

SQL 2000 or SQL 2005?

Not sure -- I'm not seasoned. I hope this barf will answer:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| protocol_version        | 10                                                      | 
| version                 | 5.0.45                                                  | 
| version_bdb             | Sleepycat Software: Berkeley DB 4.1.24: (July  4, 2007) | 
| version_comment         | Source distribution                                     | 
| version_compile_machine | i386                                                    | 
| version_compile_os      | redhat-linux-gnu                                        | 
+-------------------------+---------------------------------------------------------+

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 03:55:13

its mysql. so you will get accurate results if you post this in some mysql forums. this forum is for MS SQL Server.

raulcleary
Starting Member

14 Posts

Posted - 2008-09-03 : 01:42:09

I'll still hold out for someone useful who can wade through the vast differences in syntax between MySQL versus SQL Server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources