Solved

Tabular format query

Posted on 2010-11-29
24
342 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
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.

 

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
 
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

785 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