| Author |
 Topic  |
|
|
oracle_corrgi
Yak Posting Veteran
India
98 Posts |
 Posted - 10/16/2006 : 02:08:28
|
hi
can u pls let me know how do query and go about to do this
i need to pay the bill say 635 rupees indian currency in the cash counter for eg:keb bill
i have/pay 1000 i should get 365 back
in the cash countersay dinomination is 1000,500,100,50,20,10,5,2,1 rupee
i should get the best possiblites
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)
database |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 02:23:30
|
CREATE FUNCTION dbo.fnCashReturn
(
@Prize INT,
@Paid INT
)
RETURNS @Out TABLE (BillSize INT)
AS
BEGIN
DECLARE @CashCounter TABLE (BillSize INT, Items INT)
-- This code below should come from your real cash counter table
INSERT @CashCounter
SELECT 1000, 1 UNION ALL
SELECT 500, 10 UNION ALL
SELECT 100, 1 UNION ALL
SELECT 50, 2 UNION ALL
SELECT 20, 10 UNION ALL
SELECT 10, 40 UNION ALL
SELECT 5, 250 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 1, 9
-- This code above should come from your real cash counter table
DECLARE @Return INT,
@BillSize INT
SELECT @Return = @Paid - @Prize
WHILE @Return > 0
BEGIN
SELECT @BillSize = MAX(BillSize)
FROM @CashCounter
WHERE BillSize <= @Return
AND Items > 0
INSERT @Out
SELECT @BillSize
SELECT @Return = @Return - @BillSize
UPDATE @CashCounter
SET Items = Items - 1
WHERE BillSize = @BillSize
END
RETURN
END
Call with either select *
from dbo.fnCashReturn(635, 1000)
order by BillSize desc or
select COUNT(*) Items,
BillSize
from dbo.fnCashReturn(635, 1000)
GROUP BY BillSize
order by BillSize desc
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/16/2006 02:26:59 |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 02:45:59
|
This all sounds very good. Just wondering how accurate it will be for the notes in the cash drawer against the POS.
KH
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 03:37:46
|
Can be, it the code is turned into a SP and work directly against POS.
Some Foreign Currency Exchange Offices still works this way. I wrote an app in Pascal in spring 1990, and that office still uses my app.
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/16/2006 03:38:45 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 03:48:39
|
Well you can't help if the cashier decided to give 2 50 notes instead of a 100 note 
KH
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 03:51:29
|
Certainly not. But that's an front end decision, right? 
Peter Larsson
Helsingborg, Sweden |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/16/2006 : 04:05:23
|
LOL ! Yes. You are absolutely correct
KH
|
|
|
|
oracle_corrgi
Yak Posting Veteran
India
98 Posts |
Posted - 10/17/2006 : 05:58:40
|
hi
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 demoniation 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 demoniation 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 demoniation for 14 rupees best possibilities is 7+7=14 not 10+2+2=14 or 2+2+2+2+2+2+2=14
i ran ur function
it retuns me
1 100
2 50
8 20
1 5
thanxs
database |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
| |
Topic |
|
best possiblites
Forum Locked
