Wednesday, 15 February 2012

SQL 101: when storing daily figures...

SQL 101: when storing daily figures in weekly batches, you stash half the data in one table and number all the days from 0 to 7 inclusive. (Yes, an eight day week.) Then you store the other half of each week's data in an almost identical table, but number those days NULL+0-6 inclusive. For added variety, try changing which day 0 refers to mid-dataset.

NULL. It's not just for breakfast any more.


  1. I recently played games a bit like that working with data created under ASP (Weekdays 1-7) while building a .Net display component (Weekdays 0-6).

    Fortunately, no nulls.

  2. It turns out the eight-day weeks were because he didn't know about adding up in SQL, so he was generating and storing weekly totals (from the Javascript front end) in days NULL and 7 respectively.

    Needless to say, one of the change requests was to fix the fact the current code is unusably slow when you have a year of data in. It fetches the whole year's data and renders every single week's data as an HTML form, then hides all weeks but the one you're looking at.

  3. So do you now get to scrub the "weekly" records, correct the Day of Week mismatch, and implement proper reporting queries/procedures?

  4. Worse - I'm down to one day a week there* now, so I kludge the code just enough that it stops showing this symptom again. I'm trying to scrape a little bit of fail out of this mess each time I have to patch it, though.

    The 'good' news is that the only reporting done consists of plotting a graph of the weekly figures anyway.

    (* - six figures of government funding to develop these two web applications and one desktop application; one day a week to maintain those, a third web application, two company websites - one half in Croatian - as well as doing the company accounts. Makes for a very busy day...)