SQL statement problem

[fattyfatpants]

I have a SQL statement as follows

'dw_xaraged = Data Warehouse table
'clients = Clients table
'matter = Services for each client
'relationships: client 1..N matter | matter 1..N dw_xaraged
'mmatter = Specific service for each client (ie Tax Return, Financial Statement, etc.)
'arage1, arage2, arage3, arage4, arage5 = 0, 30, 60, 90, 120 days past due on payments and how much is owed in each
'araget = total amount outstanding
'mdesc1 = description of service rendered to client
'clientaddr1 = client name
'morgaty = originating attorney for the client

SELECT dw_xaraged.mmatter, arage1, arage2, arage3, arage4, arage5, araget, mdesc1, clientaddr1, morgaty
FROM dw_xaraged INNER JOIN (clients INNER JOIN matter ON clients.clnum = matter.mclient) ON dw_xaraged.mmatter = matter.mmatter
WHERE peendt = '9/30/2004'
AND (arage4 + arage5 >= 1000)
ORDER BY morgaty, crelated, dw_xaraged.mmatter

for some reason this omits a lot of results, however if I leave out the AND (arage4 + arage5 >= 1000) it brings in all of the results I am looking for plus more...Why?? I hope I am making sense...Thanks ahead of time.

 

[donutman]

Well, the purpose of a WHERE clause is to filter out some of the rows. When you leave that AND clause in, you are eliminating all row data that has AR amounts in excess of $1,000 that are in the 90 or 120 day categories.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[fattyfatpants]

shouldn't this statement return only records in the 9/30/2004 period that have balances outstanding for greater than 90 days?? (I don't mean to ask it like a question since I am the one who wrote it, I mean it as an 'are you sure?' kind of thing) The really wierd thing about this is that when I run it in Crystal it returns records from previous periods when it shouldn't since i've specified that I only want the records in the 9/30/2004 period...could I write this statement better??

 

[donutman]

Maybe peendt doesn't contain the datetime data that you expect. I have no way of knowing...sorry.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

It probably is a data problem of some type or a failure to properly define what you want logically.

This is what I would do. Run it with and without the final where clause and identify the records that you get in the second version that you want inthe first version. then lok at them incdividually and see if you can see where they are differnt from the records which are correctly returned.

If you still can;t see the problem maybe you should post a data sample here of the base data, the data returned by the fisrt query and the data you would like returned by the query that is not being returned adn then maybe one of us can spot the problem.

Questions about posting. See faq183-874

 

[fattyfatpants]

I would like to thank both of you for helping me out on this...it seems that the query was working after all it's just that since I am trying to run Aged Accounts Receivable over a 1 month period that I would have a lot more outstanding than just the 3 records that were returned...I went and talked with the Controller and she said that I am not crazy (first person to tell me that) and the data was correct...So thanks again, I'm sure I'll be talking with you again soon.