Tabular format query

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
GRChandrashekarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
anumosesCommented:
what help do you need here?
0
 
GRChandrashekarAuthor Commented:
I want the ouput in required format. Please see the sheet IN EXCEL named "REQUIRED"
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
anumosesCommented:
LEDGER      
MEMBER DEBIT      
MEMBER TAX      
MEMBER TOTAL      
EMPLOYEE DEBIT      
EMPLOYEE TAX      
EMPLOYEE TOTAL

Are all these calculated fields?
0
 
GRChandrashekarAuthor Commented:
No only MEMBER TOTAL      EMPLOYEE TOTAL are calcualted rest are readily available in DB
0
 
anumosesCommented:
POINTOFSALEBILL, POINTOFSALEBILLDETAIL, LEDGER
 Are these the tables?
If so give me the colums in corresponding tables
0
 
GRChandrashekarAuthor Commented:
YES these are tables
Columns are there in query I have written
0
 
HainKurtSr. System AnalystCommented:
use case

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

hope it helps...
0
 
anumosesCommented:
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
 
GRChandrashekarAuthor Commented:
both solutions are not working
0
 
mohammadzahidCommented:
Check out this thread of EE. Hope this helps!!
0
 
GRChandrashekarAuthor Commented:
That is my own post :) but this is differnet. I also want to group by Leger.description
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
@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
 
anumosesCommented:
Plese give us the table names on the specific column you need in the select query
0
 
GRChandrashekarAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
GRChandrashekarAuthor Commented:
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
 
GRChandrashekarAuthor Commented:
attached sheet may be wrong but my above ans is right
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
GRChandrashekarAuthor Commented:
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
 
GRChandrashekarAuthor Commented:
not necessary to display MEMBER_ID in output
0
 
GRChandrashekarAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Help to close the question if you have got the answer you wanted.

Thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.