best possiblites - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

best possiblites

Author

Topic

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-16 : 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

30421 Posts

Posted - 2006-10-16 : 02:23:30

[code]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[/code]
Call with either [code]select *
from dbo.fnCashReturn(635, 1000)
order by BillSize desc[/code]or
[code]select COUNT(*) Items,
BillSize
from dbo.fnCashReturn(635, 1000)
GROUP BY BillSize
order by BillSize desc[/code]
Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 03:48:39

Well you can't help if the cashier decided to give 2 50 notes instead of a 100 note

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 03:51:29

Certainly not. But that's an front end decision, right?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 04:05:23

LOL ! Yes. You are absolutely correct

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-17 : 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

30421 Posts

Posted - 2006-10-17 : 06:07:11

Are you insane?

You already have started this new question here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610
AND got an answer too...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-18 : 14:00:47

http://www.sqlteam.com/forums/topic.asp?whichpage=2&TOPIC_ID=73610#264068

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-18 : 14:35:23

quote:
Are you insane?

Rhetorical question i assume

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources