Sum from many to one. - 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 2000 Forums

Transact-SQL (2000)

Sum from many to one.

Author

Topic

danyeung
Posting Yak Master

102 Posts

Posted - 2006-10-31 : 00:03:33

I need to calculate the balance for the request that has not allocation fund for the fiscal year 2007. The following SQL returns no record if it includes "and AllocationFund.FiscalYear = 2007". But if it is without "and AllocationFund.FiscalYear = 2007", it sums the allocation funds from prior years. Can you please help me on the SQL statment.

select Request.CustomerID, Distributor.FullName,
Request.AllocTypeID,
sum(AllocationFund.intAmount) as Allocation,
sum(Request.Amount) as sum_Amount,
sum(AllocationFund.intAmount) - sum(Request.Amount) as Balance
from Request inner join Distributor
on Request.CustomerID = Distributor.CustomerID
left outer join AllocationFund
on Request.CustomerID = AllocationFund.CustomerID
and Request.AllocTypeID = AllocationFund.AllocTypeID
where Request.SubmittedDate >= '10/01/2006'
and Request.SubmittedDate <= '09/30/2007'
and bitCancel = 0 and Request.AllocTypeID = 1
and Request.CustomerID = '163395'
and AllocationFund.FiscalYear = 2007

Thanks.
DanYeung

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 00:07:45

[code]select Request.CustomerID,
Distributor.FullName,
Request.AllocTypeID,
sum(AllocationFund.intAmount) as Allocation,
sum(Request.Amount) as sum_Amount,
sum(AllocationFund.intAmount) - sum(Request.Amount) as Balance
from Request
inner join Distributor on Request.CustomerID = Distributor.CustomerID
left join AllocationFund on Request.CustomerID = AllocationFund.CustomerID
and Request.AllocTypeID = AllocationFund.AllocTypeID
and AllocationFund.FiscalYear = 2007
where Request.SubmittedDate >= '10/01/2006'
and Request.SubmittedDate <= '09/30/2007'
and bitCancel = 0
and Request.AllocTypeID = 1
and Request.CustomerID = '163395'[/code]

Peter Larsson
Helsingborg, Sweden

danyeung
Posting Yak Master

102 Posts

Posted - 2006-10-31 : 00:14:05

Thank you. I thought the join ... on is for join between two tables only.

DanYeung

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 00:43:38

This is powerful when using LEFT JOIN.

Peter Larsson
Helsingborg, Sweden

danyeung
Posting Yak Master

102 Posts

Posted - 2006-11-02 : 13:34:03

I need more help on the SQL. There is always one sum of fun per AllocTypeID in AllocationFund table. If there are multiple rows in Request table, the sum of fun in AllocationFund table is sum multiple times. For example:

The fund for AllocTypeID is $5000
There are three requests for this AllocTypeID: $1000, $1200, and $1500.

The result from the SQL statement is
CustomerID: 123
CustomerName: ABC
AllocTypeID: 1
Allocation: 15000 (5000 * 3) should be 5000
sum_Amount: 3700
Balance: 11300 should be 1300

Can you please help with the SQL? Thanks.

DanYeung

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources