Actuate Programming Journal

Report design from a guy who thinks he knows what he’s doing; you decide . . . .

Date Wrapping

leave a comment »

I have a report that is the presentation piece for some end to end poor design.

For reasons that are no longer known to me the database uses what we refer to as snapshots. These snapshots are exactly what you would think: pictures of a specific table at a specific time. I am sure that some obvious drawbacks come to mind concerning the bulk of data we must be repeating in a table or the chance that a snapshot is missed on a particular day. That is not what I am here to divulge today.

The report provides eight weeks worth of snapshots as well as comparisons (adds and subtracts) between consecutive weeks. The data is displayed as couple of slightly different charts, a bar chart, and a pie chart. It captures the information using a complicated set of UNIONs between each snapshot to basically diff the data. Week 1 had 100 widgets; week 2 had 110. That means that 5 were lost and 15 were gained, not that 10 were gained.

This is all great till you discover that 2005 has 53 weeks not just 52.

This post serves as a warning more than instruction. If you have to compare week to week data wrapping across a year/year threashold keep in mind that–as hard as this can be on its own–you will need to find a place in your calculations for 53 weeks in a year. That and you’ll finally get to use mod( ).

In one of the charts I have used a column numbering system that allows me to put rolling weeks worth of data in it. So that this week is always in the first column no matter what week it is. To create these numbers (1-9) I am comparing the current date to the week (1-53) that the snapshot was taken.

(mod(((week(current timestamp) - WIDGETS.SNAPSHOT_WEEK) + 53), 53) + 1) as CHART_COLUMN

By subtracting the snapshot week from the current week and adding 53 I get a value that tells me how many weeks back from this week the snapshot was. So a snapshot on week 48 of the year is 5 weeks back from the first week of this year. Unfortunately this results in a vlaue for this week compared this week as being 53 instead of 0. Running the results through mod(53) keeps all the values the same except the 53 which is converted into a 0. So now with 9 weeks of data I have CHART_COLUMN values of (0-8).

Though I am only displaying 8 weeks of data, I need to have the 9th one back to properly display the differences for week 8.

In all, I had originally used mod(52), because–you know–there are 52 weeks in a year. The result was in one chart causing week 53 to be lumped in with week 1 an in another dropping it totally. By simply using mod(53) is solved all my problems.

Hope everyone had a great week 53 of 2005!


Written by Douglas

20060104 at 16:56 pm

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: