GSTR-1 HSN Summary SQL Query Generator

SELECT l.hsn_code,
       MAX(l.item_description) AS description,
       l.uqc,
       l.gst_rate,
       SUM(l.quantity) AS total_quantity,
       SUM(l.line_total) AS total_value,
       SUM(l.taxable_value) AS taxable_value,
       SUM(l.igst_amount) AS integrated_tax,
       SUM(l.cgst_amount) AS central_tax,
       SUM(l.sgst_amount) AS state_tax,
       SUM(l.cess_amount) AS cess
FROM sales_invoice_header h
JOIN sales_invoice_line l ON l.invoice_id = h.invoice_id
WHERE h.invoice_date >= '2026-09-01'
  AND h.invoice_date <= '2026-09-30'
  AND h.doc_type = 'INV'
GROUP BY l.hsn_code, l.uqc, l.gst_rate
ORDER BY l.hsn_code;

SELECT h.invoice_no, l.line_no, l.item_code, l.hsn_code
FROM sales_invoice_header h
JOIN sales_invoice_line l ON l.invoice_id = h.invoice_id
WHERE h.invoice_date >= '2026-09-01'
  AND h.invoice_date <= '2026-09-30'
  AND (l.hsn_code IS NULL OR LTRIM(RTRIM(l.hsn_code)) = '' OR LEN(l.hsn_code) < 6)
ORDER BY h.invoice_no, l.line_no;

SELECT CASE WHEN h.buyer_gstin IS NULL OR h.buyer_gstin = '' THEN 'B2C' ELSE 'B2B' END AS supply_class,
       COUNT(DISTINCT h.invoice_id) AS invoices,
       SUM(l.taxable_value) AS taxable_value
FROM sales_invoice_header h
JOIN sales_invoice_line l ON l.invoice_id = h.invoice_id
WHERE h.invoice_date >= '2026-09-01'
  AND h.invoice_date <= '2026-09-30'
  AND h.doc_type = 'INV'
GROUP BY CASE WHEN h.buyer_gstin IS NULL OR h.buyer_gstin = '' THEN 'B2C' ELSE 'B2B' END;