Home » SQL & PL/SQL » SQL & PL/SQL » Get Total Sales Dollar Amount from two tables without cursor (Oracle 9i)
Get Total Sales Dollar Amount from two tables without cursor Wed, 28 August 2013 03:20
 amsteel Messages: 13Registered: August 2013 Junior Member
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)
1 60
2 50
3 5
4 20

T2
Item_no(number) Sales_Date(date) Sales_Number(number) Sale_Price(number)
1 2012-01-01 20 15
1 2012-02-15 30 12
2 2012-01-10 40 16
2 2012-03-05 30 18
3 2012-02-01 10 20

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.

Thanks,
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594394 is a reply to message #594392] Wed, 28 August 2013 03:26
 cookiemonster Messages: 12793Registered: September 2008 Location: Rainy Manchester Senior Member
Welcome to the forum.
If you post a Test case - create table statements and insert statements for a small, representative sample of data, then we'll be able to work with your tables and data.

A cursor is just a way of declaring and running a select statement in pl/sql, so this can be done with or without one. Posting your current code would probably also help.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594395 is a reply to message #594394] Wed, 28 August 2013 03:27
 cookiemonster Messages: 12793Registered: September 2008 Location: Rainy Manchester Senior Member
What happens if the total amount in t2 exceeds the amount in t1?
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594397 is a reply to message #594392] Wed, 28 August 2013 03:30
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
Hi, welcome to the forum.
Couple of points:
1. Rather than posting sample data in the way that you have, please post a valid, working test case. That way, we don't have to build it on your behalf.
2. Any time you select from a table, you work with cursors, it's just that they are implicit cursors.
3. Have you made any attempt to solve this (I assume that you have)? Please post your attempt.
4. Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

ETA, OK, I was waaaaay too slow there

[Updated on: Wed, 28 August 2013 03:30]

Report message to a moderator

