Ahmad Alkhawaja Ahmad Alkhawaja - 1 month ago 22
SQL Question

Informix DB - SQL Group By Year, Month, Week, Day, Hour

I want to write sql queries in Informix db to return results that are grouped by Year/Month/Week/Day/Hr (I will write 5 sql queries for each one).

The scenario I have is as follow :


  • I have two tables, Contest table (contains information about contest name, description, start date, submission date, review date, result date), the other table is Submission table (contains submissions made to contests, things like submission id, submitter id, submission date ..etc).

  • What I want to achieve is get progress of a contest by getting submissions made grouped by day (sql returns stats about how many submissions submitted for each day), or by week, or by month, or by year ..etc.



I am familiar with writing sql queries in Informix DB but such scenario turns out to be very complicated to me and I could not figure out how to do it.

Can you please provide me a sample query that accomplish the scenario described above?

RET RET
Answer

If this isn't exactly what you need, it might get you some way along, or give you some ideas:

SELECT contest_id, YEAR(submission_date) AS submission_period, COUNT(*),
       "Y" AS sub_type
  FROM submissions
  GROUP BY 1, 2
UNION ALL
SELECT contest_id, MONTH(submission_date) AS submission_period, COUNT(*),
       "M" AS sub_type
  FROM submissions
  GROUP BY 1, 2
UNION ALL
SELECT contest_id, DAY(submission_date) AS submission_period, COUNT(*),
       "D" AS sub_type
  FROM submissions
  GROUP BY 1, 2
ORDER BY 1, 4, 2

However, if you're looking at Month by Year instead of Months in isolation, or Week in Year, then I highly recommend you look at creating a "Time Dimension" such as is used in Data Warehousing applications. This is a table with a record for every day that looks a bit like this:

Date         Year   Month     Week      Quarter  Day    MthName
2013-01-01 | 2013 | 2013-01 | 2013W01 | 2013Q1 | Tues | January
2013-01-02 | 2013 | 2013-01 | 2013W01 | 2013Q1 | Wed  | January
..
2013-03-20 | 2013 | 2013-03 | 2013W12 | 2013Q1 | Wed  | March
..
2013-05-01 | 2013 | 2013-05 | 2013W18 | 2013Q2 | Wed  | May

By joining the submission date to this table, you can group by whatever column suits your requirement.

Pro Tip: don't call them what I've labelled those headers, using reserved words will cause you pain :-)