I have a query that i can pull when the last time a location was counted for accuracy:
SELECT a.scannable_id, MAX(a.count_date)
FROM adhocs a
WHERE a.scannable_id like 'P-0-A1A10%'
GROUP BY a.scannable_id;
It returns:
scannable_id MAX(a.count_date)
P-0-A1A10 2010-06-15 17:28:00
P-0-A1A100 2010-07-13 13:20:00
P-0-A1A102 2010-07-18 11:00:00
P-0-A1A106 2010-09-08 18:10:00
P-0-A1A108 2010-08-17 12:56:00
From bth_ust is my stow information, this query returns my stows to the above bins
SELECT distinct date(entry_date) as date_code, new_bin_id
FROM bth_ust
WHERE new_bin_id like 'P-0-A1A10%';
and returns this:
date_code new_bin_id
2010-06-13 P-0-A1A10
2010-06-17 P-0-A1A10
2010-06-24 P-0-A1A10
2010-06-30 P-0-A1A10
2010-07-15 P-0-A1A10
2010-07-30 P-0-A1A10
2010-08-04 P-0-A1A10
2010-08-27 P-0-A1A10
2010-06-10 P-0-A1A100
2010-06-30 P-0-A1A100
2010-07-08 P-0-A1A100
2010-07-14 P-0-A1A100
2010-08-04 P-0-A1A100
2010-08-25 P-0-A1A100
2010-06-10 P-0-A1A102
2010-07-09 P-0-A1A102
2010-07-21 P-0-A1A102
2010-07-22 P-0-A1A102
2010-07-24 P-0-A1A102
2010-07-26 P-0-A1A102
2010-07-30 P-0-A1A102
2010-08-04 P-0-A1A102
2010-08-26 P-0-A1A102
2010-06-10 P-0-A1A104
2010-07-01 P-0-A1A104
2010-07-09 P-0-A1A104
2010-07-14 P-0-A1A104
2010-07-21 P-0-A1A104
2010-07-23 P-0-A1A104
2010-08-05 P-0-A1A104
2010-08-12 P-0-A1A104
2010-08-26 P-0-A1A104
2010-08-31 P-0-A1A104
2010-09-06 P-0-A1A104
2010-06-10 P-0-A1A106
2010-07-05 P-0-A1A106
2010-07-15 P-0-A1A106
2010-07-20 P-0-A1A106
2010-07-23 P-0-A1A106
2010-07-30 P-0-A1A106
2010-08-04 P-0-A1A106
2010-08-18 P-0-A1A106
2010-06-10 P-0-A1A108
2010-07-06 P-0-A1A108
2010-07-14 P-0-A1A108
2010-08-05 P-0-A1A108
2010-08-11 P-0-A1A108
I would like one query that looks at adhocs and finds the last count date of a location and finds out how many times it has been stowed to since that date.
The output should be something like this (note P-0-A1A104 has never been counted for accuracy):
scannable_id Last count stows
P-0-A1A10 2010-06-15 17:28:00 7
P-0-A1A100 2010-07-13 13:20:00 3
P-0-A1A102 2010-07-18 11:00:00 7
P-0-A1A104 11
P-0-A1A106 2010-09-08 18:10:00 0
P-0-A1A108 2010-08-17 12:56:00 0
Hopefully that is enough information
Thanks