Link to home
Start Free TrialLog in
Avatar of John Ellis
John Ellis

asked on

T-SQL: Subtracting Amounts from "Among Rows"

Hello:

Below is my code that requires modification.  Underneath this code, I have embedded a screenshot of the data displayed.

I need for OPEN A/R in the second row to be subtracted from the amount in the first row but only for the one CustomerID that exists in those first two rows.  I don't want anything done with OPEN A/R in the third row.

How do I have SQL subtract those two amounts?

Thank you!

John

DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';

select RM20201.CUSTNMBR as [CustomerID], --RM00101.CUSTNAME as [CustomerName], 
RM20201.APTODCNM as [DocumentNumber], 
0 as Cnt,
CASE WHEN RM20201.APFRDCTY = 8 and
RM20201.APTODCTY < 7 and RM20201.DATE1 > @AGE and RM20201.APTODCDT < @AGE AND RM20201.ApplyFromGLPostDate < @AGE and RM20101.CURTRXAM = 0 and RM20201.ApplyFromGLPostDate <> RM20201.APFRDCDT
and RM20201.APFRDCDT <= @AGE AND RM20101.CURTRXAM <> RM20201.APPTOAMT and RM20201.TRXSORCE = '' --and (RM20101.ORTRXAMT - RM20101.CURTRXAM) <> RM20201.APPTOAMT
THEN RM20201.APPTOAMT ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM 
AND RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM 
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', 
'0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049', '0100014968', 
'0100018654', '0100019363', '0100019371',
'0100020932', '0100021047', '0100024819', '0100030188', '0100014661', '0100015270', '0100032895', '0100022052',  
'0100033398', 'WW_FLIGHT12', 'WEST_MD_HC01', 'TRN6557', 'SVC989E')) 
GROUP BY RM20201.CUSTNMBR, RM00101.CUSTNAME, RM20201.APFRDCTY, 
RM20201.APTODCDT, 
RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20201.APPTOAMT, RM20201.APFRDCNM, RM20201.APFRDCDT, RM20201.APTODCNM, RM20201.ApplyFromGLPostDate, RM20201.APTODCTY, RM20201.TRXSORCE
--RM20201.APFRDCDT, RM20201.APTODCNM, RM20101.DOCDATE,
HAVING 
CASE WHEN RM20201.APFRDCTY = 8 and
RM20201.APTODCTY < 7 and RM20201.DATE1 > @AGE and RM20201.APTODCDT < @AGE AND RM20201.ApplyFromGLPostDate < @AGE and RM20101.CURTRXAM = 0 and RM20201.ApplyFromGLPostDate <> RM20201.APFRDCDT
and RM20201.APFRDCDT <= @AGE AND RM20101.CURTRXAM <> RM20201.APPTOAMT and RM20201.TRXSORCE = '' --and (RM20101.ORTRXAMT - RM20101.CURTRXAM) <> RM20201.APPTOAMT
THEN RM20201.APPTOAMT ELSE 0 END
<> 0

Open in new window


User generated image
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Normally you'd do a SUM([Open A/R]), but in this case, how do you know that the 45.05 is the number you're subtracting since it's not negative? The query is way too complicated to try and sort out exactly what you're looking for - if you have a simpler example, we could show you how to do it and you can apply it to the larger query, but I'm still not clear how you'd know which amount is positive and which you one you want to subtract since they have the same details displayed.
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My first question is, "What was the scenario that caused there to be two rows?" Could there be more than two? What is the reason behind the need to do the subtraction?