# GSTR-1 HSN Summary SQL Query Generator

> Source: https://netray.co/tools/gstr-1-hsn-summary-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Produces SQL for the HSN-wise summary of outward supplies and a data quality check for missing HSN, using table and column names you provide.

## Category

regional

## Region

India

## Audience

- ERP developer
- GST accountant
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Invoice header table

**Key:** header_table

**Type:** text

**Default:** sales_invoice_header

#### Invoice line table

**Key:** line_table

**Type:** text

**Default:** sales_invoice_line

#### From date (YYYY-MM-DD)

**Key:** from_date

**Type:** date

**Default:** 2026-09-01

#### To date (YYYY-MM-DD)

**Key:** to_date

**Type:** date

**Default:** 2026-09-30

#### Document type column

**Key:** doc_type_col

**Type:** text

**Default:** doc_type

#### Buyer GSTIN column

**Key:** buyer_gstin_col

**Type:** text

**Default:** buyer_gstin

**Blocks:**

#### 1. HSN-wise summary of outward supplies

**Body:** 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 {{header_table}} h
JOIN {{line_table}} l ON l.invoice_id = h.invoice_id
WHERE h.invoice_date >= '{{from_date}}'
  AND h.invoice_date <= '{{to_date}}'
  AND h.{{doc_type_col}} = 'INV'
GROUP BY l.hsn_code, l.uqc, l.gst_rate
ORDER BY l.hsn_code;

#### 2. Lines with blank or short HSN

**Body:** SELECT h.invoice_no, l.line_no, l.item_code, l.hsn_code
FROM {{header_table}} h
JOIN {{line_table}} l ON l.invoice_id = h.invoice_id
WHERE h.invoice_date >= '{{from_date}}'
  AND h.invoice_date <= '{{to_date}}'
  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;

#### 3. B2B versus B2C split of taxable value

**Body:** SELECT CASE WHEN h.{{buyer_gstin_col}} IS NULL OR h.{{buyer_gstin_col}} = '' THEN 'B2C' ELSE 'B2B' END AS supply_class,
       COUNT(DISTINCT h.invoice_id) AS invoices,
       SUM(l.taxable_value) AS taxable_value
FROM {{header_table}} h
JOIN {{line_table}} l ON l.invoice_id = h.invoice_id
WHERE h.invoice_date >= '{{from_date}}'
  AND h.invoice_date <= '{{to_date}}'
  AND h.{{doc_type_col}} = 'INV'
GROUP BY CASE WHEN h.{{buyer_gstin_col}} IS NULL OR h.{{buyer_gstin_col}} = '' THEN 'B2C' ELSE 'B2B' END;

**Notes:** The table and column names in the queries (invoice_id, hsn_code, taxable_value and so on) are generic placeholders, not the names of any particular ERP. Map them to your billing tables. LEN and LTRIM/RTRIM are SQL Server syntax; use LENGTH and TRIM in Oracle, PostgreSQL or MySQL. Credit notes need a separate pass with negative sign handling. Read-only queries, but test on a copy first.

## Content

**Intro:** GSTR-1 asks for an HSN-wise summary of outward supplies with quantity, value and tax for each code and rate. Most ERPs can produce it, but a custom SQL query is useful when you reconcile the report to the books or when the standard report does not match the return.

**Sections:**

#### How to use the generator

**Body:** Enter your invoice header and line table names, the period, and the document type and buyer GSTIN columns. The tool fills the three queries. Run the first to produce the summary, the second to find lines that will fail HSN validation, and the third to check the B2B and B2C split against your return tables. Map the generic column names in the body to your own billing fields before running.

**Bullets:**

- Run on a reporting copy or read replica
- Check the date filter against your invoice date field
- Handle credit notes in a second query

#### How the summary is built

**Body:** The HSN summary groups invoice lines by HSN, unit of quantity and rate, and adds quantity, total value, taxable value and the tax amounts. Credit notes reduce the figures, and unit of quantity must follow the standard UQC list. The totals must agree with the taxable value and tax in the invoice tables of the return. A difference usually points to an amendment, a rate change in the month or a missing line.

#### Common mismatches

**Body:** Differences between the HSN summary and the invoice tables normally trace to three sources: invoices with blank HSN, free-of-charge or sample lines with no value, and rounding at invoice level versus line level. Another frequent cause is grouping by item description instead of HSN, which splits one code across rows. Fix at the source in the item master, not in the report.

#### ERP notes

**Body:** In SAP the data sits in billing document item tables joined to the material master tax data. In Oracle and Dynamics it is in the receivables or sales invoice lines. In Tally or Busy export the voucher lines. Whatever the system, make sure the HSN is stored on the transaction line at the time of billing, not looked up later, so the report is stable after master changes.

**Cta:** If you want a tested version of these queries on your ERP schema, with the reconciliation to the GSTR-1 tables, we can build and document it for your finance team.

**Faq:**

#### Is the HSN summary mandatory in GSTR-1?

**Answer:** Yes, the HSN-wise summary table is required in GSTR-1 and the portal validates the codes against its master. The number of digits required depends on turnover and the current notification. Blank or invalid codes can block filing.

#### Why does the portal reject my UQC?

**Answer:** The portal accepts only codes from the standard Unit Quantity Code list, such as NOS, KGS, MTR and PCS. Local units like EA or Each from the ERP must be mapped to the standard code before the summary is built.

#### Do I include credit notes?

**Answer:** Yes, credit notes reduce the quantity, value and tax in the summary and debit notes increase it. Run a second pass for notes with the sign reversed, then combine the two result sets before you compare with the return.

#### Can I run this on SAP HANA or Oracle?

**Answer:** The pattern works, but the table names, the string functions and the date handling differ. Replace LEN and LTRIM with the equivalent functions in your database and use your actual billing table names.

## Search terms

gstr-1 hsn summary sql, hsn wise summary query, gst hsn summary from erp, gstr-1 table 12 query, sql query for gst hsn report, hsn summary report erp, blank hsn invoice check, gst outward supply sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json