-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
10
  • Please tag with version of SQL Server - also InvoiceDate<=InvoiceDate will always evaluate to true, not sure the purpose.
    – bbaird
    Jul 26 '21 at 17:37
  • tagged the versions i suspect. its the version where OVER function is not supported.
    – Rishtin
    Jul 26 '21 at 17:42
  • Do SELECT @@VERSION and you should be able to get the exact version, or just leave as 2005 since the only windowing functions were row_number(), rank(), etc.
    – bbaird
    Jul 26 '21 at 17:45
  • Adaptive Server Enterprise/16.0 SP02 PL04/EBF 26122 SMP/P/x86_64/Enterprise Linux/ase160sp02pl04x/2586/64-bit/FBO/Fri Jun 10 11:33:16 2016
    – Rishtin
    Jul 26 '21 at 17:49
  • That's Sybase ASE 16, not SQL Server. You should be able to use standard windowing functions.
    – bbaird
    Jul 26 '21 at 17:52
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.