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

SQL & PL/SQL :: Find Total Sales For The Whole Last Week

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

Forms :: How To Get Total Records And Amount

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

SQL & PL/SQL :: Cursor Implement / Strings Of Data Concatenated Into One Sales Contact Record

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

SQL & PL/SQL :: How To Join Tables To Get All Sales IDs That Have At Least Two Products

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

SQL & PL/SQL :: Print WHEN 30 Then Sales After That It Prints WHEN 50 Then Sales As Invalid

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

Forms :: Distribute Master Block Total Amount In Detail Block Line Wise

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

Application Express :: Formatting (dollar Value) Within ITEM?

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

Forms :: How To Join Tables In A Cursor

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

SQL & PL/SQL :: Pull Data Into A Cursor Then Split Into 3 Different Tables?

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

SQL & PL/SQL :: Cursor Select For Update / Multiple Columns Of Different Tables

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

Database - Summary Of Sales By Quarter?

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

SQL & PL/SQL :: Compare The Sales With The Same Month Last Year

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

PL/SQL :: Display Top 3 Sales Column Wise

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

SQL & PL/SQL :: Create A Query Which Will Show Sales Of Products By Month

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

Workflow :: External Sales Order Lines Remain In Internal Req Requested Status

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

PL/SQL :: Total And Grand Total

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

SQL & PL/SQL :: Amount In Words

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

SQL & PL/SQL :: Put 0 If Row Exist But Has No Amount In Where Conditions

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

SQL & PL/SQL :: How To Get Payment Amount Before Withholding

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

SQL & PL/SQL :: Balance Of Stock Amount

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

Large Amount Of Data

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

Amount Value Format Without Using To_char Function

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

Replication :: How Much Amount Of Data Over Network

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

SQL & PL/SQL :: Year To Date Value In Amount Field

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

SQL & PL/SQL :: Value Distinct Count To Be Multiplied With Different Amount?

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

Checking Real Amount Connections To Oracle Database

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

Force Query To Always Bring Back Certain Amount Of Rows

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

Server Utilities :: Exporting Huge Amount Of Data?

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

Performance Tuning :: Can Limit Dbms_job To Use Defined Amount Of CPU%

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







Copyrights 2005-15 www.BigResource.com, All rights reserved