Proper totals/results on timebased entries and cross-linked tables

[llirik]

Hey guys,

 

First post, but I've been coming here the last couple of months and finding answers to questions so I never had a reason to create an account.

 

But currently, I'm a bit a stuck and I'm positive this should be handled SQL side (rather than php... i can make this work via php, but it would be ineffecient).

 

What I have, is a database that tracks the status/locations of units on a per-change basis, (ie every status change etc is logged so there can be a history trail).

 

Now, what I'm having trouble with figuring out how to approach is to total up (either COUNT() or mysql_num_rows) the total amounts per each status change).

 

Meaning, there is one table "wip_status" which traffics every change, storing the unit_id, the user who changed it, the actual status, the time etc. Example status are things like "not set (0), available (1), pending (2)" etc... (in my db, they're all tracked by numbers, with one seperate table to distinguish what the numbers mean).

 

What I need to do is to have a query that will go through the entire wip_status and count how many unit_ids are have a certain status as its MOST RECENT timestamp. I've tried going about this with subqueries but have not had much luck.

 

Also, I track SOLD units in a seperate db (wip_sold) which is NOT reflected in wip_status (on my client side, when a unit_id is found to be sold in this table, it does not need to read the status' from wip_status) so these units need to be excluded from the COUNT() that comes up in wip_status.

 

The current SQL statement that i've been working with is:

 

SELECT *,
( SELECT COUNT(id) as sold FROM wip_sold ),
( SELECT COUNT(id) FROM wip_status WHERE status = wip_status.status ),
COUNT(id) 
FROM wip_status  
GROUP BY status 
ORDER BY datetime 
DESC

 

but its not getting me results that I would like.

 

 

 

my test values/structure are (all this works perfectly when dealing on a per unit basis, all changes display correctly)

 

wip_status

 

id unit_id uid ip status datetime

1 R100  1 # 1 2009-03-16 02:31:01

2 J652  1 # 3 2009-03-16 02:31:01

3 R100  2 # 2 2009-03-16 02:51:27

4 J652  4 # 1 2009-03-16 06:07:38

5 S300  1 # 0 2009-03-16 06:07:38

6 R100  3 # 3 2009-03-16 08:25:27

7 R100  1 # 2 2009-03-17 01:13:25

8 R300  1 # 1 2009-03-17 01:18:06

9 R100  3 # 1 2009-03-18 02:24:06

 

wip_sold

 

id uid byuid datetime

R100 4 1 2009-04-14 05:33:57

 

so R100 is sold and needs to be excluded from wip_status (its easy enough for me to total sold units as once a unit it sold, it obviously stay there)

 

So my expected result is:

status (0) = 1 (S300)

status (1) = 2 (R300, J652)

status (2) = 0

status (3) = 0

status (sold) = 1 (R100)

 

any help is greatly appreciated. i'm not sure how the SQL side should look. i'm only able to get partial results and they're not even that accurate.

[fenway]

First, you need to get the most recent timestamped "thing"... then you need to join it back.