Juha K Juha K - 1 month ago 22
SQL Question

Sum of transactions between varying dates

Background

I have a table containing transactions. There are two types of transactions: "normal entries" (type=N), and "fix" entries (type=F). Each transaction has a client-ID, a date, a type code, and an EUR amount. Some example data is below:

| client_id | date | transaction_type | amount |
|-----------|-----------|------------------|---------|
| 111 | 01jan2015 | N | 1000.0 |
| 111 | 01jan2015 | F | -500.0 |
| 222 | 05mar2015 | N | 2000.0 |
| 222 | 06mar2015 | F | -100.0 |
| 222 | 07mar2015 | F | -100.0 |
| 222 | 09mar2015 | N | 1000.0 |
| 222 | 10mar2015 | N | 400.0 |
| 222 | 15jun2015 | F | -200.0 |


The fix entries are manual corrections to normal transactions made by someone at the register. They can be done on the same day or after the normal transaction, but if a new normal transaction is entered for the same client, all that client's consecutive fixes concern the new transaction (until yet another normal transaction is entered). So in effect, all fixes are "fixing" only the latest transaction of that client.

The fixes can be positive or negative numbers, the normal transactions only positive.

Desired output

What I want is a set of "normal" transactions per client, with a sum amount corrected by all the fixes related to that transaction. Example data below:

| client_id | date | amount |
|-----------|-----------|--------|
| 111 | 01jan2015 | 500.0 |
| 222 | 05mar2015 | 1800.0 |
| 222 | 07mar2015 | 1000.0 |
| 222 | 08mar2015 | 200.0 |


So this is a sum of one transaction of type N and all the consecutive F-transactions up until the next N-transaction.

What I have so far

If all the fixes happen on the same date as the original transaction (as is usually the case), this is very simple:

select client_id, date, sum(amount)
from transaction_table
group by client_id, date


However, I'm having problems handling fixes that happen after the original transaction date, because I need to pick only those that happen before the next normal transaction (and this needs to apply for each normal transaction).

A note on products in use

I'm actually using SAS 9.4, but through SAS's proc sql procedure I can apply basic SQL and that's what I'm more comfortable using. Nothing fancy though (so cursors, CTEs and such are out). A nice SAS answer will be accepted, too!

Answer

Create a grouping flag that is set at every N. What happens if there's multiple purchases on same day?

 Data want;
  Set have;
  By ID;
   Retain purchaseGroup;
  If transx = 'N' then purchaseGroup+1;
  If first.id then purchaseGroup=1;
 Run;

Then summarize using a SQL step grouping by ID and PurchaseGroup.