Solved

Tabular format query

Posted on 2010-11-29
24
339 Views
Last Modified: 2012-05-10
Hi I have query as below


SELECT   POINTOFSALEBILLDETAIL.AMOUNT,
         POINTOFSALEBILLDETAIL.TAXAMOUNT,
         LEDGER.DESCRIPTION,
         POINTOFSALEBILL.BILLDATE,
         POINTOFSALEBILL.EMPLOYEE_ID,
         POINTOFSALEBILL.MEMBER_ID
  FROM   POINTOFSALEBILL, POINTOFSALEBILLDETAIL, LEDGER
 WHERE   (POINTOFSALEBILL.POINTOFSALEBILL_ID =
             POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID)
         AND (LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID)
         AND (POINTOFSALEBILL.FLAG = 0)

Attached excel with present output of query and required output. request experts to help me out
REQUEST.xls
0
Comment
Question by:GRChandrashekar
  • 11
  • 5
  • 5
  • +2
24 Comments
 
LVL 6

Expert Comment

by:anumoses
ID: 34230454
what help do you need here?
0
 

Author Comment

by:GRChandrashekar
ID: 34230471
I want the ouput in required format. Please see the sheet IN EXCEL named "REQUIRED"
0
 
LVL 6

Expert Comment

by:anumoses
ID: 34230474
LEDGER      
MEMBER DEBIT      
MEMBER TAX      
MEMBER TOTAL      
EMPLOYEE DEBIT      
EMPLOYEE TAX      
EMPLOYEE TOTAL

Are all these calculated fields?
0
 

Author Comment

by:GRChandrashekar
ID: 34230484
No only MEMBER TOTAL      EMPLOYEE TOTAL are calcualted rest are readily available in DB
0
 
LVL 6

Expert Comment

by:anumoses
ID: 34230505
POINTOFSALEBILL, POINTOFSALEBILLDETAIL, LEDGER
 Are these the tables?
If so give me the colums in corresponding tables
0
 

Author Comment

by:GRChandrashekar
ID: 34230513
YES these are tables
Columns are there in query I have written
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34230516
use case

select
...
case when employee_id is not null then amount else 0 end as employee_tax,
...

hope it helps...
0
 
LVL 6

Expert Comment

by:anumoses
ID: 34230561
SELECT  c.ledger,
              b.member_debit,
              b.member_tax,
              b.employee_debit,
              b.employee_tax
     from   pointofsalebill a, pointofsalebilldetail b, ledger c
  where   a.pointofsalebill_id = b.pointofsalebill_id
      and c.ledger_id = b.ledger_id
      and a.flag = 0
0
 

Author Comment

by:GRChandrashekar
ID: 34230578
both solutions are not working
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 34230591
Check out this thread of EE. Hope this helps!!
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 34230594
0
 

Author Comment

by:GRChandrashekar
ID: 34230669
That is my own post :) but this is differnet. I also want to group by Leger.description
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34230829
@GRChandrashekar,

can you explain for first few input records to get the output you wanted. Basically i did not understand on how to get that output. So can you explain in details on how to derive that output from those input records.

Thanks
0
 
LVL 6

Expert Comment

by:anumoses
ID: 34230994
Plese give us the table names on the specific column you need in the select query
0
 

Author Comment

by:GRChandrashekar
ID: 34236111
Hi I have query as below

@ nav_kum_v:
SELECT   POINTOFSALEBILLDETAIL.AMOUNT,
         POINTOFSALEBILLDETAIL.TAXAMOUNT,
         LEDGER.DESCRIPTION,
         POINTOFSALEBILL.BILLDATE,
         POINTOFSALEBILL.EMPLOYEE_ID,
         POINTOFSALEBILL.MEMBER_ID

The table POINTOFSALE BILL has Member_ID and Employee_ID and BillDate. POINTOFSALEBILLDETAIL table has got AMOUNT AND TAXAMOUNT for coresponding MEMBER_ID AND EMPLOYEE_ID and POINTOFSALEBILL IS FK TO BILLDETAIL

POINTOFSALEBILLDETAIL has LEDGER_ID which is FK to TABLE LEDGER. So I am taking LEDGER.DESCRIPTION.


0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34236206
ok. i understood a little more on what you need.

Can you please explain the below :

your present data has first 5 records as below...
453      10      Cpu      10/04/2010      3      
23      5      Cpu      10/04/2010      3      
53      6      Cpu      10/04/2010            93
34      8      Cpu      10/04/2010            93
425      36      Cpu      10/04/2010            93

required has the below 3 records for those 5 records...
Cpu      53      6      59      453      10      
Cpu      34      8      42      23      5      
Cpu      425      36      461      0      0      

How do you say that "453" and "10" has to come for the first record in the output. On what basis/logic do we need to arrive this ?

similarly, on what logic/basis do you say that you need "23" and "5" for the second record which has "34" and "8" as the values.

Thanks,
0
 

Author Comment

