Bounced check problem

Question

I have a simple StreamInsight application that calculates a running total from a set of bank transactions using the recommended method of extending the events and summing across the snapshots.

            IQStreamable<double> transactions;

            var balance = from w in transactions.AlterEventDuration(_ => TimeSpan.MaxValue).SnapshotWindow()

                    select w.Sum(e=>e);

The problem is there are credit and debit transactions and sometimes a debit cannot be applied as it will force the account into the red. We can certainly skip the creation of a Sum for the transactions that force the Sum to go negative but they will remain in the Sum and will be included when later positive transactions are added (due to all events having an infinite event duration). e.g.  

Transactions:  1,  4,-10, 4,  5

QueryBalance:1,  5,    -, -,   4

ActualBalance:1,  5,    -, 9, 14

What I ended up doing is creating a user defined operator to “net” out the transactions in time order at every snapshot, but it doesn’t perform very well. What we really want to do is to remove the bounced check transactions forever which I think is an instance of a more generic pattern of providing a filtered feedback loop from the output to the input of the query delayed by some interval of t+nEvents.

Does such a pattern exist in StreamInsight?

---

peter_m_reid

Answer 1

There isn't a built-in pattern but that doesn't mean that you couldn't solve it. Here's how I would attack the problem:

1) Check the transaction's impact on the balance. The snapshop that you are currently doing may work.

2) For transactions that put the balance "in the red", filter them out. For example, "where 0 > balance - debitAmount" or something similar.

3) Do a second snapshot that calculates on only the included transactions.

I'm not sure if that's helpful ... a LinqPad sample with some sample "transactions" that would trigger this condition would be very helpful.

---

DevBiker (aka J Sawyer)
Microsoft MVP - Sql Server (StreamInsight)

---

---

If I answered your question, please mark as answer.
If my post was helpful, please mark as helpful.

Answer 2

Oh yeah LinqPad, using the example of 1,4,-10,-4,1

The correct answer is 2 (just the -10 check should bounce)

var transactionData = Application.DefineEnumerable(() => new []
{
	new { Transaction = 1.0, TimeStamp = new DateTime(2009, 10, 23, 0, 0, 0, DateTimeKind.Utc) },
	new { Transaction = 4.0, TimeStamp = new DateTime(2009, 10, 24, 0, 0, 0, DateTimeKind.Utc) },
	new { Transaction = -10.0, TimeStamp = new DateTime(2009, 10, 25, 0, 0, 0, DateTimeKind.Utc) },
	new { Transaction = -4.0, TimeStamp = new DateTime(2009, 10, 26, 0, 0, 0, DateTimeKind.Utc) },
	new { Transaction = 1.0, TimeStamp = new DateTime(2009, 10, 27, 0, 0, 0, DateTimeKind.Utc) },
});

var transactions = transactionData.ToPointStreamable(
	ev => PointEvent.CreateInsert(ev.TimeStamp.ToLocalTime(), ev),
	AdvanceTimeSettings.StrictlyIncreasingStartTime);

var balance = (from w in transactions.AlterEventDuration(_ => TimeSpan.MaxValue).SnapshotWindow()
		  select w.Sum(e => e.Transaction));
		  
(from b in balance
select b).Dump("Balances");

Standard Sum() gives us -8

var balance = (from w in transactions.AlterEventDuration(_ => TimeSpan.MaxValue).SnapshotWindow()
		  select w.Sum(e => e.Transaction)).Where(e=>e>0);

Filter out negative Sum() gives us 5

The problem is we'd like to trim the event duration based on the output of the Sum() of the events in the current window so they are no longer included in any future window.

---

peter_m_reid

Answer 3

Let me make sure that I understand what you are trying to do.

First, the -10 check should bounce. Got that. But if it bounces, shouldn't it be included in the sum (account balance)? I'm assuming that the other check would not be paid or returned?

If I understand you correctly, based on the above, the answer would be -5. Removing just the bouncing items would give you a 6.

---

DevBiker (aka J Sawyer)
Microsoft MVP - Sql Server (StreamInsight)

---

---

If I answered your question, please mark as answer.
If my post was helpful, please mark as helpful.

Answer 4

OK imagine its Johnny's bank account

He's saving pocket money

Save $1, then another $4, to take it to $5 then you want to buy a bike which is $-10 but bounces so you are left with the $5 then he tries a less ambitious purchase of a goldfish at $-4 which succeeds leaving Johnny with 1$, then he saves another $1 leaving him with $2.

(The actual scenario I'm looking at has many, many refused transactions)

I thought initially it'd be a simple LINQ query to write but requires a type of feedback system into the filtering query which I haven't seen an example of anywhere.

My latest thought is to run it as a UDSO so some state can be persisted through time but it doesn't feel very LINQ'y. I want to make sure I'm not missing a part of StreamInsight for implementing feedback loops (which can be very useful in signal analysis).

---

peter_m_reid

Answer 5

Yeah ... there isn't a "feedback loop" like you are looking for. I've looked for it too when trying to do "deadbands" (new value is reported only when it deviates from last reported non-deadband value by a specific percent). There are two possibilities for you in your scenario ... one is the UDSO that you mentioned. The other is a UDA.

I'll try to work through it if I have time later and see if I come up with something different but I don't think that I will.

---

DevBiker (aka J Sawyer)
Microsoft MVP - Sql Server (StreamInsight)

---

---

If I answered your question, please mark as answer.
If my post was helpful, please mark as helpful.