CODEWITHSUNDEEP
sqlms-access-2010
HKImpact
HKImpact

Reputation: 620

Calculating Time per person

I am trying to build a query for a friend and I'm not 100% sure what I need to do yet. I need to calculate the total time per person over a group of tables.

The table setup looks somewhat like this....

Participant(participant_id)

TableA(participant_id, time1, time2, time3)

TableB(participant_id, time1, time2, time3)

TableC(participant_id, time1, time2, time3)

A participant may or may not be in every table either. For example Participant 1 may have a record in Table A, but not B or C. Also time1, time2, or time3 may be NULL.

With this given info how would I calculate the total time per person across all the tables?

(tableA.time1 + tableA.time2 + tableA.time3) + (tableB.time1 + tableB.time2 + tableB.time3) + (tableC.time1 + tableC.time2 + tableC.time3)

I use more TSQL than anything, but have lots to learn. Thanks ahead of time for anyone that can give me some useful tips.

Upvotes: 1

Views: 68

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123809

It sounds like you need a UNION query to unpivot and merge the tables, and then aggregate over [participant_id], something like the following (tested in Access 2010):

SELECT participant_id, SUM(time_value) AS total_time
FROM
    (
        SELECT participant_id, time1 AS time_value FROM TableA
        UNION ALL
        SELECT participant_id, time2 AS time_value FROM TableA
        UNION ALL
        SELECT participant_id, time3 AS time_value FROM TableA
        UNION ALL
        SELECT participant_id, time1 AS time_value FROM TableB
        UNION ALL
        SELECT participant_id, time2 AS time_value FROM TableB
        UNION ALL
        SELECT participant_id, time3 AS time_value FROM TableB
        UNION ALL
        SELECT participant_id, time1 AS time_value FROM TableC
        UNION ALL
        SELECT participant_id, time2 AS time_value FROM TableC
        UNION ALL
        SELECT participant_id, time3 AS time_value FROM TableC
    ) AS u
GROUP BY participant_id

Edit re: comment

If you "hit a wall" on the number of UNIONS allowed in the subquery and start getting a "Query is too complex" error then a workaround would be to

  • create a new empty table named [TimeValues] with columns [participant_id] and [time_value]

  • load up the temporary table in batches with queries like this

INSERT INTO TimeValues (participant_id, time_value)
SELECT participant_id, time_value FROM
    (
        SELECT participant_id, time1 AS time_value FROM TableA
        UNION ALL
        SELECT participant_id, time2 AS time_value FROM TableA
        UNION ALL
        SELECT participant_id, time3 AS time_value FROM TableA
        UNION ALL
        SELECT participant_id, time1 AS time_value FROM TableB
        UNION ALL
        SELECT participant_id, time2 AS time_value FROM TableB
        UNION ALL
        SELECT participant_id, time3 AS time_value FROM TableB
    ) AS u

INSERT INTO TimeValues (participant_id, time_value)
SELECT participant_id, time_value FROM
    (
        SELECT participant_id, time1 AS time_value FROM TableC
        UNION ALL
        SELECT participant_id, time2 AS time_value FROM TableC
        UNION ALL
        SELECT participant_id, time3 AS time_value FROM TableC
        UNION ALL
        SELECT participant_id, time1 AS time_value FROM TableD
        UNION ALL
        SELECT participant_id, time2 AS time_value FROM TableD
        UNION ALL
        SELECT participant_id, time3 AS time_value FROM TableD
    ) AS u
  • then run the aggregation query on the temporary table.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can do this with left joins and a lot of addition in the select clause:

select p.particpant_id,
       (nz(a.time1) + nz(a.time2) + nz(a.time3) +
        nz(b.time1) + nz(b.time2) + nz(b.time3) +
         . . .
       ) /
       (iif(a.time1 is null, 0, 1) + iff(a.time2 is null, 0, 1) + iif(a.time3 is null, 0, 1) +
        iif(b.time1 is null, 0, 1) + iff(b.time2 is null, 0, 1) + iif(b.time3 is null, 0, 1) +
        . . .
       )
from (participant p left join
      tableA a
      on p.participant_id = a.participant_id
     ) left join
     tableB b
     on p.participant_id = b.participant_id
     . . . ;

Upvotes: 0

Related Questions