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....
13 1834 Stang02GT 1,208
Recognized Expert Top Contributor
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.
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......
Hi,
What is the query you are using now?
Pilgrim.
you just need to sum up all the columns that will give you total.
For Eg: (jan+feb+mar+ap r+.....) Total
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?
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
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.
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
-
-
HAI AMIT,
Thank u for your reply,
I have executed ur query, iam getting repetation of data.
let me explain clearly,
ITEM_DRG_SER_NA ME 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......
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mark Townsend |
last post by:
Following is a summary of articles spanning a 7 day period,
beginning at 23 Feb 2004 05:29:19 GMT and ending at
01 Mar 2004 04:12:14 GMT.
Notes
=====
- A line in the body of a post is considered to be original if it
does *not* match the regular expression /^\s{0,3}(?:>|:|\S+>|\+\+)/.
- All text after the last cut line (/^-- $/) in the body is
|
by: Gilles Cadorel |
last post by:
Sur un PC, nous avons installé plusieurs version d'Oracle Client.
Plusieurs logiciels tiers utilisant Oracle ne demandent pas lors de
leur installation, quelle version d'Oracle utiliser.
Ces logiciels fonctionnent correctement mais nous aimerions savoir à
coup sûr, quel Client Oracle est réellement utilisé.
Merci d'avance
|
by: Mike |
last post by:
No flame wars, please!
We're planning a move from a non-relational system to
a relational system. Our choices have been narrowed to
Oracle and DB2. Since we're moving from non-relational
to relational, then we're not currently using any
relational-type operators. So I expect the end result to
use simple, SQL standard commands and queries.
The question: At the SQL standard level is there any
|
by: Markus Breuer |
last post by:
I have a question about oracle commit and transactions. Following scenario:
Process A performs a single sql-INSERT into a table and commits the
transaction. Then he informs process B (ipc) to read the new date. So
process B starts "select ..." but does not get the previously inserted
row. The timespan between commit and select is very short.
(NOTE: two different sessions are used)
Questions:
1.) Does commit when returning from call...
|
by: Rhino |
last post by:
One of my friends, Scott, is a consultant who doesn't currently have
newsgroup access so I am asking these questions for him. I'll be telling him
how to monitor the answers via Google Newsgroup searches.
Scott has heard a lot of hype about DB2 and Oracle and is trying to
understand the pros and cons of each product. I'm quite familiar with DB2
but have never used Oracle so I can't make any meaningful comparisons for
him. He does not have...
| |
by: Murtix Van Basten |
last post by:
Hi all,
I will deploy a database project to an Oracle server, but I could not
figure out which version of Oracle should I get. Here is my configuration:
Hardware:
Dell 1750 Dual Xeon 3.2Ghz, 2GB Ram, 3x36GB Hdd on Raid 5
Operating System: Redhat Linux 9
I will deploy only 1 database for the application. Only 1 DBA will use the
Oracle server when necessary. When the database once deployed, Only 1
|
by: Mark A |
last post by:
Here is Mark Townsend's (Oracle Product Manager) explanation and
justification for posting in the DB2 newsgroup, as posted on the Oracle
newsgroup, and my response:
"Mark Townsend" <markbtownsend@comcast.net> wrote in message
>
> By an large you will find that they
> all fall into 1 of 3 categorires.
>
> 1) Explaining how Oracle does something when somebody asks how to do the
|
by: Jon Ole Hedne |
last post by:
My Access 2002-application need to work with tables from both Oracle and
Access. To solve this, I want to run some querys on three views in Oracle
and import the results into temporary Access-tables.
I have tried this:
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString = "data source=" & datafil & ";Jet OLEDB:Database
Password="
conn.Open datafil
|
by: JV |
last post by:
A ref cursor data type can obviously be returned as an output parameter of a
stored procedure, but can an ASP.NET application call an oracle proc that
uses a ref cursor as an input parameter? If so, how? What object do you
pass as that parameter?
We are using System.Data.OracleClient, but if necessary, we might be able to
switch.
|
by: jehugaleahsa |
last post by:
Hello:
I am working with Oracle .NET Stored Procedures. I would like to know
how to return the results of a SELECT statement. I have tried
returning a OracleRefCursor and a DataTable, but nothing seems to
work.
What data type must I return for this to be accepted as .NET stored
procedure?
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |