SHARE
TWEET

Untitled

a guest Jan 18th, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. refund_code    refund_amount    month_group
  2. -----------    -------------    -----------
  3. ref1               10           january
  4. ref2               20           january
  5. ref3               30           january
  6. ref1               40           february
  7. ref2               50           february
  8. ref3               60           february
  9.    
  10. refund_code    refund_amount    month_group    month_total  
  11. -----------    -------------    -----------    -----------  
  12. ref1               10           january                 60  
  13. ref2               20           january                 60  
  14. ref3               30           january                 60  
  15. ref1               40           february               150  
  16. ref2               50           february               150  
  17. ref3               60           february               150
  18.    
  19. SELECT mr.month_group,
  20.     bd.transaction_code AS refund_code,
  21.     SUM(bd.extended) AS refund_amount
  22. FROM   billing_details AS bd
  23.     LEFT JOIN monthly_ranges AS mr
  24.         ON ( bd.entry_date BETWEEN mr.start_date AND mr.end_date )
  25. WHERE  bd.transaction_code IN ( 'REFPRI', 'REFSEC', 'REFPT', 'REFREQPRI' )
  26.     AND bd.entry_date >= '2012-01-05'
  27. GROUP BY mr.month_group, bd.transaction_code
  28. ORDER BY mr.month_group, bd.transaction_code
  29.    
  30. SELECT mr.month_group,
  31.     SUM(bd.extended) AS refund_amount
  32. FROM   billing_details AS bd
  33.     LEFT JOIN monthly_ranges AS mr
  34.         ON ( bd.entry_date BETWEEN mr.start_date AND mr.end_date )
  35. WHERE  bd.transaction_code IN ( 'REFPRI', 'REFSEC', 'REFPT', 'REFREQPRI' )
  36.     AND bd.entry_date >= '2012-01-05'
  37. GROUP BY mr.month_group
  38. ORDER BY mr.month_group
  39.    
  40. SELECT m.refund_code, m.refund_amount, m.month_group, t.month_total
  41.  FROM (your refund query above) m
  42.  JOIN (your total query above) t
  43.    ON m.month_group = t.month_group
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top