Hello — I would like advice on how to aggregate some data.
## Purpose- The goal is to trace daily changes in workers' working hours.
## Data structure- Each record represents a user's single work session and contains: - **RECORD_ID**, **USER**, **START_TIME**, **END_TIME**, **DURATION**- A user can have multiple sessions in one day, so one user can have multiple records for the same calendar day.
Example:RECORD_ID | USER | START_TIME | END_TIME | DURATION1 | A | 12/23 10:00 | 12/23 12:00 | 1202 | A | 12/23 13:00 | 12/23 15:00 | 1203 | A | 12/24 10:00 | 12/24 11:00 | 604 | B | 12/23 10:00 | 12/23 12:00 | 1205 | B | 12/23 13:00 | 12/23 15:00 | 1206 | B | 12/24 10:00 | 12/23 12:00 | 1207 | B | 12/24 13:00 | 12/23 15:00 | 1208 | B | 12/24 16:00 | 12/23 17:00 | 60```
## Required aggregation steps- First, aggregate these records by **user + work date** to compute each user’s total working time per day.- Then, calculate the **average daily working time per organizational unit** (the organization each user belongs to) and trace that over time.
## Questions and constraints- When editing directly in the database, we would use a subquery to compute per-user daily totals and then join to organization data. - Is it possible to perform subquery-like processing when aggregating with Appian’s `queryRecordType`? In other words, can Appian perform the equivalent of the database subquery aggregation (group by user and date) before joining to organization and computing averages?- I tried creating a database VIEW that computes users’ daily totals, but I gave up because I could not create the relationship between the user and organization in Appian. The user–organization relationship data exists in a central database, and it is difficult to join that at VIEW creation time.
Could you advise:- Whether `queryRecordType` (or other Appian record/query features) can replicate the subquery aggregation pattern?- Recommended approaches or alternatives in Appian to produce per-user daily totals and then aggregate those by organization (for example, using multiple queries, CDT transformations, database views, or integration to the central user–organization DB)?- Any best practices for handling the central user–organization relationship when the relationship data is stored outside the record source?
Discussion posts and replies are publicly visible
You can easily query per user per day data using simple aggregation. I suggest to use the visual query editor to toy around with that feature.
Next, I suggest to connect to that central database using a synced record to relate internal used data to your org units. Then, you can use another aggregation call to fetch that combined data.
Hi Stefan,Thank you for your advice.But what I'd like to get is Avarage by Date as below.To get it, I need to aggreate Summary by User + Date.Can I edit it by the query editor?
RECORD_ID | USER | START_TIME | END_TIME | DURATION1 | A | 12/23 10:00 | 12/23 12:00 | 1202 | A | 12/23 13:00 | 12/23 15:00 | 1203 | A | 12/24 10:00 | 12/24 11:00 | 604 | B | 12/23 10:00 | 12/23 12:00 | 1205 | B | 12/23 13:00 | 12/23 15:00 | 1206 | B | 12/24 10:00 | 12/23 12:00 | 1207 | B | 12/24 13:00 | 12/23 15:00 | 1208 | B | 12/24 16:00 | 12/23 17:00 | 60
<Summary by User + Date>
RECORD_ID | USER | Date | DURATION1 | A | 12/23 | 2402 | A | 12/24 | 603 | B | 12/23 | 2404 | B | 12/24 | 180
<Average by Date>
RECORD_ID | Date | DURATION(Ave)1 | 12/23 | 2402 | 12/24 | 120
You will have to prepare the summarization while writing data. In my experience it is a good idea to create a separate record to hold data specific to support flexible reporting.
In your case, when writing a new row to your user-working-time record, calculate the sum and store it as one row per user per day to a separate record.
Then, use the aggregation feature in queryRecordtype to get the final numbers.
Hi Stefan,Thank you for your advice. I'll prepare the summarization table according to your advice.