SUM
-
I am trying to create a stored procedure that will give me the results like so:
TransactionType
SaleAmount
PaymentAmount
Balance = Sum of SaleAmount - sum of PaymentAmount
This is how I have done the stored procedure so far. When I add the TransactionType, SaleAmount, and PaymentAmount to the statement then it doesn't do the math right or tells me I need to group them.
Select Sum(SaleAmount) - Sum(PaymentAmount) AS Balance FROM dbo.Transactions Where UserID = @ClientID
-
Select TransactionType, Sum(SaleAmount) as SaleAmount, Sum(PaymentAmount) as PaymentAmount, Sum(SaleAmount - PaymentAmount) AS Balance FROM dbo.Transactions Where UserID = @ClientID
group by TransactionType
-
Thank you for replying but I am still not getting the right results.
the values in the SaleAmount and PaymentAmount are in different rows
-
Can you give the output from Remi's code, because that seems very odd. Maybe include UserID; remember to use that in the GROUP BY as well...
I wasn't born stupid - I had to study.
-
This is with the UserID in the mix
UserID TransactionType SaleAmount1 PaymentAmount1 Balance
----------- -------------------------------------------------- ----------- -------------- ----------
209541 Executive 3 Year 6200
209541 Exeuctive 1 Year 10400
209541 Payment 10800
-
Can you post the definition of the tables with some sample data for each one also with the expected results of the query? We're only gonna do guess work without it.
-
Here are the columns:
UserID, TransactionType, SaleAmount, PaymentAmount, PaymentType
UserID TransactionType SaleAmount PaymentAmount PaymentType
209541 Executive 3 Year 6200
209541 3000 Visa
209541 <null 3200 Amex
-
What are the expected results from the query with this data?
-
I need to show each transaction and then the balance on the account. Something like a receipt.
I am going to use a datalist on my aspx page to show my results
Transaction Sale Payment
Executive 3 year 6200
Visa 3000
Balance 3200
-
This is a basic task but I just don't see a solution with the data you are presenting me...
Can you post the script to create all the tables you need in this query along with data for each table? Or if it's the same table, can you point me to the flag that tells weather the line is a payment or a bill?
-
It is all one table. I was just going to order them by TransactionDate because the sale will always appear first.
TransactionType determines if it is a sale or payment
-
Again more demo data would have been great because I'm still guessing here :
this assumes that you have only a single amount column and also assumes that you have some sort of orderid column
Select UserID, SUM(CASE when TransactionType = 'Sale' THEN AmountColumn ELSE AmountColumn * -1 END) as Balance, OrderId
from dbo.YourTable
group by UserId, OrderId
this assumes that you have one column for salesamount and another for paymentamount (still with orderid)
Select userId, OrderId, sum(SaleAmount - PaymentAmount) as Balance from dbo.YourTable
group by UserId, OrderId
-
Can you fill in the blanks ?
UserID TransactionType SaleAmount PaymentAmount PaymentType
209541 Executive 3 Year 6200
209541 3000 Visa
209541 3200 Amex
-------|----------------|-----------|--------------|------------|
UserID |TransactionType |SaleAmount |PaymentAmount |PaymentType |
-------|----------------|-----------|--------------|------------|
209541 | | | | |
-------|----------------|-----------|--------------|------------|
209541 | | | | |
-------|----------------|-----------|--------------|------------|
209541 | | | | |
-------|----------------|-----------|--------------|------------|
* Noel -
Already asked him 3 times... maybe he'll actually help us help him this time...
-
I know ... that is why I tried to post a table so that he can be CLEAR about what data goes into what column!
* Noel
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply