Column wise month wise date range in query report

ERPNext Reports
,
1

I created a month wise query report and it really works.

    SELECT `tabSales Invoice Item`.`item_code` as `Item:Link/Item:150`,

SUM(IF(`tabSales Invoice`.`posting_date` between "2020-04-01" and "2020-04-30", amount, 0)) AS `Apr 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-05-01" and "2020-05-31", amount, 0)) AS `May 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-06-01" and "2020-06-30", amount, 0)) AS `June 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-07-01" and "2020-07-31", amount, 0)) AS `July 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-08-01" and "2020-08-31", amount, 0)) AS `Aug 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-09-01" and "2020-09-30", amount, 0)) AS `Sep 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-10-01" and "2020-10-31", amount, 0)) AS `Octo 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-11-01" and "2020-11-30", amount, 0)) AS `Nov 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-12-01" and "2020-12-31", amount, 0)) AS `Dec 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2021-01-01" and "2021-01-31", amount, 0)) AS `Jan 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2021-02-01" and "2021-02-28", amount, 0)) AS `Feb 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2021-03-01" and "2021-03-31", amount, 0)) AS `Mar 20-21::100`,

SUM(IF(`tabSales Invoice`.`posting_date` between "2020-04-01" and "2021-03-31", amount, 0)) AS `TOTAL 20-21::100`,

    FROM `tabSales Invoice Item`,`tabSales Invoice`
    where `tabSales Invoice Item`.`parent` = `tabSales Invoice`.`name` 
    and `tabSales Invoice`.docstatus = 1 

Group by `item_code`
1 Like