Dispatched AMA Appeals

dispatched-ama-appeals.py

subq1=select([
  func.date_trunc('month', tasksT.columns['closed_at']).label('closed_month'),
  tasksT.c.appeal_type,
  tasksT.c.appeal_id
  ])\
  .where(and_(
    tasksT.c.type.in_(['BvaDispatchTask']),
    tasksT.c.status == 'completed',
    tasksT.c.closed_at > '2018-10-01 04:00:00'
  ))\
  .group_by(tasksT.c.appeal_id, tasksT.c.appeal_type, 'closed_month')\
  .order_by(tasksT.c.appeal_id)
dispatchesQ=subq1.alias('dispatchesByMonth')
stmt=select([func.to_char(dispatchesQ.c.closed_month, 'YYYY-mm').label('monthStr'),
  dispatchesQ.c.appeal_type,
  func.count().label('count')])\
  .group_by(text("1"), text("2"))\
  .order_by(text("1"), text("2"))

dispatched-ama-appeals.rb

span=Time.new(2018,10)..Time.new(2019,10)-1
BvaDispatchTask.where(status: :completed, closed_at: span, appeal_type: :Appeal).group(:appeal_id).count

dispatched-ama-appeals.sql

SELECT month, appeal_type, count(*)
FROM (
  SELECT
  DATE_TRUNC('month', closed_at) AS month
  , appeal_type ,appeal_id
  FROM tasks
  WHERE type IN ('BvaDispatchTask')
  AND status = 'completed'
  AND closed_at > '2018-10-01 04:00:00'
  GROUP BY appeal_id, appeal_type, month
) as subq
GROUP BY 1, 2
ORDER BY 1, 2