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.

Continue reading ‘SQL for Breaks in a Sequence’ »

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.

Continue reading ‘The Bug in Every Peoplesoft SQR Program’ »

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.
Continue reading ‘Rounding Error’ »

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.

Continue reading ‘Dynamic Views in Application Designer’ »

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.
Continue reading ‘How To Write Spreadsheets From SQR’ »

SQR Functions

The SQR language has several families of built-in functions. They range from minor conveniences to potential life savers. There are some surprising inclusions and surprising omissions. I discuss some of the functions I wish we had in An SQR Wish List.

Continue reading ‘SQR Functions’ »

Reporting Changes In Peoplesoft With SQR, Part 2

Last week, we considered change reporting based on the effective dates and action dates of PS_JOB. Other Peoplesoft tables track history differently. We need other approaches to report those changes.
Continue reading ‘Reporting Changes In Peoplesoft With SQR, Part 2’ »