How to get Total Fee data of last 6 months by using date field

I have a model as below,

class Visits(models.Model):
visit_date = models.DateField(blank=False, null=False)
fees = models.DecimalField(null=False, blank=False,  max_digits=10, decimal_places=2)

I want to get data for last six months (including current month) total fees month wise

Example:

{
July : 750, 
June : 800,
May : 500,
April : 200,
March: 450,
Febraury: 310 }

How to get done this by having date field.?

CodePudding user response：

You can use annotate to get the month from visit_date, sort it, group by month and calculate the sums grouped by month.

from django.db.models import Sum
from django.db.models.functions import Extract

offset_date = '2022-02-01'  # All Visits after this date (included) will be used

fees_by_month = (Visits
                 .objects
                 .filter(visit_date__gte=offset_date)
                 .annotate(month=Extract('visit_date', 'month'))
                 .order_by('month')  # Important to get correct output
                 .values('month')    # Group by month
                 .annotate(sum=Sum('fees')))

resulting in

<QuerySet [{'month': 2, 'sum': 310}, {'month': 3, 'sum': 450}, {'month': 4, 'sum': 200}, {'month': 5, 'sum': 500}, {'month': 6, 'sum': 800}, {'month': 7, 'sum': 750}]>