Oracle question.

[chandra794]

Dear sir,

Iam Oracle developer(D2k), i have a problem in developing the query.....
let me explain in detail,

Report is Revenues for the year of Doctors.

i have develope the query, which give out but like:

Revenue type doctor name Amount
------------------------------------------------------------
consultation umesh k.m 5000

this for, total period.

but, i want to print, months wise,
like:
Revenue type doctor name jan feb mar april................. dec
--------------------------------------------------------------------------------------------------------

can u plz, help me out this problem.......

my query looks like:

SELECT 
TO_CHAR(INVOICE_DATE,'MON') MONTH,
TO_CHAR(INVOICE_DATE,'YYYY') YEAR, 
REVENUE_TYPE_NAME, 
DOCTOR_NAME, 
SUM(AMOUNT) TOTALAMOUNT 
FROM 
       INVOICEMASTER IVM, 
       INVOICEDETAILS IVD, 
       DOCTORMASTER DTM, 
       MASTERTABLE MRT, 
       REVENUETYPEMASTER RTM, 
       COSTCENTERMASTER CCM 
WHERE 
      IVM.INVOICE_NO=IVD.INVOICE_NO 
      AND IVD.DOCTOR_CODE=DTM.DOCTOR_CODE 
      AND IVD.SER_CODE=MRT.RECORD_CODE 
      AND MRT.REVENUE_TYPE_CODE=RTM.REVENUE_TYPE_CODE 
      AND MRT.COSTCENTER_CODE=CCM.COSTCENTER_CODE 
          AND IVM. CANCELINVOICENO IS NULL 
      AND IVD. AMOUNT > 0
GROUP BY 
TO_CHAR(INVOICE_DATE,'MON'),
TO_CHAR(INVOICE_DATE,'YYYY'), 
REVENUE_TYPE_NAME, 
DOCTOR_NAME 
ORDER BY 
TO_CHAR(INVOICE_DATE,'MON'),
TO_CHAR(INVOICE_DATE,'YYYY')
 

plz help me out...

chandra....

[Stang02GT]

This belongs in the Oracle forum and please make sure to use code tags when posting code. I will add them in for you this time.

[chandra794]

Hello,

i have built a query, which gives amount of doctor monthly wise, I want to sum and place beside it.

query output as:

revenuetype doctor jan feb mar apr ...........dec
--------------------------------------------------------------------------------------------------------
consultation chandu 235 300 205 500 169.

but i want as follows

revenuetype doctor jan feb mar apr ...........dec Total
-----------------------------------------------------------------------------------------------------------------
consultation chandu 235 300 205 500 169. sum

by using this query, i want to develop a report in Reports6i.

plz, can anyone help out....

chandra......

[Pilgrim333]

Hi,

What is the query you are using now?

Pilgrim.

[amitpatel66]

you just need to sum up all the columns that will give you total.

For Eg: (jan+feb+mar+apr+.....) Total

[amitpatel66]

What is the output of your query?...Could you please post the error your query is giving or in case incorrect ouput of your query?

[chandra794]

hi amit,

thanks for ur, reply.....any how, i have resolved my problems by myself..
thank u for responding to my problems....

i have another problem,

Report format looks like:

Department:
Doctor name:
Revenue type:
Service Amount:
-----------------------------------

i have develop the query, but, i want to sum up each service and put the total in the amount column.

eg: per day if there are 5 new visits for a doctor, my query prints 5 times, but, i want to sum of this 5 new visits and print.
same as for Follow up.......etc...

my query:

SELECT 
CM.COSTCENTER_NAME,
DM.DOCTOR_NAME,
RM.REVENUE_TYPE_NAME,
ID.INVOICE_NO,
ID.ITEM_DRG_SER_NAME,
ID.AMOUNT
FROM 
COSTCENTERMASTER CM,
DOCTORMASTER DM,
MASTERTABLE MT,
REVENUETYPEMASTER RM,
INVOICEDETAILS ID
WHERE 
REQ_DATE BETWEEN :FROMDATE AND :TODATE AND
CM.COSTCENTER_CODE=DM.DEPT_CODE AND
MT.REVENUE_TYPE_CODE=RM.REVENUE_TYPE_CODE AND
MT.RECORD_CODE=ID.SER_CODE AND
MT.COSTCENTER_CODE=CM.COSTCENTER_CODE
GROUP BY 
COSTCENTER_NAME,
DOCTOR_NAME,
RM.REVENUE_TYPE_NAME,
ID.INVOICE_NO,
ID.ITEM_DRG_SER_NAME,
ID.AMOUNT
ORDER BY 
CM.COSTCENTER_NAME,
DM.DOCTOR_NAME,
RM.REVENUE_TYPE_NAME,
ID.INVOICE_NO

