[Answered ]-Django aggregate field, but filtered by date

by

1👍

✅

You can filter for example with:

Employee.objects.filter(
    shift__start__range=('2022-01-01', '2022-01-31')
).annotate(
    total=Sum('shift__dur')
)

This will only show shifts for January 2022. Furthermore Employees without any shift for January 2022 will be filtered out.

You can also obtain data per week, for example with:

from django.db.models.functions import TruncWeek

Shift.objects.values(
    'employee_id',
    week=TruncWeek('start')
).annotate(
    total=Sum('dur')
).order_by('employee_id', 'week')

This will construct a QuerySet with:

<QuerySet [
    {'employee_id': 1, 'week': date(2022, 1, 3), 'total': Decimal('14.25')},
    {'employee_id': 1, 'week': date(2022, 1, 10), 'total': Decimal('13.02')},
    {'employee_id': 1, 'week': date(2022, 1, 17), 'total': Decimal('17.89')},
    {'employee_id': 2, 'week': date(2022, 1, 3), 'total': Decimal('57.3')},
    {'employee_id': 2, 'week': date(2022, 1, 17), 'total': Decimal('18.30')}
]>

This thus makes an aggregate per empoloyee primary key and per week (the week is truncated to start on monday), and the total lists the total of the durs of the shifts. If for a given employee_id–week combination, there are no shifts, then this will not appear in the queryset. You thus will need to do some post-processing if you want to introduce zeros for these weeks.

👤willeM_ Van Onsem

Leave a comment