SQL & PL/SQL :: Get Total Sales Dollar Amount From Two Tables Without Cursor?
Aug 28, 2013
I got this intertesting request from one of my client some time ago and is still trying to find an answer.
Getting total sales dollar amount from two tables.
T1
Item_no(number)Total_Sales_Number(number)
160
250
35
420
T2
Item_no(number)Sales_Date(date)Sales_Number(number)Sale_Price(number)
12012-01-012015
12012-02-153012
22012-01-104016
22012-03-053018
32012-02-011020
Required to generate report to total sale dollar amount by combining information from T1 and T2 (matching with item_no). Rules are
a)Formula is Sales_Number*Sale_price
b)Choose the earliest sale record to latest by Sales_Date with the sum of Sales_Number is less or equal the Total_Sales_Number from T1.
c)If the sum of Sales_Number is less than the Total_Sales_Number from T1, then the use a fixed price for all items, say $10
d)If a item is not in T2 then use $10 for the price
For example,
item_no 1:
Total sale dollar amount = 20*$15+30*$15+(60-20-30)*$10
item_no 2:
Total sale dollar amount = 40*$16+(50-40)*$18
item_no 3:
Total sale dollar amount = 5*$20
item_no 4:
Total sale dollar amount = 20*$10
The current logic is done by using cursor. Can this be done without cursor using such as analytical statements? The Oracle DB is 9i.
View 22 Replies
ADVERTISEMENT
Mar 7, 2010
I have two columns in table
sales_date
sales_amount
I need to find total sales for the whole last week.
Today is current week i.e. 1 (March 1, 2010 and March 7, 2010)
I need to find total_sales for the last week (i.e. Feb 22, 2010 to Feb 28, 2010)
I am unable to create logic for the same.
View 4 Replies
View Related
Jul 16, 2013
I've a form that allow the user to enter information & save it. in the form there is text_item called AMOUNT with multiable records.
I want when the user press SAVE button, a popup message shows the number of records enterd & total amount in all records.
EX:
AMOUNT
1000
200
3000
After clicking Save button a popup message shows ( you enterd 3 records & 4200 $ ) OK ? Cancel?
this my code in WHEN-BUTTON-PRESSED trigger
if :amount IS NOT NULL THEN
declare
cnt_record number := 1;
cnt_amount number :=:amount;
begin
go_item('amount');
[code]....
BUT !! when I test the form nothing happened.
View 5 Replies
View Related
Sep 23, 2013
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
View 8 Replies
View Related
Nov 18, 2010
Trick is that I've got 4 tables
Sale , sale_details, sales_message, message_text
Table:Sales
Sales_id
Table: Sales_product
Sales_id, sales_product_id
Table: Sales_message
Sales_id, sales_message_id, message_text_id
Table:Message_text,
Message_text_id, message_text
Thing is how can I join those tables to get all the sales ID's that have at least two products one of which has got any number of messages and the other one hasn't got ANY any messages. So another words one sale entity that has both products with messages and at lease one with no message. Something like this
Sales 1 ID 123
Product1 ID 1234 for sale ID 123
Product2 ID 4321 for sale ID 123
Sales_message ID 098 for product ID 1234 'blabla'
Sales_message ID 876 for product ID 1234 'albalb'
AND NO MESSAGES FOR PRODUCT 4321
This is what I want to get from DB.
View 3 Replies
View Related
Aug 23, 2013
The below Decode query is
SELECT CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 50 THEN 'SALES'
WHEN 30 THEN 'SALES'
ELSE 'Invalid'
END
FROM dept;
But my question is that, it prints WHEN 30 THEN 'SALES' after that it prints WHEN 50 THEN 'SALES' as 'Invalid'.
View 5 Replies
View Related
May 5, 2012
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0
20 , 95 , 0
30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196
20 , 95 , 95
30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
View 2 Replies
View Related
Mar 21, 2013
So I'm trying to display a monetary value in an item.
SELECT to_char(SUM(TXN_AMT),'$9,999') from CMS_COST_TRACKING where UPPER(TXN_NODE)='VMAX'this results in ######### in the item
If I remove the to_char formatting then the SUM displays correctly.Is this a restriction of some kind. The item is a display only.
APEX Application Express 4.1.1.00.23
ORacle 11G.
View 1 Replies
View Related
Feb 14, 2012
I'd like to join tables in a cursor.
I tried coding this way...
CURSOR studgrade_cur IS
SELECT g.stud_id, g.grade, subj_code, s.description
FROM studgrades g JOIN subjects s
ON(g.subj_code = s.subj_code)
WHERE stud_id = :Studentprofile.student_id;
but i got an error, saying:
Quote:encountered the symbol JOIN when expecting one of the following:
,; for group having intersect minus order start union where
connect
Is it not allowed to use JOIN statement in a cursor?
View 4 Replies
View Related
May 3, 2010
I'm needing to pull data into a cursor, then split this data into 3 different tables, each having the same number of rows and a select number of columns from the original. i can pull the data, but then i can only access it one row at a time via FETCH, then i can't load into the 3 new CURSORS one row at a time.
View 11 Replies
View Related
Apr 8, 2010
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE
v_mydept1 TEST1.DEPT_CD%TYPE;
v_mydept2 TEST2.DEPT_CD%TYPE;
CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR;
[code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF"
I am not sure what is the exact problem when we want to update multiple columns of different tables.
View 5 Replies
View Related
Nov 30, 2010
I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate
OrderId
Subtotal
CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL")
AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
View 1 Replies
View Related
Jul 6, 2012
I wanted to compare the sales with the same month last year. But the issue is only one value on each customers.
File Attached Table as JPG file..
-----------------------------------------------------------------------
SELECT WORDERS.BP_ORDER,
(SELECT SUM (WINVITEMS.ITEM_VAL)
FROM WINVITEMS
INNER JOIN WORDERS ON WORDERS.ORD_NO = WINVITEMS.ORD_NO
WHERE WORDERS.DATE_ORDER >= TRUNC(ADD_MONTHS(SYSDATE, -3),'MM')
[Code]....
View 7 Replies
View Related
Dec 21, 2012
I have two tables as below
AREA (area_code, area_name)
MONTHLY_SALES (area_code, isbn, book_name, qty, sales_amount)
I want to display the output of top 3 sales columnwise as below.
AREA_NAME Top 1st (ISBN) Top 1st (BOOK_NAME) Top 1st (QTY) Top 2nd (ISBN) Top 2nd (BOOK_NAME) Top 2nd (QTY) Top 3rd (ISBN) Top 3rd (BOOK_NAME) Top 3rd (QTY)
View 5 Replies
View Related
Jul 12, 2004
I'm trying to create a query which will show sales of products by month, something like this: -
JanFebMarApr
Prod 11020511
Prod 200510
Prod 31921015
The data is held on 3 tables: -
TB_ARTICLES
ID
CODE
DESCRIPTION
TB_TRANSACTIONS
ID
BOOKKEEPING_DATE
TRANS_DATE
[code]....
Running a query for one month is no problem at all, I just don't know how to create a cross tab style one.
View 12 Replies
View Related
Feb 13, 2013
Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.
As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?
View 1 Replies
View Related
Mar 26, 2013
I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.
SELECT decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
--decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
farinva_invh_Code,
spriden_id,
--spriden_last_name "last Name"
[Code]....
View 3 Replies
View Related
Aug 4, 2009
I am using below code to spell number in words
create or replace
function spell_number( p_number in number )
return varchar2
-- original by Tom Kyte
-- modified to include decimal places
[Code]....
The number amount is : 9899.25
I am getting the output is:
NINE THOUSAND EIGHT HUNDRED NINETY-NINE POINT TWO FIVE
But i want output should be
NINE THOUSAND EIGHT HUNDRED NINETY-NINE AND Twenty five FILS ONLY
where i need to modify the code.
Files is equal to cent as per DOLLOR currency
View 6 Replies
View Related
Jul 28, 2012
I need to put amount '0' if the row exist but has no amount in my "where " conditions. the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is : "result" tab in excel atached file.i need this result: "result 2" tab in excel atached file.
View 8 Replies
View Related
Aug 8, 2013
I need to get payment amount. For example i have one payment for 3 invoices.I need to show in my report for this payment 3 invoices:
invoice_number, invoice_amount, payment_amount.
Total payment is $10,000.
Invoice A: $5,000
Invoice B: $5,000
Invoice C: $5,000
So,
for Invoice A payment is 3,000$,
for Invoice B payment is 2,000$,
for Invoice C payment is 5,000$.
This is that i need to get.In action payment amounts are after withholding tax and i need to get amount before.
View 5 Replies
View Related
Nov 29, 2012
i want to get balanced amount based on max(suid ).
my query is
create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type,
v_area_suid in i_purchase_h.pih_area_suid%type,
v_invoice_dt in i_purchase_h.pih_invoice_date%type,
v_pih_suid in i_purchase_h.pih_suid%type,
v_type in i_purchase_h.pih_type%type) is
[code]......
my output is
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00055.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.00030.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
my output should come
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00075.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.000105.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
View 3 Replies
View Related
Aug 6, 2013
I have oracle 11gr2 database on linux os. It's total sga size is 500mb only. Now, if uses wants read the 1gb of data from database, then there is no sufficient memory in buffer cache. so how it will works. the transaction will get successful or it will fail.And i have another doubt, does oracle can read the data from memory only or it can also read directly from disk.
View 11 Replies
View Related
Mar 1, 2012
I want to convert an amount value in 99,999,999,990.99 format without using TO_CHAR function. I was trying to use regex. Is there any other function through which I would be able to do this?
View 6 Replies
View Related
Mar 1, 2011
I have implement multi master replication between two server.
How much amount of data transfer over the network? How to calculate this value?
View 2 Replies
View Related
Oct 18, 2010
I have a table and below are the rows:
create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)
Begin
insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300);
insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800);
insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000);
insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100);
insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100);
End;
I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.
The output should be:
Person_ID Month End Date Year Date Rev YTD
111 Dec-09 31-Dec-09 1-Apr-09 1300 1300
111 Jan-10 31-Jan-10 1-Apr-09 1000 2300
111 Feb-10 28-Feb-10 1-Apr-09 800 3100
111 Mar-10 31-Mar-10 1-Apr-09 1000 4100
111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year
111 May-10 31-May-10 1-Apr-10 1100 2100
111 Jun-10 30-Jun-10 1-Apr-10 2100 4200
I guess this should be achievable using some analytical functions, but I am unable to get the desired output.
View 7 Replies
View Related
Oct 8, 2013
I have the below SQL code -
SELECT DISTINCT
opr, CAST (distinctcount AS REAL) * 0.02 AS total, distinctcount
FROM ( SELECT DISTINCT opr, COUNT (*) AS distinctcount
FROM out
WHERE code = '22'
AND timestamp LIKE '201303%'
GROUP BY opr)
I would like this value 'distinctcount' to be multiplied with different amount for period January till June and from June to December. For instance for period (January till June) to be multiplied with 0.02, for period (June to December) to be multiplied with 0.03.
View 12 Replies
View Related
Mar 12, 2013
My problem:
+ Oracle 11g
+ Centos 5.5
+ I create many users by shell script (user01, user02, user03 ..... user0n)
+ How can I connect all user to Oracle database in Centos (may be create connect.sh) - command line env ?
......
// user01 , user02 ... ---===> connect Oracles ?
My idea: using fork to create multi sqlplus to connect but it cant o_0
If all users can connect to Orcl db, I can count real user connections.
View 4 Replies
View Related
Jun 24, 2010
Is there a way to force a query to ALWAYS bring back a certain amount of rows, even if some of the rows are blank?
I know there's a way in MSSQL but not sure how to in SQL+
View 7 Replies
View Related
Jul 25, 2011
extract a huge amount of data from a couple of views... the problem is that they want it in TXT files with fixed record length. There will be like 6 files, for a total amount of about 10GB.
export those tables in the fastest possible way? If I'm not mistaken exp and expdp can't create txt files, so do I really need to use utl_file or spool?
View 1 Replies
View Related
Dec 14, 2010
I am creating 5 dbms_job at run time in side a stored procedure.But when I execute that procedure, all the job get 100%cpu and as a result other process does not get response. so my question is can we limit the dbms_job to use a defined amount of cpu%.
View 9 Replies
View Related