SQL is only working for one month

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

SQL is only working for one month

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2010-09-01 : 16:12:28

This app is powered by an SQL as you can see, the total 'Usage to date' for the 2 months is off, it does work perfectly for one month.In this example the usage to date on the summary line is 4,086.48, but on the detail part you can see a total of 15,220.78

http://img34.imageshack.us/img34/2063/brokengrid.png

The SQL is here:

http://pastebin.com/yQ7NrxwN

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-02 : 02:46:02

I'm not spending half my day trying to debug this but at first glance I think there is something fishy about this part:

(
  SOP10100.DOCDATE >= @fromdate AND 
  SOP10100.DOCDATE < @todate
OR      
  SOP10100.DOCDATE >= @CurrMonthStart AND 
  SOP10100.DOCDATE < DATEADD(MONTH, 1, @CurrMonthStart)
)

You need brackets around any expression that involves OR, if you don't the the logic in the expression will be hard to track. Change it to this:

(
  (SOP10100.DOCDATE >= @fromdate AND SOP10100.DOCDATE < @todate)
OR      
  (SOP10100.DOCDATE >= @CurrMonthStart AND SOP10100.DOCDATE < DATEADD(MONTH, 1, @CurrMonthStart))
)

and maybe you'll be better off.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources