Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Tabular format query

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

Title # Comments Views Activity
ORA-00923: FROM keyword not found where expected 3 80
Can't Access My Database 57 76
create a nested synonym 4 28
SQL2016 to ORACLE11G linked-server 6 28
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

808 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