SQL help with a report on latest statuses within calendar weeks

[cjashwell]

Hi all. I have an issue with a piece of SQL I'm trying to write for a MySQL database, and I wonder if anyone has time to have a look at it and see if they can suggest a solution.

The database is used for tracking workflow of projects. Projects are recorded as being at particular stages (represented by number codes). Whenever a user creates a project revision, the revision is timestamped.

Here is some sample data from the two tables relevant to the problem:

projects

id[tab]created
1[tab]1249635614
2[tab]1245765600

id is an auto increment integer (PK).
created is a unix timestamp of the date the project was created

project_revisions
rid[tab]pid[tab]uid[tab]status[tab]timestamp
1[tab]1[tab]ca[tab]9[tab]1249642823
2[tab]1[tab]ca[tab]7[tab]1249642337
3[tab]2[tab]pj[tab]6[tab]1245766500

rid is an autoincrement integer, PK for this table.
pid is the project id, FK for the project table
uid is a varchar field for the user id
status is an integer field representin the current project status
timestamp is a unix timestamp of the project revision

The report the users want is like this:

Week[tab]Status 6[tab]Status 7[tab]Status 9
23[tab][tab]4[tab][tab]2[tab][tab]0
24[tab][tab]1[tab][tab]0[tab][tab]3
25[tab][tab]0[tab][tab]0[tab][tab]6

So the week numbers are row headings down the left and the statuses are column headings along the top. The count at each intersection is the count of the latest revision for that week for each project. For each week, count a status if that is the latest status for a project in that week.

What I have so far is this:

SELECT qgbn.weeknum,
  sum(if(qgbn.status=1,1,0)) 'Status 1',
  sum(if(qgbn.status=7,1,0)) 'Status 7',
  sum(if(qgbn.status=9,1,0)) 'Status 9'
FROM
(SELECT qgb.weeknum, gpr2.gp_id, gpr2.status
  FROM
  (SELECT
    week(from_unixtime(gpr.timestamp)) weeknum, gp.id, max(gpr.timestamp) maxtimestamp
  FROM projects gp
    LEFT JOIN
    projects_revisions gpr on gp.id = gpr.gp_id
  GROUP BY week(from_unixtime(gpr.timestamp)), gp.id) qgb
  JOIN
    projects_revisions gpr2 ON gpr2.timestamp = qgb.maxtimestamp
) qgbn

The logic I'm following here is first constructing a query for each week taking the latest status for each project

SELECT week(from_unixtime(gpr.timestamp)) weeknum, gp.id, max(gpr.timestamp) maxtimestamp
FROM projects gp
LEFT JOIN
    projects_revisions gpr on gp.id = gpr.gp_id
GROUP BY week(from_unixtime(gpr.timestamp))

I'm then using that as an aliased subquery and joing it to project_revisions on the 'maxtimestamp' = timestamp, and selecting the week number, the project id and the status, and using this as an aliased subquery from which to select a 'crosstab' type query by selecting the week number and using an 'if' to count statuses.

I'm slightly nervous reporting that this approach works in MS Access (using the built-in 'transform-pivot' construct in jet SQL), but when I run this in MySQL it returns one row only. It's as if there is some grouping on weeknum taking place without my having specified it.

Does anyone know if this is a quirk of MySQL when using subqueries? Alternatively, can anyone suggest a different approach to getting what my users have requested?

(P.S. Thanks for reading this long-winded post!)

 

[cjashwell]

Ah, sorry, forget all this. I just missed a GROUP BY clause at the end.

*embarrassed*

 

[TrojanWarBlade]

Don't worry about it.
We all miss silly things now and again.





Trojan.