How Do I get original amount based on Posted Date, in an aggregate query?

Hi All,

I'm trying to find out how to include the original amount of the first transaction (oldest by Posted Date) to an aggregate query.

The following finds reversed transactions ..

SELECT DISTINCT [Account], [Voucher], [DocumentDate], Sum([Amount])
FROM MyTable
WHERE [Account] = 'abc'
GROUP BY [Account], [Voucher], [DocumentDate]
HAVING Sum([Amount]) = 0

How would I add the Amount for the transaction with the oldest Posted Date to each record?

Thanks in advance

Please provide:

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected output from your provided sample data

Which version of Microsoft SQL Server are you using?

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

Option 1

Query
select account
      ,voucher
      ,documentdate
      ,originalamount
      ,totalamount
      ,transcount
  from (select distinct
               account
              ,voucher
              ,documentdate
              ,first_value(amount) over(partition by account,voucher,documentdate order by posteddate) as originalamount
              ,sum(amount) over(partition by account,voucher,documentdate) as totalamount
              ,count(*) over(partition by account,voucher,documentdate) as transcount
          from @temptrans
       ) as a
 where totalamount=0
;

Option 2

Query
select account
      ,voucher
      ,documentdate
      ,sum(case when rn=1 then amount else 0 end) as originalamount
      ,sum(amount) as totalamount
      ,max(rn) as transcount
  from (select account
              ,voucher
              ,documentdate
              ,amount
              ,row_number() over(partition by account,voucher,documentdate order by posteddate) as rn
          from @temptrans
       ) as a
 group by account
         ,voucher
         ,documentdate
 having sum(amount)=0
;