Pivot

Pivot table

Category: SQL
Tags: SQL Oracle

Intro🔗

Occasionaly I get hired to work on a sophisticated scheduling software. While I tried to gather more domain knowledge, I had the luxury to assist the sales manager during a prospect visit. The prospect was rather impressed with the software but they had one requirement:

Their current system (MS Excel) had a simple counter that enabled them to assert that none of the staff worked more then 52 hours in a 14 day cycle.

Simple query🔗

I guess it wa a kind of a liablity issue that they needed to cover. Such a report is rather trivial. Here below is a mockup of such a query.

file: group_by.sql

SELECT
    period.time_start,
    period.time_end,
    staff.name,
    worked.minutes / 60 as hours
FROM (
    SELECT DISTINCT
        TO_CHAR( slots.timestamp, 'YYYY-MM-DD' )		AS time_start,
        TO_CHAR( slots.timestamp + 14, 'YYYY-MM-DD' )		AS time_end
    FROM slots slots
    ) period
    LEFT OUTER JOIN (
        SELECT
            TO_CHAR( slots_v.timestamp, 'YYYY-MM-DD' )	AS time_start,
            schedule.staff_id					AS staff_id,
            SUM( slots_v.minutes )				AS minutes
        FROM slots_v slots_v
            INNER JOIN schedule schedule	
                ON 	schedule.act_type_id NOT IN ( 'holiday', 'sickness', 'congres' )
                AND schedule.timestamp = slots_v.timestamp
                AND TO_CHAR( schedule.timestamp, 'YYYY-MM-DD' ) BETWEEN TO_CHAR(slots_v.timestamp, 'YYYY-MM-DD') AND TO_CHAR( slots_v.timestamp + 14 , 'YYYY-MM-DD' ) 
        GROUP BY	TO_CHAR( slots_v.timestamp, 'YYYY-MM-DD' ), schedule.staff_id
    ) worked 
        ON period.time_start = worked.time_start
    LEFT OUTER JOIN staff staff 
        ON worked.staff_id = staff.id
WHERE	period.time_start BETWEEN '2017-01-01' AND '2017-02-01'
ORDER by period.time_start,
        staff.name

That gives just a list.

Pivot🔗

To go the extra mile it would be nice if that list could be made in a nice overview:

It happened to be that this application was running on a oracle database. I was realy excited to use pivot, as that is a realy nice feature that neither DB2 nor PostgreSQL have.

In fact, oracle makes it is very easy, to use 'SUM' as a cummulative function:

SELECT *
    FROM (
    --origional query goes here
    )
    PIVOT ( SUM( field-to-sum- goes-here ) FOR name IN ( [ array-with-the-columnsnames-that-we-want] ) )

In our case this results into:

file: pivot.sql

SELECT *
    FROM (
    SELECT
        period.time_start,
        period.time_end,
        staff.name,
        worked.minutes / 60 as hours
    FROM (
        SELECT DISTINCT
            TO_CHAR( slots.timestamp, 'YYYY-MM-DD' )			AS time_start,
            TO_CHAR( slots.timestamp + 14, 'YYYY-MM-DD' )		AS time_end
        FROM slots slots
        ) period
        LEFT OUTER JOIN (
            SELECT
                TO_CHAR( slots_v.timestamp, 'YYYY-MM-DD' )	AS time_start,
                schedule.staff_id							 	AS staff_id,
                slots_v.minutes
            FROM slots_v slots_v
                INNER JOIN schedule schedule
                    ON 	schedule.act_type_id NOT IN ( 'holiday', 'sickness', 'congres' )
                    AND schedule.timestamp = slots_v.timestamp
                    AND TO_CHAR( schedule.timestamp, 'YYYY-MM-DD' ) BETWEEN TO_CHAR( slots_v.timestamp, 'YYYY-MM-DD' ) AND TO_CHAR( slots_v.timestamp + 14 , 'YYYY-MM-DD' ) 
        ) worked 
            ON period.time_start = worked.time_start
        LEFT OUTER JOIN staff staff 
            ON worked.staff_id = staff.id
    WHERE	period.time_start BETWEEN '2017-01-01' AND '2017-02-01'
    )
    PIVOT ( SUM( hours ) FOR name in ( 'Smith', 'Johnson', 'Eisenhower' ) )
ORDER BY time_start

Snowball effect🔗

It was very easy to add this pivot query to a dynamic report in APEX. The prospect was happy and signed the deal.

As a side effect, the main developer got very excited and took this idea one step further.

  • Several other reports, for other metrics were created. Each giving more and more insights in how the existing customers of the service where handeling their planning.
  • These ratings gave each planner a better indication how 'fair' or 'balanced' the planning was.
  • This again lead to a better use of the planning tools and algorithms.
  • This lead to a better planning.
  • This lead to a happier staff.
  • This lead to better input for the planner,
  • ..

Off topic🔗

These 'do not work more then ..' comparissons do remind me of a side-project that I had a few years ago. We sold a reception/waiting-queue ticket system to a customer. The customer that offered a set of services where small business owners could come in and ask questions about a whole set of topics.

  • There were 5 desks in the frontoffice, manned by 1-5 persons.
  • Not every person was trained for every category.
  • Some categories were easier (read: faster) to handle then others.
  • Some tasks could be handled by the backoffice.
  • When it was very busy at the frontoffice, the people in the backoffice, would assist at the frontoffice.

So, when a small business owner punched a category on the touchscreen and got a ticket, our system did a very sophisticated planning. It worked fine, (accourding to us, techies). But the first version was not accepted by the staff.

We could not pinpoint the exact cause of the problems and the complaints; Our logs and on-site measurements showed that the assignement model was working fine. Further discussion with the staff highlighted the root cause of the problem.

"It is no problem that I do more work then the colleagues."

"It is a problem that the colleagues do less work then I."

Finally we changed the assignment algorithm into "Assign the task to the person that is allowed, but has had the fewest contact-minutes customer since the start of this shift".

After those changes:

  • Everybody on the staff was happy.
  • Even the manager was satisfied, allthough my initial measurements showed that:
    • the time to finish a task went up significally.
    • the backoffice did not assist the frontoffice anymore.