15,904,638 members
See more:
Hi,
I have two tables

1)Ledgers
Srno (PK)
Ledger_name
TAXNo
2)Bill_Master
Bill_No (PK)
Leger_no (FK to Ledgers.srno)
GRANDTOTAL
VATPercentage
VATAmount

Sample data in Bill_Master
Bill_no Leger_No GRANDTOTAL VATPERCENTAGE VATAMOUNT
1 10 1000 5 50
2 10 165 12 19.8
5 10 5565 5 278.2
3 13 980 12 11.76
4 15 4566 5 228.3

I need to Generate Following Report

Ledger_Name TAX_NO GRANDTOTAL VAT5_AMOUNT VAT12_AMOUNT

Every time A bill is created VAT Percentage is Different for that Bill. It may be 5 or it may Be 12.
This report should display all the LEDGERS and sum(grandtotal) and sum(vatamount)

Output should be

Ledger_Name TAX_NO GRANDTOTAL VAT5_AMOUNT VAT12_AMOUNT
XYZ AAA 6730 328.2 19.8
ABC BBB 980 0 11.76
BBC CCC 4566 228.3 0

I tried using DECODE.. but did not help.

Can you please suggest me Query for this?
Posted

## Solution 1

Hi,

try below SQL

SQL
```SELECT L.Ledger_Name,L.TAXNo AS [TAX_NO],SUM(GRANDTOTAL) AS [GRANDTOTAL],
CASE WHEN VATPercentage =5 THEN SUM(VATAmount) ELSE 0 END AS [VAT5_Amount],
CASE WHEN VATPercentage = 12 THEN SUM(VATAMount)ELSE 0 END AS [VAT12_Amount]
FROM   Ledger L
INNER JOIN Bill_Master B ON  B.Ledger_No = L.Srno
GROUP BY
L.Ledger_Name,L.TAXNo,VATPercentage```