SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 best possiblites
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/16/2006 :  02:08:28  Show Profile
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
30281 Posts

Posted - 10/16/2006 :  02:23:30  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 10/16/2006 :  02:45:59  Show Profile
This all sounds very good. Just wondering how accurate it will be for the notes in the cash drawer against the POS.


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/16/2006 :  03:37:46  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 10/16/2006 :  03:48:39  Show Profile
Well you can't help if the cashier decided to give 2 50 notes instead of a 100 note


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/16/2006 :  03:51:29  Show Profile  Visit SwePeso's Homepage
Certainly not. But that's an front end decision, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 10/16/2006 :  04:05:23  Show Profile
LOL ! Yes. You are absolutely correct


KH

Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/17/2006 :  05:58:40  Show Profile
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/17/2006 :  06:07:11  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/18/2006 :  14:00:47  Show Profile  Visit SwePeso's Homepage
http://www.sqlteam.com/forums/topic.asp?whichpage=2&TOPIC_ID=73610#264068


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 10/18/2006 :  14:35:23  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000