can u help me out...
chandra

[Pilgrim333]

Hi,

It would help if you would provide the solution that helped you with your first problem before posting another problem. The reason is, that other people who visit the forum can be helped by it.

Pilgrim.

[amitpatel66]

I did not understand your query that you have used. Why are you using a GROUP BY clause in your query?

Try the below query that would display list of visits and SUM(Amount) for visits on a particular day

 
SELECT  
CM.COSTCENTER_NAME, 
DM.DOCTOR_NAME, 
RM.REVENUE_TYPE_NAME, 
ID.INVOICE_NO, 
ID.ITEM_DRG_SER_NAME, 
SUM(ID.AMOUNT) OVER(PARTITION BY TRUNC(REQ_DATE) ORDER BY TRUNC(REQ_DATE)) Total_Amount 
FROM  
COSTCENTERMASTER CM, 
DOCTORMASTER DM, 
MASTERTABLE MT, 
REVENUETYPEMASTER RM, 
INVOICEDETAILS ID 
WHERE  
REQ_DATE BETWEEN :FROMDATE AND :TODATE AND 
CM.COSTCENTER_CODE=DM.DEPT_CODE AND 
MT.REVENUE_TYPE_CODE=RM.REVENUE_TYPE_CODE AND 
MT.RECORD_CODE=ID.SER_CODE AND 
MT.COSTCENTER_CODE=CM.COSTCENTER_CODE 
ORDER BY  
CM.COSTCENTER_NAME, 
DM.DOCTOR_NAME, 
RM.REVENUE_TYPE_NAME, 
ID.INVOICE_NO 
 
 

[chandra794]

HAI AMIT,

Thank u for your reply,
I have executed ur query, iam getting repetation of data.
let me explain clearly,

ITEM_DRG_SER_NAME is the service, eg: newvist , reviews, followup,...etc.
i want to sum all the newvisits, followup and reviews seperatly by docor wise.....

for ex:
doctorname services amount
----------------------------------------------------------------------
chandra newvisit 50kd
chandra followup 150kd
chandra reviews 10kd
amit followup 200kd
umesh reviews 110kd....


i want in this way....
iam groping to avoid repeation of data....

thanks,
amit......

[amitpatel66]

Try this:

 
SELECT  DISTINCT
CM.COSTCENTER_NAME, 
DM.DOCTOR_NAME, 
RM.REVENUE_TYPE_NAME, 
ID.INVOICE_NO, 
ID.ITEM_DRG_SER_NAME, 
SUM(ID.AMOUNT) OVER(PARTITION BY DM.doctor_name,ID.item_drg_ser_name ORDER BY DM.doctor_name,ID.item_drg_ser_name) Total_Amount 
FROM  
COSTCENTERMASTER CM, 
DOCTORMASTER DM, 
MASTERTABLE MT, 
REVENUETYPEMASTER RM, 
INVOICEDETAILS ID 
WHERE  
REQ_DATE BETWEEN :FROMDATE AND :TODATE AND 
CM.COSTCENTER_CODE=DM.DEPT_CODE AND 
MT.REVENUE_TYPE_CODE=RM.REVENUE_TYPE_CODE AND 
MT.RECORD_CODE=ID.SER_CODE AND 
MT.COSTCENTER_CODE=CM.COSTCENTER_CODE
ORDER BY  
CM.COSTCENTER_NAME, 
DM.DOCTOR_NAME, 
RM.REVENUE_TYPE_NAME, 
ID.INVOICE_NO 
 
 

[chandra794]

Hai amit...

Thank a lot, u have done it......thank u so much........,
i too tried and got the result, but, i have used subquery and u dont it without subquer...
anyhow.....thanks...
for further support, i will get back to u...


chandra...

[amitpatel66]

Hey no problem. I am glad that I could help you. Yep do post back in case of any further issues.

Moderator

[amitpatel66]

Duplicate threads merged for better management of forum

MODERATOR