MS Access SQL - Capture changes in status over time
I have an Access 2007 database that tracks document progression through time. The progression goes like:
- Created
- Sent for Review
- Reviewed
- Sent for Approval
- Approved
I've created a history table for changes in document status with columns like this:
hist_id doc_id month step status datestamp
I've created a query that returns statuses for the end of the month, like so:
SELECT doc_id, month, step, status, datestamp
FROM hist
WHERE (((hist.datestamp) In
(
Select Top 1 h.[datestamp]
From hist as h
Where h.[doc_id] = hist.[doc_id] and h.[month] = hist.[month]
Order By h.[datestamp] DESC))
)
ORDER BY month, doc_id DESC;
to get....
doc_id month step status datestamp
a 2011-01 2 sent for review 18/01/2011
b 2011-02 1 created 01/02/2011
a 2011-02 3 reviewed 19/02/2011
c 2011-03 1 created 07/03/2011
d 2011-03 1 created 08/03/2011
e 2011-06 1 created 14/06/2011
f 2011-07 1 created 05/07/2011
g 2011-07 4 sent for approval 18/07/2011
h 2011-07 2 sent for review 14/07/2011
f 2011-08 3 reviewed 29/08/2011
g 2011-08 5 approved 17/08/2011
h 2011-08 1 created 10/08/2011
e 2011-09 3 reviewed 17/09/2011
But what I really need is my query to also return documents in months for which the status hasn't changed. For example, document a
's status became reviewed
on 19/02/2011
, but that's the last time it shows up in the results above. It should actually show up in every month afterward as reviewed
until it later becomes sent for approval
.
So I'm trying to modify my query (or query the above query) to provide results like below...
doc_id month step status datestamp
a 2011-01 2 sent for review 18/01/2011
a 2011-02 3 reviewed 19/02/2011
b 2011-02 1 created 01/02/2011
a 2011-03 3 reviewed 19/02/2011
b 2011-03 1 created 01/02/2011
c 2011-03 1 created 07/03/2011
d 2011-03 1 created 08/03/2011
a 2011-04 3 reviewed 19/02/2011
b 2011-04 1 created 01/02/2011
c 2011-04 1 created 07/03/2011
d 2011-04 1 created 08/03/2011
a 2011-05 3 reviewed 19/02/2011
b 2011-05 1 created 01/02/2011
c 2011-05 1 created 07/03/2011
d 2011-05 1 created 08/03/2011
a 2011-06 3 reviewed 19/02/2011
b 2011-06 1 created 01/02/2011
c 2011-06 1 created 07/03/2011
d 2011-06 1 created 08/03/2011
e 2011-06 1 created 14/06/2011
a 2011-07 3 reviewed 19/02/2011
b 2011-07 1 created 开发者_运维技巧 01/02/2011
c 2011-07 1 created 07/03/2011
d 2011-07 1 created 08/03/2011
e 2011-07 1 created 14/06/2011
f 2011-07 1 created 05/07/2011
g 2011-07 4 sent for appr 18/07/2011
h 2011-07 2 sent for rev 14/07/2011
a 2011-08 3 reviewed 19/02/2011
b 2011-08 1 created 01/02/2011
c 2011-08 1 created 07/03/2011
d 2011-08 1 created 08/03/2011
e 2011-08 1 created 14/06/2011
f 2011-08 3 reviewed 29/08/2011
g 2011-08 5 approved 17/08/2011
h 2011-08 1 created 10/08/2011
a 2011-09 3 reviewed 19/02/2011
b 2011-09 1 created 01/02/2011
c 2011-09 1 created 07/03/2011
d 2011-09 1 created 08/03/2011
e 2011-09 1 reviewed 17/09/2011
f 2011-09 3 reviewed 29/08/2011
g 2011-09 5 approved 17/08/2011
h 2011-09 1 created 10/08/2011
Thanks for your help... I really don't even know where to start here.
This works in SQL and doesn't use any special features, so should work in MS-ACCESS SQL as well.
First, you will need to create a table on months.
create table monthly (monthN char(7))
insert into monthly values('2011-01')
insert into monthly values('2011-02')
...
and populate it with all the months you need.
With that table built, the following query should return what you are looking for:
select d1.doc_id,d1.monthN,d1.step,d1.status,d1.dateStamp
from monthly m1
join docs d1 on d1.monthN=m1.monthN
union
select d2.doc_id,zz.monthN,d2.step,d2.status,d2.dateStamp
from docs d2
join
(
select aa.doc_id,aa.monthN,bb.EndM from
(
select yy.doc_id,yy.monthN from
(
select d3.doc_id,m2.monthN
from monthly m2
join (select distinct doc_id from docs) d3 on 1=1
) yy
left join docs xx on xx.doc_id=yy.doc_id and xx.monthN=yy.MonthN
where xx.hist_id is null
) aa
join (select doc_id,MIN(monthN) as startM,MAX(monthN) as EndM
from docs group by doc_id)
bb on bb.doc_id=aa.doc_id and aa.monthN>=bb.StartM
) zz
on zz.doc_id=d2.doc_id and zz.EndM=d2.monthN
order by d1.monthN,d1.doc_id
I would suggest running each inner query separately to help follow what is being done...
Thanks to some clues from Sparky's answer, I was able to piece together something that works for me.
Steps
- Create a
months
table to contain list of months like2011-08
Create a
month_range
query to get the range of months from actual documentsSELECT month_no FROM months WHERE month BETWEEN (minium month for hist) AND (max month for hist)
Do a cross product query with
month_range
andhist
tables, wherehist.month_no
<=month_range.month
The step above leaves a table with multiple status changes per month per document. Just GROUP BY on
month_no
anddoc_no
withmax(hist_id)
in the SELECT- INNER JOIN the result from above with the
hist
table usinghist_id
to get access to the status
The exact final query that I used looks like this....
SELECT xx.month_no, xx.swp, xx.hist_id, h.status
FROM
(
SELECT zz.month_no, zz.swp, Max(zz.hist_id) AS hist_id
FROM
(
SELECT * FROM month_range AS mr, hist AS h
WHERE h.[first_of_month] <= mr.[first_of_month]
) zz
GROUP BY zz.month_no, zz.swp
) xx
INNER JOIN hist as h ON xx.hist_id = h.hist_id;
精彩评论