Query Problem Oracle.

Question

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 11-Sep-11 21:55pm

prathameshpitale

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MaulikDusara · Accepted Answer · 2011-09-11T22:07:00

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