Perform calculation to sum values grouped by another column value

Transact-SQL
1

I've created a table of data and need to update the [debit] amount where the description is 'MM House' as follows:
When [Description] = 'MM House'
sum ('MM House' [Amount] - 'Customer Charge 1'[ Amount] - 'Customer Charge 2' [Amount] - 'Customer Charge 3' [Amount]) AS Debit
Group by DocDate, DocNumber

In addition, if the calculation is <= 0 set Debit to 0 and there could be up to 12 Customer Charges per document.

DocNumber CustID Amount CustChargeSum Debit DocDate Description
115003 1150 242.18 242.18 2020-02-19 Customer Charge
115003 1150 242.18 242.18 2020-02-19 MM House
115004 1150 643.78 643.78 2020-02-20 Customer Charge
115004 1150 214.24 214.24 2020-02-20 Customer Charge
115004 1150 424.00 424.00 2020-02-20 Customer Charge
115004 1150 1290.02 1290.02 2020-02-20 MM House

To summarize: In the above table example the Amount value on Row 2 would be zero and the amount value on row six would be 8.00.

I'm spinning my wheels, any help appreciated.

2

How about providing sample data
Thank you

3

Thanks for the reply.

I'm not able to paste the table but the section above represents the columns and data from the table. Is there something additional that would help?

DocNumber CustID Amount CustChargeSum Debit DocDate Description
115003 1150 242.18 242.18 2020-02-19 Customer Charge
115003 1150 242.18 242.18 2020-02-19 MM House
115004 1150 643.78 643.78 2020-02-20 Customer Charge
115004 1150 214.24 214.24 2020-02-20 Customer Charge
115004 1150 424.00 424.00 2020-02-20 Customer Charge
115004 1150 1290.02 1290.02 2020-02-20 MM House

image

4

That's good enough

Please provide like insert statements

Thanks,

5

Given the above data - what are the expected results? How are the Customer Charge rows related to the MM House row - is it DocNumber and CustID?

6 
,CASE
	WHEN [Description] = 'MM House'
	THEN Amount - SUM(CASE WHEN [Description] = 'Customer Charge' THEN Amount END) OVER (PARTITION BY DocNumber, CustID)
	ELSE Debit
END AS Debit
7

Thanks so much! The suggestion works but it does lead me to another question since it inserts the data in an additional column. Following are the results:

DocNumber CustID Amount CustChargeSum Debit DocDate Description CustChargeSum
115003 1150 242.18 NULL 242.18 2/19/2020 Customer Charge 242.18
115003 1150 242.18 NULL 242.18 2/19/2020 MM House 0.00
115004 1150 643.78 NULL 643.78 2/20/2020 Customer Charge 643.78
115004 1150 214.24 NULL 214.24 2/20/2020 Customer Charge 214.24
115004 1150 424.00 NULL 424.00 2/20/2020 Customer Charge 424.00
115004 1150 1,282.02 NULL 1,282.02 2/20/2020 MM House 0.00

Can I now update the Debit column amount with the results? The below attempt fails because it is a windowed function.

update SRR_AR_SalesDataLoad set Debit =
(CASE
WHEN [Description] = 'MM House'
THEN Amount - SUM(CASE WHEN [Description] = 'Customer Charge' THEN Amount END) OVER (PARTITION BY DocNumber, CustID)
ELSE Debit
END)

8

Thanks for the response jeffw8713. If the source data were correct MM House amount would always equal the sum of the Customer Charge amounts and MM House could be eliminated. However, there are three possible scenarios identified so far:

  1. MM House amount equals the sum of the Customer Charge amounts. In this case MM House amount is a duplicate and should be eliminated.
  2. MM House amount is greater than the sum of customer charge amounts. In this case MM House amount should be MM House amount less sum of customer charge amounts. This data indicates that there is a Customer Charge missing from the source data.
  3. MM House amount is less than the sum of customer charge amounts. The result will be a negative MM House amount.
9 
WITH Calcs
AS
(
	SELECT DocNumber, CustID, CustChargeSum
		,CASE
			WHEN [Description] = 'MM House'
			THEN Amount - SUM(CASE WHEN [Description] = 'Customer Charge' THEN Amount END) OVER (PARTITION BY DocNumber, CustID)
			ELSE Debit
		END AS NewCustChargeSum
	FROM <YourTable, sysname, YourTable>
)
UPDATE Calcs
SET CustChargeSum = NewCustChargeSum;
2 Likes
10

Thanks once again Ifor. I'm getting a syntax error on the AS. I'm not familiar with the 'With' function but am researching to get an understanding and consequently determine the issue.

11

Hi @dwelch
try to add semicolon before With

Thanks

1 Like
12

Thank you and that takes care of the AS syntax error but now have syntax error near FROM. I have not figured out what the sysname refers to in the suggested code.

13

Got it! Thanks all for sharing your knowledge. The below tests as desired.

;WITH NewCustChargeSum

AS

(

SELECT DocNumber, CustID, CustChargeSum, DocDate

	,CASE

		WHEN [Description] = 'MM House'

		THEN Amount - SUM(CASE WHEN [Description] = 'Customer Charge' THEN Amount END) OVER (PARTITION BY DocNumber, CustID, DocDate)

		ELSE Debit

		END AS NewCustChargeSUm


FROM SRR_AR_SalesDataLoad
where CustID = '1198'

)

UPDATE NewCustChargeSum

SET CustChargeSum = NewCustChargeSum;