SQLTeam.com | Weblogs | Forums

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


#1

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


#2

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?


#3

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


#4

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
;