Archive for the ‘Algorithms’ Category.

## SQL for Breaks in a Sequence

In a previous post, Views You Can Use: Ordinal Numbers, we discussed how to acknowledge children who visited Pee Wee Bees most often.  The little boy who had been here the most (88 times to date) should be in first place.  The little girl who has visited 83 times should be in second place.  It’s easy to count each child’s number of visits; harder to assign ordinal numbers (first, second, third) and recognize ties (two siblings have been here 79 times).

I also have a “Hot Streaks” list that shows the children who have visited Pee Wee Bees at least once per calendar month for at least the past three calendar months.  This was a more challenging query.  A child who visits once in January, once in February, and once in each following month throughout 2012, would have a streak of twelve consecutive months.  A child who visited ten times in September, ten times in October, not once in November, and ten times in December would have a “streak” of one month.

## Date Arithmetic Breaks the Laws Of Arithmetic

One of our readers, John, recently posted a question on the SQR Dates blog entry:

“I need to subtract from date in the format years, months, days. For example, I want to subtract 5 years, 2 months and 3 days from the current date.  How can this be done in SQR?”
Continue reading ‘Date Arithmetic Breaks the Laws Of Arithmetic’ »

## The Bug in Every Peoplesoft SQR Program

I shouldn’t claim that this bug appears in every SQR program written for the Peoplesoft environment.  It’s not even in every SQR program that takes run control parameters.  But I bet it is in most of them.

## Rounding Error

Some people use the term “rounding error” to indicate that an answer is right enough, although not perfectly right.  Those people think “accurate” and “precise” are two different concepts.  They may be experimental scientists or programmers, but they aren’t working in Accounting.  Accountants cannot and should not leave journal entries alone with rounding errors.

## The Rewrite And The Bug Fix

Last week I worked on two SQR program bugs.  I rewrote the first one from scratch.  I wanted to rewrite the second one but didn’t.  Here is why.
Continue reading ‘The Rewrite And The Bug Fix’ »

## Dynamic Views in Application Designer

Views are a useful feature of SQL databases, letting us create virtual tables based on SQL select statements.  The Peoplesoft Application Designer also lets us create dynamic views, which we may use within the page processor.

## Evaluate Two Values At Once In SQR

Last year I wrote about the similarity and superiority of the SQR evaluate command compared to the C or Java switch command in SQR Evaluate Versus C/Java Switch, part 1 and part 2. Here is another way to extend evaluate functionality.
Continue reading ‘Evaluate Two Values At Once In SQR’ »

## How To Write Spreadsheets From SQR

SQR and the Peoplesoft Process Scheduler work together to allow users to choose output format. The favorites in my organization are document (PDF) and spreadsheet (XLS). I mentioned this in 4 SQR Resolutions and I’ve had three readers ask me to elaborate. For this blog, that’s a tsunami of feedback.