I am sharing the sqls for query reports for GSTR 1 reports that I have used for my company. They are formatted to generate CSV as per the XLS sheet of GSTR1.
GSTR 1 - B2B
select
xta.gstin as "GST Number:Text:130",
xta.name as "Invoice Number:Link/Sales Invoice:200",
xta.posting_date as "Date:Date:120" ,
xta.total as "Taxable Value:Float:120",
CAST(LPAD(xta.gst_state_number,2,'0') AS CHAR) as "Place Of Supply:Text:150",
xta.reverse_charge as "Reverse Charge:Text:20",
xta.invoice_type as "Invoice Type:Text:120",
xta.ecommerce_gstin as "E-Commerce GSTIN:Text:130",
xta.rate as "Rate:Float:130",
xta.taxable_value as "Taxable Value:Float:130",
(xta.taxable_value*xta.rate/100) as "Tax:Float:130",
0 as "Cess:Float:120",
xta.month,
xta.account_head
from
(
select
ta.gstin ,
tsi.name ,
tsi.posting_date ,
tsi.total ,
ta.state ,
ta.gst_state_number,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,
(case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0
THEN (select sum(tax_rate) as tax_rate from `tabItem Tax` where `tabItem Tax`.parent = tsit.item_code and LEFT(`tabItem Tax`.tax_type,6)="IGST -")
else (select sum(rate) as tax_rate from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
LEFT(tstc.account_head,6) in ("IGST -","SGST -","CGST -")) END)
as rate,
(tsit.net_amount) as "taxable_value",
Left(tstc.account_head,4) as "account_head",
Mid(tsi.posting_date,6,2) as "month"
from `tabSales Invoice Item` as tsit
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent
inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name
where
tsi.docstatus=1 and
Left(tstc.account_head,6) in ("CGST -","IGST -")
and ta.gstin <> "NA"
) as xta where xta.month=07 order by xta.posting_date,xta.name ;
B2Cl
select
xta.name as "Invoice Number:Link/Sales Invoice:200",
xta.posting_date as "Date:Date:120" ,
xta.grand_total as "Invoice Value:Float:120",
case
when xta.state="JAMMU AND KASHMIR" Then "01"
when xta.state="HIMACHAL PRADESH" Then "02"
when xta.state="PUNJAB" Then "03"
when xta.state="CHANDIGARH" Then "04"
when xta.state="UTTARAKHAND" Then "05"
when xta.state="HARYANA" Then "06"
when xta.state="DELHI" Then "07"
when xta.state="RAJASTHAN" Then "08"
when xta.state="UTTAR PRADESH" Then "09"
when xta.state="BIHAR" Then "10"
when xta.state="SIKKIM" Then "11"
when xta.state="ARUNACHAL PRADESH" Then "12"
when xta.state="NAGALAND" Then "13"
when xta.state="MANIPUR" Then "14"
when xta.state="MIZORAM" Then "15"
when xta.state="TRIPURA" Then "16"
when xta.state="MEGHLAYA" Then "17"
when xta.state="ASSAM" Then "18"
when xta.state="WEST BENGAL" Then "19"
when xta.state="JHARKHAND" Then "20"
when xta.state="ODISHA" Then "21"
when xta.state="CHATTISGARH" Then "22"
when xta.state="MADHYA PRADESH" Then "23"
when xta.state="GUJARAT" Then "24"
when xta.state="DAMAN AND DIU" Then "25"
when xta.state="DADRA AND NAGAR HAVELI" Then "26"
when xta.state="MAHARASHTRA" Then "27"
when xta.state="ANDHRA PRADESH(BEFORE DIVISION)" Then "28"
when xta.state="KARNATAKA" Then "29"
when xta.state="GOA" Then "30"
when xta.state="LAKSHWADEEP" Then "31"
when xta.state="KERALA" Then "32"
when xta.state="TAMIL NADU" Then "33"
when xta.state="PUDUCHERRY" Then "34"
when xta.state="ANDAMAN AND NICOBAR ISLANDS" Then "35"
when xta.state="TELANGANA" Then "36"
when xta.state="ANDHRA PRADESH" Then "37"
END as "Place Of Supply:Text:150",
xta.rate as "Rate:Float:120",
xta.taxable_value as "Taxable Value:Float:120",
0 as "Cess:Float:120",
xta.ecommerce_gstin as "E-Commerce GSTIN:Text:130",
xta.month as "Month",
xta.account_head as "Account Head"
from
(
select
ta.gstin ,
tsi.name ,
tsi.posting_date ,
tsi.total ,
tsi.grand_total,
ta.state ,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,
(case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0
THEN (select sum(tax_rate) as tax_rate from `tabItem Tax` where `tabItem Tax`.parent = tsit.item_code and LEFT(`tabItem Tax`.tax_type,6)="IGST -")
else (select sum(rate) as tax_rate from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
LEFT(tstc.account_head,6) in ("IGST -","SGST -","CGST -")) END)
as rate,
(tsit.net_amount) as "taxable_value",
Left(tstc.account_head,4) as "account_head",
Mid(tsi.posting_date,6,2) as "month"
from `tabSales Invoice Item` as tsit
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent
inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name
where
tsi.docstatus=1 and
Left(tstc.account_head,6) in ("CGST -","IGST -") and
ta.gstin = "NA" and tsi.grand_total > 250000
) as xta where xta.month=07 order by xta.posting_date,xta.name ;
B2CS
select
if (xta.ecommerce_gstin is NULL,"OE","E") as "Type:Text:25",
case
when xta.state="JAMMU AND KASHMIR" Then "01"
when xta.state="HIMACHAL PRADESH" Then "02"
when xta.state="PUNJAB" Then "03"
when xta.state="CHANDIGARH" Then "04"
when xta.state="UTTARAKHAND" Then "05"
when xta.state="HARYANA" Then "06"
when xta.state="DELHI" Then "07"
when xta.state="RAJASTHAN" Then "08"
when xta.state="UTTAR PRADESH" Then "09"
when xta.state="BIHAR" Then "10"
when xta.state="SIKKIM" Then "11"
when xta.state="ARUNACHAL PRADESH" Then "12"
when xta.state="NAGALAND" Then "13"
when xta.state="MANIPUR" Then "14"
when xta.state="MIZORAM" Then "15"
when xta.state="TRIPURA" Then "16"
when xta.state="MEGHLAYA" Then "17"
when xta.state="ASSAM" Then "18"
when xta.state="WEST BENGAL" Then "19"
when xta.state="JHARKHAND" Then "20"
when xta.state="ODISHA" Then "21"
when xta.state="CHATTISGARH" Then "22"
when xta.state="MADHYA PRADESH" Then "23"
when xta.state="GUJARAT" Then "24"
when xta.state="DAMAN AND DIU" Then "25"
when xta.state="DADRA AND NAGAR HAVELI" Then "26"
when xta.state="MAHARASHTRA" Then "27"
when xta.state="ANDHRA PRADESH(BEFORE DIVISION)" Then "28"
when xta.state="KARNATAKA" Then "29"
when xta.state="GOA" Then "30"
when xta.state="LAKSHWADEEP" Then "31"
when xta.state="KERALA" Then "32"
when xta.state="TAMIL NADU" Then "33"
when xta.state="PUDUCHERRY" Then "34"
when xta.state="ANDAMAN AND NICOBAR ISLANDS" Then "35"
when xta.state="TELANGANA" Then "36"
when xta.state="ANDHRA PRADESH" Then "37"
END AS "STATE:TEXT:50",
xta.rate as "Rate:Float:120",
xta.total as "Taxable Value:Float:120",
0 as "Cess Amount:Float:120",
xta.ecommerce_gstin as "E-Commerce GSTIN:Text:130",
Mid(xta.posting_date,6,2) as "Month",
xta.posting_date as "Date:Date:120",
xta.name as "Invoice Number:Link/Sales Invoice:120"
from
(
select
ta.gstin ,
tsi.name ,
tsi.posting_date ,
tsi.total ,
ta.state ,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,
(case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0
THEN (select sum(tax_rate) as tax_rate from `tabItem Tax` where `tabItem Tax`.parent = tsit.item_code and LEFT(`tabItem Tax`.tax_type,6)="IGST -")
else (select sum(rate) as tax_rate from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
LEFT(tstc.account_head,6) in ("IGST -","SGST -","CGST -")) END)
as rate,
(tsit.net_amount) as "taxable_value",
Left(tstc.account_head,4) as "account_head",
Mid(tsi.posting_date,6,2) as "month"
from `tabSales Invoice Item` as tsit
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent
inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name
where
tsi.docstatus=1 and
Left(tstc.account_head,6) in ("CGST -","IGST -")
) as xta where xta.month=07 order by xta.posting_date,xta.name ;
HSN
select
xta.gst_hsn_code as "HSN:Text:130",
xta.item_code,
xta.item_name as "Description:Text:130",
CASE WHEN xta.uom in ("BAG","CTN","MTS","TGM","BAL","DOZ","NOS","THD","BDL","DRM","PAC",
"TON","BKL","GGK""PCS","TUB","BOU","GMS","PRS-PAIRS","UGS","BOX","GRS","QTL","UNT","BTL","GYD","ROL","YDS","BUN","KGS","SET","OTH","CAN","KLR","SQF","CBM",
"KME","SQM","CCM","MLT","SQY","CMS","MTR","TBS") THEN (select concat(name,"-",uom_name) from `tabUOM` where name=xta.uom)
ELSE "OTH-Others" END AS "UQM:Text:120",
sum(xta.qty) as "Quantity:Float:100",
sum(xta.net_amount*(1+xta.rate/100)) as "Total Value:Float:130",
sum(xta.net_amount) as "Total Taxable Value:Float:130",
sum(if(xta.account_head="IGST -",(xta.net_amount*xta.rate/100),0)) as "Integrated Tax Amount:Float:130",
sum(if(xta.account_head="CGST -",(xta.net_amount*xta.rate/100),0)) as "Total Central Tax:Float:130",
sum(if(xta.account_head="CGST -",(xta.net_amount*xta.rate/100),0)) as "Total State/UT Tax:Float:130",
0 as "Total Cess:Float:120",
Mid(xta.posting_date,6,2) as "Month",
LEFT(xta.posting_date,4) as "Year"
from
(
select
ta.gstin ,
tsi.name ,
tsit.item_code,
tsit.gst_hsn_code,
tsit.item_name,
tsit.uom,
tsit.qty,
tsit.net_amount,
tsi.posting_date ,
tsi.total ,
ta.state ,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,
(case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0
THEN (select (tax_rate) as tax_rate from `tabItem Tax`
where `tabItem Tax`.parent = tsit.item_code and
(LEFT(`tabItem Tax`.tax_type,6) in ("IGST -","CGST -") )and `tabItem Tax`.tax_type=tstc.account_head LIMIT 1
)
else (select (rate) as tax_rate from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
LEFT(`tabSales Taxes and Charges`.account_head,6) in ("IGST -","CGST -") LIMIT 1) END)
as rate,
(tsit.net_amount) as "taxable_value",
Left(tstc.account_head,6) as "account_head",
Mid(tsi.posting_date,6,2) as "month"
from `tabSales Invoice Item` as tsit
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent
inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name
where
tsi.docstatus=1 and
Left(tstc.account_head,6) in ("CGST -","IGST -") and
CAST(LEFT(tsi.posting_date,4) AS UNSIGNED) = if (MID(curdate(),6,2)="01",CAST(LEFT(curdate(),4) AS UNSIGNED)-1,CAST(LEFT(curdate(),4) AS UNSIGNED)) and
CAST(MID(tsi.posting_date,6,2) AS UNSIGNED) = if (MID(curdate(),6,2)=01,"12",CAST(MID(curdate(),6,2) AS UNSIGNED)-2)
) as xta group by concat(xta.gst_hsn_code,xta.item_name) order by xta.posting_date,xta.name ;
You may need to format the sql’s as per your requirements
-Amit