-2

I have a dataset that has two date columns for each transaction. An invoice date and a charge date. I am trying to do a cumulative or running total of the table in sql.

Data table looks like this:

InvoiceDate ChargeDate TransactionID
----------- ---------- -------------
jan/1/2021  jan/1/2021 11111
jan/1/2021  jan/1/2021 55555
jan/1/2021  Feb/1/2021 11111
jan/1/2021  Feb/1/2021 22222
jan/1/2021  Feb/1/2021 33333
jan/1/2021  Feb/1/2021 44444
Feb/1/2021  jan/1/2021 11111
Feb/1/2021  jan/1/2021 22222
Feb/1/2021  jan/1/2021 33333
Feb/1/2021  jan/1/2021 44444
Feb/1/2021  Feb/1/2021 55555

I want the output in this format:

InvoiceDate ChargeDate cumulativeCount
----------- ---------- ---------------
jan/1/2021  jan/1/2021 2
jan/1/2021  Feb/1/2021 5
Feb/1/2021  jan/1/2021 4
Feb/1/2021  Feb/1/2021 5

I tried the following code but it doesn't give me a running total. it gives me total of each of the two columns

select
    chargeDate,
    InvoiceDate,
    Count(Distinct TransactionID) as CountofIDs
from mytable1
where InvoiceDate<=InvoiceDate
group by ChargeDate, InvoiceDate
markp-fuso
  • 2,604
  • 1
  • 10
  • 19
Rishtin
  • 1
  • 1

1 Answers1

1

Looking this over, due to the need for a distinct count and the lack of other options in Sybase ASE, you'll have to use a self-join:

SELECT
  Tr.InvoiceDt
 ,Tr.ChargeDt
 ,COUNT(DISTINCT PriorTr.TransactionID) AS TransactionCount
FROM
  mytable1 Tr
INNER JOIN
  mytable1 PriorTr
    ON PriorTr.InvoiceDt = Tr.InvoiceDt
        AND PriorTr.ChargeDt <= Tr.ChargeDt
GROUP BY
  Tr.InvoiceDt
 ,Tr.ChargeDt

Fiddle here (have to settle for the distant cousin, SQL Server): http://sqlfiddle.com/#!18/ff8e0d/2