samedi 25 avril 2015

making MULTIPLE subgroup aggregations

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