Re: Get Total Sales Dollar Amount from two tables without cursor [message #594401 is a reply to message #594394] Wed, 28 August 2013 04:07
 amsteel Messages: 13Registered: August 2013 Junior Member
Sorry, I will try to post the create table and insert statement as soon as I can.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594402 is a reply to message #594395] Wed, 28 August 2013 04:08
 amsteel Messages: 13Registered: August 2013 Junior Member
use only the amount in T1, ignore the exceeded amount.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594403 is a reply to message #594397] Wed, 28 August 2013 04:11
 amsteel Messages: 13Registered: August 2013 Junior Member
The client is suspecting performance issues are related to the use of cursor. I am no expert on that and that is why I come here. I tried to use analytic functions such as partition by and ranges but can not find a solution yet.

I will try to pose my code along with the create table and insert statement but the code makes no sense at all at this moment.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594404 is a reply to message #594403] Wed, 28 August 2013 04:23
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
amsteel wrote on Wed, 28 August 2013 10:11
The client is suspecting performance issues are related to the use of cursor.
The client is more astute than many then. Yay for the client!

Quote:
I am no expert on that and that is why I come here. I tried to use analytic functions such as partition by and ranges but can not find a solution yet.
No offense is intended here, but why are you doing work for a client (and presumably charging them for that work) when you lack the skillset for that work?
Quote:

I will try to pose my code along with the create table and insert statement but the code makes no sense at all at this moment.

You might be surprised, post it anyway, i'll give us a hint on how close you are and how to pitch the solution.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594417 is a reply to message #594404] Wed, 28 August 2013 04:59
 amsteel Messages: 13Registered: August 2013 Junior Member
Quote:
No offense is intended here, but why are you doing work for a client (and presumably charging them for that work) when you lack the skillset for that work?

I am doing some design work for the same client but not as a PL/SQL developer. Apparently they are running out of options/time to solve the performance issue. And too bad, my expense is already covered and I can't charge extra on this.

Talking about the lacking of skills, to my defense, I think my situation is on par with many CEOs. So, yay for the American Corporations.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594421 is a reply to message #594417] Wed, 28 August 2013 05:08
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
LOL, that's a very fair comment
Look forward to your test case.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594466 is a reply to message #594421] Wed, 28 August 2013 19:38
 amsteel Messages: 13Registered: August 2013 Junior Member
Here are the create table and insert scripts:

Create Table t1 (Item_No number, Total_Sales_Number number);

Insert Into t1 (Item_No, Total_Sales_Number) Values(1,60);
Insert Into t1 (Item_No, Total_Sales_Number) Values(2,50);
Insert Into t1 (Item_No, Total_Sales_Number) Values(3,5);
Insert Into t1 (Item_No, Total_Sales_Number) Values(4,20);

Create Table t2 (Item_No number, Sales_Date date, Sales_Number Number, Sale_Price number);

Insert Into t2 (Item_No, Sales_Date, Sales_Number, Sale_Price) Values(1, To_date('2012-01-01', 'yyyy-mm-dd'), 20, 15);
Insert Into t2 (Item_No, Sales_Date, Sales_Number, Sale_Price) Values(1, To_date('2012-02-15', 'yyyy-mm-dd'), 30, 12);
Insert Into t2 (Item_No, Sales_Date, Sales_Number, Sale_Price) Values(2, To_date('2012-01-10', 'yyyy-mm-dd'), 40, 16);
Insert Into t2 (Item_No, Sales_Date, Sales_Number, Sale_Price) Values(2, To_date('2012-03-05', 'yyyy-mm-dd'), 30, 18);
Insert Into t2 (Item_No, Sales_Date, Sales_Number, Sale_Price) Values(3, To_date('2012-02-01', 'yyyy-mm-dd'), 10, 20);

Re: Get Total Sales Dollar Amount from two tables without cursor [message #594468 is a reply to message #594466] Wed, 28 August 2013 19:46
 amsteel Messages: 13Registered: August 2013 Junior Member
And here is my non-sense script. ：-（

SELECT t1.Item_No, t2.Sales_Date, t2.Sale_Price,
t1.Total_Sales_Number-t2.total_Sales As diff, t2.Sale_Price*(t1.Total_Sales_Number-t2.total_Sales) as total_Dollar_amount
From t1
Left Outer Join
(
SELECT Item_No, Sales_Date, Sale_Price, Sum(Sales_Number) OVER (Partition By Item_No Order by Sales_Date) As total_Sales
FROM t2
)t2
On t1.Item_No=t2.Item_No;
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594469 is a reply to message #594468] Wed, 28 August 2013 19:56
 BlackSwan Messages: 25523Registered: January 2009 Location: SoCal Senior Member
based upon the posted tables & data, what should be the results of the query & why only those results?

http://www.orafaq.com/forum/t/88153/0/
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594470 is a reply to message #594469] Wed, 28 August 2013 20:00
 amsteel Messages: 13Registered: August 2013 Junior Member
BlackSwan wrote on Wed, 28 August 2013 19:56
based upon the posted tables & data, what should be the results of the query & why only those results?

http://www.orafaq.com/forum/t/88153/0/

The rules and expected results are in my first post and here again：
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

Re: Get Total Sales Dollar Amount from two tables without cursor [message #594487 is a reply to message #594470] Thu, 29 August 2013 02:33
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
Hi, your requirements are a little unclear.
You say that the formula is
" Sales_Number*Sale_price"
Obviously it is more complex than that in that as the next set of requirements sow, but that's fine.
Then
"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."
This is really unclear. Can you break down exactly what you mean by:
Choose the earliest sale record to latest by Sales_Date
Do we choose the earliest, do we then go and find the latest, why did we have to find the earliest first.
Is this meant to be a running calculation? If so, it's not particularly clear.
Are you really just looking for total sales i.e. the sum of sales_number in each item.
" 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" I have a feeling that this is where your own calculation goes somewhat awry. You requirement clearly states an option, i.e. some conditional processing (if then else) but you have performed no condition processing in the query.
In addition, this simply doesn't match up to the formula that you posted for item no 1.
20*\$15+30*\$15+(60-20-30)*\$10
Which can be more simply written as

A B
50*15 + 10*10

What your formula appears to show is that instead of using "a fixed price for all items, say \$10", you are only using that fixed price for the difference between Sales_Number and Total_Sales_Number.

As Blackswan has already requested, can you now post a 'table' of your expected output based on the inputs that you provided. Please make sure that you use [code] tags to help preserve formatting. I also have a feeling that you're going to end up having to provide a larger sample dataset.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594492 is a reply to message #594487] Thu, 29 August 2013 03:12
 amsteel Messages: 13Registered: August 2013 Junior Member
well, I think there is a typo in item_no 1. Sorry about that.
Ok. Let me try it again. The basic rule is to find the sale price and total unit from t2, starting with the earliest date.

Using item_no 1 as example, it has 60 units sold.
1. Find the earliest sale record in t2, which is 2012-01-01, 20 units at \$15 each. So for that 20 units, the total dollar amount is 20*\$15 = \$300.
2. Then it still has 40 left (60-20). The next sale record in t2 is 2012-02-15, 30 units at \$12. Dollar amount for this 30 is 30*\$12=360.
3. It still has 10 left (60-20-30). There is no more record in t2. So for this 10 units, it should use the default price \$10, Dollar amount is 10*\$10=100.
The total dollar amount for item_no 1 now is \$300+\$360+\$100=\$760.

For item_no 2,
1. 2012-01-10, 40*\$16
2. It has only 10 left (50-40), less than the units of 2012-03-05, the dollar amount is 10*\$18
Total dollar amount 40*\$16+10*\$18

Item_no 4 has no record in t2,
Total dollar amount 20*\$10.

I hope I did better this time. I will try to see if I can format the results better later.

Thanks,
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594493 is a reply to message #594492] Thu, 29 August 2013 03:18
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
From my earlier post
Quote:
As Blackswan has already requested, can you now post a 'table' of your expected output based on the inputs that you provided. Please make sure that you use [code] tags to help preserve formatting.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594495 is a reply to message #594493] Thu, 29 August 2013 03:32
 amsteel Messages: 13Registered: August 2013 Junior Member
Item_No		Total_Dollar_Amount
1		760
2		820
3		100
4		200
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594498 is a reply to message #594495] Thu, 29 August 2013 04:04
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
OK, one of the first things to point out here is that your table design seems pretty messed up.
Next, just to confirm, if the total sales for an item from t1 is greater than the total sales for an item in t2, then the remainder left over is calculated at a sales price of 10. However if the total sales for an item in t2, is greater than total sales for an item from t1 then the remainder is ignored altogether?

Looks like you need something like:
with t as (SELECT  t1.item_no
, t1.total_sales_number
, t2.item_no    in_t2
, t2.sales_date
, t2.sales_number
, sale_price
, SUM(t2.sales_number) OVER (PARTITION BY t1.item_no
ORDER BY t2.sales_date)  sales
, RANK() OVER (PARTITION BY t1.item_no
ORDER BY t2.sales_date DESC) rnk
FROM t1
LEFT JOIN t2
ON t1.item_no = t2.item_no)
SELECT sub.item_no
, sum(act_sales+remains)
FROM (
SELECT t.*
, CASE WHEN sales <= total_sales_number
THEN sales_number*sale_price
WHEN in_t2 IS NULL
THEN total_sales_number*10
WHEN sales>total_sales_number
THEN (sales_number-(sales-total_sales_number))*sale_price
END act_sales
, CASE WHEN rnk = 1 and sales <= total_sales_number
THEN (total_sales_number-sales)*10
ELSE 0
END  remains
FROM t
) SUB
GROUP BY item_no

Re: Get Total Sales Dollar Amount from two tables without cursor [message #594504 is a reply to message #594498] Thu, 29 August 2013 04:34
 amsteel Messages: 13Registered: August 2013 Junior Member
Great, the results looks good. I will try to see if the logic can be further tested and also the performance against the cursor version.

I would kill myself if I designed such tables. Also the data is so messed up. Second thought, it would be much harder to find a contract if everyone is doing what he/she is suppose to do. So, eh, tough choice.

I will post back once I have any news.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #594505 is a reply to message #594504] Thu, 29 August 2013 04:36
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
Quote:
Second thought, it would be much harder to find a contract if everyone is doing what he/she is suppose to do
Dude, that is practically my mantra
Re: Get Total Sales Dollar Amount from two tables without cursor [message #595006 is a reply to message #594505] Wed, 04 September 2013 20:14
 amsteel Messages: 13Registered: August 2013 Junior Member
pablolee wrote on Thu, 29 August 2013 04:36
Quote:
Second thought, it would be much harder to find a contract if everyone is doing what he/she is suppose to do
Dude, that is practically my mantra

Dude, keep it low.
Re: Get Total Sales Dollar Amount from two tables without cursor [message #595007 is a reply to message #594504] Wed, 04 September 2013 20:17
 amsteel Messages: 13Registered: August 2013 Junior Member
update from the client: the new script took only half the time in UAT, compare to the old one with cursor. They are in the progress to formally develope the script (to add logging and other staff), test and deploy the script.

Thank you all for the help for my first question here (being a guest for too long, I have to say).
 Previous Topic: Hierarchial query - CONNECT_BY_LOOP Next Topic: Extracts subsets in the same query
Goto Forum:

Current Time: Thu Jul 20 23:47:40 CDT 2017

Total time taken to generate the page: 0.16178 seconds