by:GRChandrashekar
ID: 34236241
Well I want sum (amount) and sum(taxamount) for MEMBER_ID to appear as member debit and member tax. same way sum(amount) and sum(taxamount) for EMPLOYEE_ID to appear as employee debit and employee tax and grouped by LEDGER.DESCRIPTION
0
 

Author Comment

by:GRChandrashekar
ID: 34236243
attached sheet may be wrong but my above ans is right
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34236266
I still do not understand what you need clearly. May be something of this sort. Try this and let me know for changes.

select description, member_id,
sum ( case when employee_id is null then amount else 0 end ) member_debit,
sum ( case when employee_id is null then taxamount else 0 end ) member_tax,
sum ( case when employee_id is null then amount else 0 end ) + sum ( case when employee_id is null then taxamount else 0 end ) member_total,
sum ( case when employee_id is not null then amount else 0 end ) employee_debit,
sum ( case when employee_id is not null then taxamount else 0 end ) employee_tax,
sum ( case when employee_id is not null then amount else 0 end ) +
sum ( case when employee_id is not null then taxamount else 0 end ) employee_total
from (
SELECT   POINTOFSALEBILLDETAIL.AMOUNT,
         POINTOFSALEBILLDETAIL.TAXAMOUNT,
         LEDGER.DESCRIPTION,
         POINTOFSALEBILL.BILLDATE,
         POINTOFSALEBILL.EMPLOYEE_ID,
         POINTOFSALEBILL.MEMBER_ID
  FROM   POINTOFSALEBILL, POINTOFSALEBILLDETAIL, LEDGER
 WHERE   (POINTOFSALEBILL.POINTOFSALEBILL_ID =
             POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID)
         AND (LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID)
         AND (POINTOFSALEBILL.FLAG = 0)
)
group by description, member_id

Thanks,
0
 

Author Comment

by:GRChandrashekar
ID: 34236292
yes we are almost there. see attached file. since the description is same, the ouput should come in one line only right? since it is grouped by desciption. This is the only problem
output.xls
0
 

Author Comment

by:GRChandrashekar
ID: 34236296
not necessary to display MEMBER_ID in output
0
 

Author Comment

by:GRChandrashekar
ID: 34236298
I think this is fine unless you have any other suggestion
select description ,
sum ( case when employee_id is null then amount else 0 end ) member_debit,
sum ( case when employee_id is null then taxamount else 0 end ) member_tax,
sum ( case when employee_id is null then amount else 0 end ) + sum ( case when employee_id is null then taxamount else 0 end ) member_total,
sum ( case when employee_id is not null then amount else 0 end ) employee_debit,
sum ( case when employee_id is not null then taxamount else 0 end ) employee_tax,
sum ( case when employee_id is not null then amount else 0 end ) +
sum ( case when employee_id is not null then taxamount else 0 end ) employee_total
from (
SELECT   POINTOFSALEBILLDETAIL.AMOUNT,
         POINTOFSALEBILLDETAIL.TAXAMOUNT,
         LEDGER.DESCRIPTION,
         POINTOFSALEBILL.BILLDATE,
         POINTOFSALEBILL.EMPLOYEE_ID,
         POINTOFSALEBILL.MEMBER_ID
  FROM   POINTOFSALEBILL, POINTOFSALEBILLDETAIL, LEDGER
 WHERE   (POINTOFSALEBILL.POINTOFSALEBILL_ID =
             POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID)
         AND (LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID)
         AND (POINTOFSALEBILL.FLAG = 0)
)
group by description
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 34236299
try this. Just got rid of member id in the group by.

select description, sum ( case when employee_id is null then amount else 0 end ) member_debit,
sum ( case when employee_id is null then taxamount else 0 end ) member_tax,
sum ( case when employee_id is null then amount else 0 end ) + sum ( case when employee_id is null then taxamount else 0 end ) member_total,
sum ( case when employee_id is not null then amount else 0 end ) employee_debit,
sum ( case when employee_id is not null then taxamount else 0 end ) employee_tax,
sum ( case when employee_id is not null then amount else 0 end ) +
sum ( case when employee_id is not null then taxamount else 0 end ) employee_total
from (
SELECT   POINTOFSALEBILLDETAIL.AMOUNT,
         POINTOFSALEBILLDETAIL.TAXAMOUNT,
         LEDGER.DESCRIPTION,
         POINTOFSALEBILL.BILLDATE,
         POINTOFSALEBILL.EMPLOYEE_ID,
         POINTOFSALEBILL.MEMBER_ID
  FROM   POINTOFSALEBILL, POINTOFSALEBILLDETAIL, LEDGER
 WHERE   (POINTOFSALEBILL.POINTOFSALEBILL_ID =
             POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID)
         AND (LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID)
         AND (POINTOFSALEBILL.FLAG = 0)
)
group by description
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34236303
Help to close the question if you have got the answer you wanted.

Thanks,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now