Yes or No, Not the Whole Table

Easy

sql-budget

existence-check

cust-ledger-entry

Yes or No, Not the Whole Table

The customer list got a little activity panel: does this customer have open ledger entries, how many, and is the customer dormant? It looked instant in the demo company. On the production database, where a busy customer carries hundreds of ledger entries, scrolling the list now crawls — the trace shows every entry being dragged into AL just to produce a yes/no or a single number.

Your job: same three answers, at row-one prices.

Requirements

Create a codeunit named "Customer Activity Check" with three public procedures:

procedure HasOpenEntries(CustomerNo: Code[20]): Boolean
procedure IsDormant(CustomerNo: Code[20]): Boolean
procedure OpenEntryCount(CustomerNo: Code[20]): Integer

Rules:

HasOpenEntries returns true exactly when at least one Cust. Ledger Entry with "Customer No." = CustomerNo has Open = true. Closed entries don't count, and a customer with no entries at all returns false.
IsDormant returns true exactly when the customer has no Cust. Ledger Entry at all — open or closed. A single closed entry is history enough: it must return false.
OpenEntryCount returns how many Cust. Ledger Entry records of that customer have Open = true — exactly, and 0 when none are open.
Entries belonging to other customers must never influence any of the three answers.
None of the procedures ever raises an error — a customer number with no entries is a normal input, not a failure.
The budget: one call to any of the three procedures must execute at most 5 SQL statements and read at most 10 rows, no matter how many ledger entries the customer has. Grading measures SessionInformation.SqlStatementsExecuted and SessionInformation.SqlRowsRead around a single call while the customer holds a couple hundred entries — an implementation that fetches the entries into AL to inspect or count them reads them all and fails.

What the tests check

The grading tests create real customers through the standard number series — the graded customer numbers differ from run to run, so an answer keyed to a specific customer number or a hardcoded count has nothing stable to latch onto — and seed Cust. Ledger Entry rows for them directly; the counting test asserts the exact number of open entries it seeded. Decoys are planted: closed entries sitting next to open ones, and a neighbour customer whose entries must not leak into your answers. The three budget tests warm the caches with one throwaway call, then invalidate the server's data cache — a repeated call served from cache memory costs zero SQL, so cached reads can't smuggle row-by-row work past the budget — and snapshot the SessionInformation counters around one graded call per procedure: HasOpenEntries on a customer whose ~200 entries are all closed, IsDormant on a customer with ~150 entries, and OpenEntryCount on a customer with well over 100 open entries. The tests run in a real company with existing data, so your answers must be driven purely by the customer number and the rules above.

Learn More

Hint 1

Each answer is one word — a yes/no or a single number — yet the starter hauls every ledger entry across the wire to inspect it in AL. Rows you fetch but never show are the whole cost: push the entire question into the database and read back only the answer.

Hint 2

A condition you test with if inside a loop belongs in the filter instead. Once Open is part of a SetRange, the filtered set IS the answer — one set-based call replaces the whole loop, for existence and for counting alike.

Hint 3

The record API has a purpose-built method per question: IsEmpty() answers 'is there at least one?' with a single TOP-1 statement that returns at most one row, and Count() makes SQL Server do the counting and send back just the number. FindSet or FindFirst plus repeat is never the cheap way to answer either.

ALBusiness Central 28.5

Press Compile to check your code compiles — Submit runs the tests.

The code editor is desktop-only

Open this problem on a computer to write and run code. Reading the description, tests and discussion works fine here.