I’m trying to create sub-records with aggregations mined from existing records in our data. I have a table that lists records, with a one to many relationship to another that lists actions on each record. Record table looks like this:
Key OpenDate LastUpdate
aa 1/1/2015 1/14/2015
bb 1/3/2015 1/15/2015
Action table looks like this:
Key Date Action
aa 1/1/2015 Working
aa 1/4/2015 Escalated
aa 1/5/2015 Done
aa 1/6/2015 Working
aa 1/7/2015 Done
aa 1/13/2015 Done
aa 1/14/2015 Working
bb 1/3/2015 Working
bb 1/4/2015 Working
bb 1/5/2015 Escalated
bb 1/6/2015 Working
bb 1/7/2015 Done
bb 1/13/2015 Working
bb 1/15/2015 Done
I want to be able to create a row for each time a record gets 'done' that notes the start and end of that cycle, and counts some items within that range:
Key SubID DateBegin DateEnd #Actions #Escalations
aa 1 1/1/2015 1/5/2015 3 1
aa 2 1/6/2015 1/7/2015 2 0
aa 3 1/13/2015 1/13/2015 1 0
aa 4 1/14/2015 null 1 0
bb 1 1/3/2015 1/7/2015 5 1
bb 2 1/13/2015 1/15/2015 2 0
Basically, the logic is that a sub-record ends when the Action value = ‘Done’, and a new sub-record begins on any subsequent action (and also the very first action).
I was shown a solution that works for only one record's data here , but more than one is giving me problems. I am working with SQL Server 2008.
UPDATE -- I've got multiple records returning but the date data seems incorrect - not sure it's getting what it is supposed to:
SELECT Key, Cycles.CYCLE_BEGIN_DATE, Cycles.CYCLE_END_DATE, Cycles.NUM_ACTIONS_IN_CYCLE
FROM Records
FULL OUTER JOIN
(select e.Key, min(Date) as CYCLE_BEGIN_DATE,
max(case when Action = 'Done') then Date end) as CYCLE_END_DATE,
count(*) as NUM_ACTIONS_IN_CYCLE
from (select Key, Action, rowID = ROW_NUMBER() OVER (PARTITION BY Key ORDER BY Date asc), Date
from Actions
) e
outer apply
(select count(*) as grp
from (SELECT Key, rowID = ROW_NUMBER() OVER (PARTITION BY Reason_Key ORDER BY Date asc), Date, Action
FROM Actions
) e2
where e2.Date < e.Date and e2.Action = 'Done' and e.Reason_Key = e2.Reason_Key
) e2
group by e.Reason_Key, e2.grp
) CYCLES
on Records.Key = Cycles.Key
Aucun commentaire:
Enregistrer un commentaire