Click here to Skip to main content
14,921,764 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I have two tables

1)Ledgers
Srno (PK)
Ledger_name
TAXNo
Address
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

1 solution

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
   
Comments
prathameshpitale 12-Sep-11 5:16am
   
thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900