Compute function problem - 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)

Compute function problem

Author

Topic

amason
Starting Member

7 Posts

Posted - 2007-07-19 : 14:19:59

Select
Tran_Acct_no
,CONVERT(CHAR(11),tran_set_date,110) as [trans date]
,cash

from [Financial_Transactions]
where
Tran_Set_Date between (GETDATE()-20) and GETDATE()
--and tran_acct_no is not null
--and tran_acct_no not in ('1')

Group By Tran_Acct_No, Tran_Set_Date, Cash

Having Sum(Cash) > 8000

Order By Tran_Acct_No, Tran_Set_Date

Compute SUM(Cash) By Tran_Acct_no

The result should list all rows that result in a total of over 8000, but in doing this, it leaves out some rows. If I lower the amount in the Having Sum(cash) > 8000 it will pick up the additional rows.
I thought that line would deal only with the aggregate total, am I misunderstanding it?

Any help would be greatly appreciated
Andrew

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 14:43:54

Post some sample data and expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

amason
Starting Member

7 Posts

Posted - 2007-07-20 : 11:59:46

current result:

Tran_Acct_no trans date cash
------------------- ----------- ---------------------------------------
XXXXXXXX 07-02-2007 15191.50
XXXXXXXX 07-03-2007 10000.00
XXXXXXXX 07-19-2007 20025.27

sum
---------------------------------------
45216.77

expected result (from looking at the table directly)

Tran_Acct_no trans date cash
------------------- ----------- ---------------------------------------
XXXXXXXX 07-02-2007 15191.50
XXXXXXXX 07-03-2007 10000.00
XXXXXXXX 07-09-2007 3014.36***
XXXXXXXX 07-16-2007 1000.00***
XXXXXXXX 07-19-2007 20025.27

sum
---------------------------------------
45216.77

*** = transactions not being pulled by the query???

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 12:02:25

Post some sample data from the table from which this result is expected.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-20 : 14:30:07

The missing values have do not satisfy the criteria: Having Sum(Cash) > 8000

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-20 : 15:11:42

quote:
Originally posted by amason

current result:

Tran_Acct_no trans date cash
------------------- ----------- ---------------------------------------
XXXXXXXX 07-02-2007 15191.50
XXXXXXXX 07-03-2007 10000.00
XXXXXXXX 07-19-2007 20025.27

sum
---------------------------------------
45216.77

expected result (from looking at the table directly)

Tran_Acct_no trans date cash
------------------- ----------- ---------------------------------------
XXXXXXXX 07-02-2007 15191.50
XXXXXXXX 07-03-2007 10000.00
XXXXXXXX 07-09-2007 3014.36***
XXXXXXXX 07-16-2007 1000.00***
XXXXXXXX 07-19-2007 20025.27

sum
---------------------------------------
45216.77

*** = transactions not being pulled by the query???

Since the two rows you marked have cash of less than 8000, why would you expect to see them?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources