0

I have a table, lets call it as DummyTable. It has fields - price_effective, store_invoice_updated_date, bag_status, gstin_code.

Now I want to get the output which does a group by of - month, year from the field store_invoice_updated_date and gstin_code.

Along with that group by I wanna do thse calculations -

Sum of price_effective as 'forward_price_effective' if the bag_status is other than 'return_accepted' or 'rto_bag_accepted'. Dont know how to do an exclude here i.e. using a filter in annotate

Sum of price effective as 'return_price_effective' if the bag_status is 'return_accepted' or 'rto_bag_accepted'.

A field 'total_price' that subtracts the 'return_price_effective' from 'forward_price_effective'.

I have formulated this query, which doesn't work

from django.db.models.functions import TruncMonth
from django.db.models import Count, Sum, When, Case, IntegerField

DummyTable.objects.annotate(month=TruncMonth('store_invoice_updated_date'), year=TruncYear('store_invoice_updated_date')).annotate(forward_price_effective=Sum(Case(When(bag_status__in=['delivery_done']), then=Sum(forward_price_effective)), output_field=IntegerField()), return_price_effective=Sum(Case(When(bag_status__in=['return_accepted', 'rto_bag_accepted']), then=Sum('return_price_effective')), output_field=IntegerField())).values('month','year','forward_price_effective', 'return_price_effective', 'gstin_code')
Umair Mohammad
  • 4,489
  • 2
  • 20
  • 34
Arjunsingh
  • 703
  • 9
  • 22
  • Did you checked this https://docs.djangoproject.com/en/2.1/ref/models/querysets/#extra ? – Umair Mohammad Sep 20 '18 at 05:30
  • Thought of evaluating it without using extra – Arjunsingh Sep 20 '18 at 05:34
  • 'I have formulated this query, which doesn't work' Can you please give us the error or the wrong result that you get with this query – Mehak Sep 20 '18 at 08:06
  • There are many when I change the query. In the given query this is the error: name 'forward_price_effective' is not defined. – Arjunsingh Sep 20 '18 at 08:50
  • Can you please try [this](https://stackoverflow.com/a/40418387/9223271) answer here. I was facing a similar problem and this solved the issue of KeyError that I assume you're having. – Mehak Sep 20 '18 at 09:20

1 Answers1

0

Solved it by multiple querysets. Just couldnt find out a way to appropriately use 'Case' with 'When' with 'filter' and 'exclude'.

basic_query = BagDetails.objects.filter(store_invoice_updated_date__year__in=[2018]).annotate(month=TruncMonth('store_invoice_updated_date'), year=TruncYear('store_invoice_updated_date') ).values('year', 'month', 'gstin_code', 'price_effective', 'company_id', 'bag_status')


forward_bags = basic_query.exclude(bag_status__in=['return_accepted', 'rto_bag_accepted']).annotate(
        Sum('price_effective')).values('year', 'month', 'gstin_code', 'price_effective', 'company_id')

return_bags = basic_query.filter(bag_status__in=['return_accepted', 'rto_bag_accepted']).annotate(
        Sum('price_effective')).values('month', 'gstin_code', 'price_effective', 'company_id')
Arjunsingh
  • 703
  • 9
  • 22