• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • OLAP Is Simple, Once You Understand It

    May 9, 2012 Hey, Ted

    I have a problem that I solved with a simple RPG program, but I know it can be done with SQL as well. I needed to generate a simple table of employee ID, name, and average of the last five weeks paychecks. Is this simple or complicated to do with SQL?

    –Barry

    You are correct that SQL can do the calculation, Barry. However, you’ll have to decide for yourself if it’s simple or complicated.

    First, let’s get some data to work with. I’m going to simplify your question slightly by using a name only, without an ID, to identify an employee.

    create table paydata
       (Emp   char(5), PayDate date, GrossPay dec(5,0))
    
    insert into paydata values
    ('Joe', '2012-01-06',  50),
    ('Joe', '2012-01-12', 100),
    ('Joe', '2012-02-03', 120),
    ('Joe', '2012-02-17', 110),
    ('Joe', '2012-03-02', 140),
    ('Joe', '2012-03-16', 100),
    ('Joe', '2012-03-30', 100),
    ('Sam', '2012-01-12',  10),
    ('Sam', '2012-02-17',  30),
    ('Sam', '2012-03-02',  70),
    ('Sam', '2012-03-16',  50),
    ('Sam', '2012-03-30',  50),
    ('Bill','2012-03-30',  20)
    

    Now, let’s consider the question in two different ways, because there are applications for each one. First, let’s get the average of the paychecks for the last five weeks. For the PAYDATA table, that means February 3 through March 30. Some employees may not have five paychecks, because they may have started during that period (like Bill), or may have quit and come back after missing one or more pay periods (like Sam). We will get the average of five or fewer paychecks.

    with WeekNumbers as
       (select paydate,
               rank() over (order by paydate desc) as WeekNumber
          from (select distinct paydate 
                  from paydata) as x)
    select pd.emp, avg(pd.grosspay) as avgpay
      from paydata as pd
      join WeekNumbers as w
        on pd.paydate = w.paydate
     where w.WeekNumber <= 5
     group by emp
     order by emp
    

    Let’s break it down to make it easier to understand.

    The common table expression uses the RANK function to assign numbers to all of the payroll dates, beginning with the most recent payroll date and working backward. March 30 is week 1, March 16 is week 2, and so forth.

    with WeekNumbers as
       (select paydate,
               rank() over (order by paydate desc) as WeekNumber
          from (select distinct paydate
                  from paydata) as x)
    

    WeekNumbers looks like this:

    PAYDATE           WEEKNUMBER
    2012-03-30                 1
    2012-03-16                 2
    2012-03-02                 3
    2012-02-17                 4
    2012-02-03                 5
    2012-01-12                 6
    2012-01-06                 7
    

    The main select retrieves the data for weeks 1 through 5 only and averages gross pay for each employee. The result set looks like this:

    EMP      AVGPAY
    Bill         20
    Joe         114
    Sam          50
    

    That answers your question. Let’s consider another common, but slightly different query.

    Suppose we need the average of the last five paychecks, regardless of when they were issued. (A more realistic example would be to retrieve the last five payments for each customer, because those payments would be made on different days, not on regularly scheduled dates. However, I don’t want to introduce another data set, so we’ll make do with payroll.)

    Here’s the query to get the average of the last five paychecks, whenever they were issued:

    with t1 as
       (select emp, paydate, grosspay,
               rank () over (partition by Emp order by paydate desc) as PaymentNumber
          from paydata as x  )
    select Emp, avg(GrossPay) as AvgPay from t1
     where PaymentNumber <= 5
     group by Emp
     order by Emp
    

    The common table expression assigns sequential numbers to each payment, beginning with the most recent payment and working into the past. PARTITION BY makes the system give each employee his own set of numbers. T1 looks like this:

    EMP    PAYDATE     GROSSPAY   PAYMENTNUMBER
    Bill   2012-03-30       20                1
    Joe    2012-03-30      100                1
    Joe    2012-03-16      100                2
    Joe    2012-03-02      140                3
    Joe    2012-02-17      110                4
    Joe    2012-02-03      120                5
    Joe    2012-01-12      100                6
    Joe    2012-01-06       50                7
    Sam    2012-03-30       50                1
    Sam    2012-03-16       50                2
    Sam    2012-03-02       70                3
    Sam    2012-02-17       30                4
    Sam    2012-01-12       10                5
    

    The main select averages payments 1 through 5 for each employee. Here’s the result set:

    EMP       AVGPAY
    Bill          20
    Joe          114
    Sam           42
    

    So, what do you think, Barry? Is the SQL method simple or complicated?

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    [Webinar Series] Demystifying DevOps on the IBM i

    Join us for a 3-part “Live” Roundtable webinar series as we demystify DevOps on the IBM i.  We’ll start with discussions on Git and the IBM i and developer tool options.  Next, we will cover Branch Management and Builds. We’ll end with discussions on an Automated DevOps Workflow.  It will be an informative series on modernizing the IBM i development process. Our speakers will address your questions and share their years of experience and best practices.

    Register NOW

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ProData Computer Services:  Save the day with RDR and ProData utilities! Download FREE Trial
    looksoftware:  Webcast - No Limits: The Power of IBM i. Download the video now!
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Safestone Updates Cross-Platform Compliance Tool COMMON Finds Its Happy Spot With IBM i And Disneyland

    Leave a Reply Cancel reply

Volume 12, Number 13 -- May 9, 2012
THIS ISSUE SPONSORED BY:

SEQUEL Software
Help/Systems
CNX

Table of Contents

  • OLAP Is Simple, Once You Understand It
  • The Proper Way To Deallocate A Pointer
  • Admin Alert: Prepping For And Responding To An Unheard Of IBM i #FAIL

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • The IBM i Power10 Upgrade Cycle Forecast Looks Favorable
  • White Hats Completely Dismantle Menu-Based Security
  • Cloud Software To Drive Enterprise Application Growth
  • How Do You Stay In Touch With The IBM i Community?
  • IBM i PTF Guide, Volume 25, Number 6
  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.