Home » SQL & PL/SQL » SQL & PL/SQL » best possibilities
best possibilities [message #198179] Mon, 16 October 2006 01:10 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i need to pay the bill say 635 rupees Indian currency in the cash counter for eg:keb bill
1 have/pay 1000 i should get 365 back
in the cash counter say denomination is 1000,500,100,50,20,10,5,2,1 rupee
i should get the best possibilities

case 1:100 three and 50+20+10+5 = 365 total notes(7 notes) this is correct(because less currency notes)
50+50+50+50+50+50+50+20+10+5(10 notes) this is wrong (because more currency notes)

case 2:
if 100 is not there 50+50+50+50+50+50+50+20+10+5(10 notes)(this is correct)
Re: best possibilities [message #198187 is a reply to message #198179] Mon, 16 October 2006 02:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Very quick and dirty, and untested:
VAR price NUMBER
exec :price := 365

SELECT floor(:price/1000)                    r_1000
     , floor(mod(:price,1000)/500)           r_500
     , floor(mod(:price,500)/100)            r_100
     , floor(mod(:price,100)/50)             r_50
     , floor(mod(:price,50)/20)              r_20
     , floor(mod(mod(:price,50),20)/10)      r_10
     , floor(mod(:price,10)/5)               r_5
     , floor(mod(:price,5)/2)                r_2
     , mod(mod(:price,5),2)                  r_1
FROM dual
/
I'm probably not 100% correct but it should get you started.

MHE
Re: best possibilities [message #198419 is a reply to message #198187] Tue, 17 October 2006 02:29 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
this is a sql server 2000 query can u all pls let me know how to go about in oracle

I HAVE created procedure with this procedure i will get only the greatest/highest amount

for eg: sql > bal 14

10.0000
2.0000
2.0000


i have 7 in the table data i should get 7+7=14 this is correct (because least currency notes)

CREATE procedure bal (@Amount money)
as
begin
declare @Note smallmoney
DECLARE @Notes TABLE (Note smallmoney NOT NULL)WHILE @Amount > 0
BEGIN
SELECT @Note = MAX(FaceValue)
FROM dbo.BankNotes
WHERE FaceValue <= @Amount and Available > 0

INSERT INTO @Notes (Note)
SELECT @Note

UPDATE dbo.BankNotes
SET Available = Available - 1
WHERE FaceValue = @Note

SET @Amount = @Amount - @Note

END
SELECT * FROM @NOTES
END

GO
thanxs once again Sergiy

thanxs
Re: best possibilities [message #198435 is a reply to message #198419] Tue, 17 October 2006 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something like this:

CREATE TABLE Notes (note_value   number);

create table banknotes (facevalue  number, available number);

insert into banknotes values (50,10);
insert into banknotes values (20,10);
insert into banknotes values (10,10);
insert into banknotes values (5,10);
insert into banknotes values (2,10);
insert into banknotes values (1,10);

CREATE OR REPLACE PROCEDURE BAL (p_amount IN NUMBER) AS

  cursor c_highest_note (p_amount in  number) is
    SELECT facevalue
    FROM   banknotes
    WHERE  facevalue <= p_amount
    and    available > 0
    order by facevalue desc
    for update of available;
    
  v_remaining   number := p_amount;  -- holds cash left to allocate    
  v_note        number;
    
begin

  WHILE v_remaining > 0 LOOP
    open  c_highest_note(v_remaining);
    fetch c_highest_note into v_note;
    
    IF c_highest_note%notfound then -- no remaining notes
      rollback;
      raise_application_error(-20001,'Not enough notes available');
    end if;

    insert into notes (note_value) values (v_note);
    update banknotes set available = available-1 where current of c_highest_note;
    v_remaining := v_remaining - v_note;
    close c_highest_note;

    dbms_output.put_line('Note value '||v_note||' remaining ');
  
  END LOOP;

end bal;

SQL> begin
  2    bal(137);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from notes;

NOTE_VALUE
----------
        50
        50
        20
        10
         5
         2

6 rows selected.

SQL> select * from banknotes;

 FACEVALUE  AVAILABLE
---------- ----------
        50          8
        20          9
        10          9
         5          9
         2          9
         1         10

6 rows selected.
Re: best possibilities [message #198449 is a reply to message #198435] Tue, 17 October 2006 04:04 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Very Good Explanation.
Re: best possibilities [message #198660 is a reply to message #198449] Wed, 18 October 2006 02:43 Go to previous message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
PLS
see i will put in a brief note what i need
if x person goes for shopping when he/she pays bill at cash counter his bill amount is 986.But the customer x has 1000 rupees in his hand and he pays the bill he should get back/balance amount 14 rupees back right
in this scenario the cash counter should give x person the least or less number of notes best probabilities
eg: in cash counter he has currency denomination 1000,500,100,50,20,10,9,7,5,2,1 etc rupee currency notes
where 10+2+2=14 or 2+2+2+2+2+2+2=14 logically this correct but actually cash counter should give customer 7+7=14 because this is the least/less currency denomination notes(count)
i should have a query which will fetch least/less number(count) of currency notes
note: cash counter should give minimum number of currency notes
i think we should use function/dynamic query array algorithm
in my database i have different denomination for 14 rupees best possibilities is 7+7=14 not 10+2+2=14 or 2+2+2+2+2+2+2=14
because i get 3 or 7 currency notes (count) but 7+7=14 is and 9+5=14 (this best answer)correct because it returns 2 currency notes(count)



thanks in advance all of u
Previous Topic: best denomination notes
Next Topic: case and Grouping
Goto Forum:
  


Current Time: Sat Dec 10 07:05:24 CST 2016

Total time taken to generate the page: 0.04861 seconds