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 Go to next message
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 #382351 is a reply to message #382350] Thu, 22 January 2009 00:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
HUH?
>'01-OCT-2008'
>'30-OCT-2008'
above are strings NOT dates! & 'this is a string & not a date'

Re: need help in PLSQL [message #382354 is a reply to message #382351] Thu, 22 January 2009 00:38 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi,
but PLSQL takes date like this only , isn't it
ant any way this condition is to restict data so that i can get result quickly.


Re: need help in PLSQL [message #382356 is a reply to message #382350] Thu, 22 January 2009 00:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: need help in PLSQL [message #382357 is a reply to message #382350] Thu, 22 January 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Use TO_DATE function.

Regards
Michel
Re: need help in PLSQL [message #382359 is a reply to message #382350] Thu, 22 January 2009 00:45 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 Go to previous messageGo to next message
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 #382383 is a reply to message #382381] Thu, 22 January 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now system sould not charge to client for the trade done on 10/1/2008 & for the 10 qty of 10/3/2008

What does this mean?

Regards
Michel
Re: need help in PLSQL [message #382385 is a reply to message #382383] Thu, 22 January 2009 01:52 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
as a result of previous provided query system should excempt 100 QTY for that client as per the scheme in Rebate client

sonal
Re: need help in PLSQL [message #382387 is a reply to message #382385] Thu, 22 January 2009 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not explain what the query should return.

In addition, your statement should end with a ";" or a "/" so we can execute them as it. Requirements in test case is also to post the result you want with the data you post.

Regards
Michel
Re: need help in PLSQL [message #382388 is a reply to message #382381] Thu, 22 January 2009 02:08 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #382393 is a reply to message #382390] Thu, 22 January 2009 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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


1/ Do you think this is formatted as requested?
2/ Why is the relation between this result and the SUM you used in first post
3/ Explain WITH WORDS what should be the result of the query.

Regards
Michel
Re: need help in PLSQL [message #382396 is a reply to message #382393] Thu, 22 January 2009 02:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 #382412 is a reply to message #382398] Thu, 22 January 2009 03:45 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Thanx a lot
it is a great help i can get
i assure you next time i will post my query as you suggested
Re: need help in PLSQL [message #382469 is a reply to message #382412] Thu, 22 January 2009 07:51 Go to previous message
joy_division
Messages: 4644
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.
Previous Topic: How to hide tables in a schema?
Next Topic: INVALID DATATYPE for Collection
Goto Forum:
  


Current Time: Sat Dec 10 22:38:36 CST 2016

Total time taken to generate the page: 0.09510 seconds