Home » SQL & PL/SQL » SQL & PL/SQL » need help in PLSQL (knocking off recird on FIFO basis)
need help in PLSQL [message #382350] |
Thu, 22 January 2009 00:27  |
sonalshastry
Messages: 52 Registered: September 2007
|
Member |
|
|
Hi All
I need help in writing query,the problem i have a table contain transactions od client & another table which contains Rebate Schemes
1 scheme having option that
client buy 500 quantity then system will exempt 100 quantity on the basis of date i.E. FIFO basis
select dt.TRADE_DT, sum(dt.TradeLot)
from trades dt, REBATE_CLIENT R
where r.client_id = DT.TRADE_DT
AND dt.TRADE_DT >= '01-OCT-2008'
and dt.TRADE_DT <= '30-OCT-2008'
and r.clientid = '246589'
group by TRADE_DT
above query gives me trade date & total lot bought for a client
what should be next step
Plzzz Help
Sonal
|
|
|
|
|
|
|
Re: need help in PLSQL [message #382359 is a reply to message #382350] |
Thu, 22 January 2009 00:45   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
'01-OCT-2008' is still a string, Don't believe in the implicit conversion of the string to date, always TYPE cast the sting into date through To_DATE function.
Provide the DML, DDL for the query including the test case, there only we can help you.
Thanks
Trivendra
|
|
|
Re: need help in PLSQL [message #382381 is a reply to message #382359] |
Thu, 22 January 2009 01:32   |
sonalshastry
Messages: 52 Registered: September 2007
|
Member |
|
|
-- Create table
create table REBATE_CLIENT
(
CLIENT_ID VARCHAR2(20),
LOT NUMBER(5)
)
create table Trades
(
CLIENT_ID VARCHAR2(20),
Tradelot NUMBER(5),
Trade_DT DATE
)
-- insert for trades
insert into trades values ('246589','90',10/1/2008)
insert into trades values ('246589','20',10/3/2008)
insert into trades values ('246589','50',10/7/2008)
insert into trades values ('246589','30',10/8/2008)
insert into trades values ('246589','120',10/10/2008)
insert into trades values ('246589','10',10/13/2008)
--insert for REBATE_CLIENT
insert into REBATE_CLIENT values ('246589','100')
now system sould not charge to client for the trade done on 10/1/2008 & for the 10 qty of 10/3/2008
|
|
|
|
|
|
Re: need help in PLSQL [message #382388 is a reply to message #382381] |
Thu, 22 January 2009 02:08   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
sonalshastry wrote on Thu, 22 January 2009 08:32 |
now system sould not charge to client for the trade done on 10/1/2008 & for the 10 qty of 10/3/2008
|
Sorry, but both are strings with possible ambiguous conversion to date. Exactly, what date is represented by '10/1/2008'. Is it October, 1st or January, 10th?
As you were already suggested, use TO_DATE function to convert strings into date.
Just to explain comparison rules in your first post:
'01-OCT-2008' < '10-JAN-2008' (because of the first character).
|
|
|
Re: need help in PLSQL [message #382390 is a reply to message #382350] |
Thu, 22 January 2009 02:09   |
sonalshastry
Messages: 52 Registered: September 2007
|
Member |
|
|
Result
ClientId TRADELOT TRADE_DT
246589 10 10/3/2008
246589 50 10/7/2008
246589 30 10/8/2008
246589 120 10/10/2008
246589 10 10/13/2008
Quote: |
Here i have escap 1 st row which has QTY 90 & date 10/1/2008
& for second row taken only QTY 10 & date 10/1/2008 after dedection 10 Qty from this record
|
|
|
|
|
Re: need help in PLSQL [message #382396 is a reply to message #382393] |
Thu, 22 January 2009 02:37   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
As far as I can tell, the OP wants the quantity in REBATE_CLIENT.LOT to be subtracted from the quantities in TRADES.TRADELOT on a row by ro basis, until the whole quantity has been used.
GThis seems to do it (and I've corrected the various errors in the OPs script:create table REBATE_CLIENT
(CLIENT_ID VARCHAR2(20),
LOT NUMBER(5));
create table Trades
(CLIENT_ID VARCHAR2(20),
Tradelot NUMBER(5),
Trade_DT DATE );
-- insert for trades
insert into trades values ('246589',90 ,to_date('10/1/2008','mm/dd/yyyy'));
insert into trades values ('246589',20 ,to_date('10/3/2008','mm/dd/yyyy'));
insert into trades values ('246589',50 ,to_date('10/7/2008','mm/dd/yyyy'));
insert into trades values ('246589',30 ,to_date('10/8/2008','mm/dd/yyyy'));
insert into trades values ('246589',120,to_date('10/10/2008','mm/dd/yyyy'));
insert into trades values ('246589',10 ,to_date('10/13/2008','mm/dd/yyyy'));
--insert for REBATE_CLIENT
insert into REBATE_CLIENT values ('246589','100');
commit;
select trade_dt
,tradelot
,greatest(least(lot-nvl(lag(s2) over (partition by client_id order by trade_dt),0),s2),0) rebate
from (select dt.trade_dt
,dt.client_id
,sum(dt.tradelot) over (partition by dt.client_id,trade_dt) tradelot
,sum(dt.tradelot) over (partition by dt.client_id order by trade_dt rows between unbounded preceding and current row) s2
,r.lot
from trades dt
,REBATE_CLIENT R
where r.client_id = DT.client_id
and dt.TRADE_DT >= to_date('01-OCT-2008','dd-mon-yyyy')
and dt.TRADE_DT <= to_date('30-OCT-2008','dd-mon-yyyy')
and r.client_id = '246589' );
@sonalshastry: Please try to explain your problem clearly. We shouldn't have to keep coming back with requests to get more information that we need - if you want our help, please take the time to post everything we might need.
On Dates - '01/03/2008' is not a date. It is a string.
TO_DATE('01/03/2008','dd/mm/yyyy') is a date.
If you think that '01/03/2008' is a date, then please tell me whether it is 1-mar-2008 or 3-jan-2008 - I can convert it to either.
|
|
|
Re: need help in PLSQL [message #382398 is a reply to message #382350] |
Thu, 22 January 2009 02:48   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
I hope is that what you are looking for..
SQL> SELECT client_id, DECODE (rn, 1, abs(balance), tradelot) AS tradelot,
trade_dt
FROM (SELECT a.client_id, a.tradelot, a.trade_dt, running_sum, lot,
lot - running_sum balance,
RANK () OVER (PARTITION BY a.client_id ORDER BY lot
- running_sum DESC) rn
FROM (SELECT client_id, tradelot, trade_dt,
SUM (tradelot) OVER (PARTITION BY client_id ORDER BY trade_dt)
running_sum
FROM trades) a,
rebate_client b
WHERE a.client_id = b.client_id AND SIGN (lot - running_sum) = -1)
ORDER BY trade_dt
CLIENT_ID TRADELOT TRADE_DT
-------------------- ---------- ---------
246589 10 03-OCT-08
246589 50 07-OCT-08
246589 30 08-OCT-08
246589 120 10-OCT-08
246589 10 13-OCT-08
5 rows selected.
Hats off to JRowBottom.
Thanks
Trivendra
[Updated on: Thu, 22 January 2009 02:54] Report message to a moderator
|
|
|
|
Re: need help in PLSQL [message #382469 is a reply to message #382412] |
Thu, 22 January 2009 07:51  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
sonalshastry wrote on Thu, 22 January 2009 04:45 |
i assure you next time i will post my query as you suggested
|
I doubt it. I also doubt you learned how to use DATEs properly.
|
|
|
Goto Forum:
Current Time: Fri Feb 07 15:31:46 CST 2025
|