FillComp
Oct 10th, 2023
35
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH i_month AS
  2.   (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month
  3.    FROM invoice
  4.    GROUP BY invoice_month
  5.    ORDER BY invoice_month),
  6. inv_2011 AS
  7.   (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month,
  8.           COUNT(invoice_id) AS year_2011
  9.    FROM invoice
  10.    WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = 2011
  11.    GROUP BY invoice_month),
  12. inv_2012 AS
  13.   (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month,
  14.           COUNT(invoice_id) AS year_2012
  15.    FROM invoice
  16.    WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = 2012
  17.    GROUP BY invoice_month),
  18. inv_2013 AS
  19.   (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month,
  20.           COUNT(invoice_id) AS year_2013
  21.    FROM invoice
  22.    WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = 2013
  23.    GROUP BY invoice_month)
  24.  
  25.    
  26. SELECT i_month.invoice_month,
  27.        inv_2011.year_2011,
  28.        inv_2012.year_2012,
  29.        inv_2013.year_2013
  30. FROM
  31.   (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month
  32.    FROM invoice
  33.    GROUP BY invoice_month
  34.    ORDER BY invoice_month) AS i_month
  35.    
  36. LEFT JOIN
  37.   inv_2011 ON i_month.invoice_month = inv_2011.invoice_month
  38.    
  39. LEFT JOIN
  40.   inv_2012 ON i_month.invoice_month = inv_2012.invoice_month
  41.    
  42. LEFT JOIN
  43.   inv_2013 ON i_month.invoice_month = inv_2013.invoice_month;
Add Comment
Please, Sign In to add comment