Hi Bitsmed,
The following is for the basic aggregate function:
DECLARE @tempTrans TABLE ( [Account] NVARCHAR(20)
, [Voucher] NVARCHAR(30)
, [DocumentDate] DATETIME
, [PostedDate] DATETIME
, [Amount] NUMERIC(32,16)
)
INSERT INTO @tempTrans ( [Account]
, [Voucher]
, [DocumentDate]
, [PostedDate]
, [Amount]
)
SELECT ('abc'),('1'),('2018-01-01'),('2018-01-08'),('100.00') UNION ALL
SELECT ('abc'),('1'),('2018-01-01'),('2018-01-15'),('-100.00')
SELECT DISTINCT [Account]
, [Voucher]
, [DocumentDate]
, Sum([Amount]) AS [TotalAmount]
, Count(*) AS [TransCount]
FROM @tempTrans
WHERE [Account] = 'abc'
GROUP BY [Account]
, [Voucher]
, [DocumentDate]
HAVING Sum([Amount]) = 0
For all results, I'm looking to include the Original 'Amount' for the oldest transaction by PostedDate, with the same Account, Voucher and DocumentDate. For the 2 transactions above, the expected output would be:
Account = abc
Voucher = 1
DocumentDate = 01/01/2018
OriginalAmount = 100
TotalAmount = 0.00
TransCount = 2
I'm using both SQL Server 2009 & 2012
Thanks