best possibilities
-
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
thanxs
-
Hi,
What precisely do you need?
Are you looking for a database design, or is the database already in place and you only need to create a query that will propose the notes to be returned? If the database exists, post table DDL (CREATE TABLE ....) and some sample data (INSERT INTO ....), so that we can think in the environment you use.
-
It sounds like the classic "make change" problem that used to be taught in many programming classes... I'm on my way to work right now but I think a function that returns a table variable that contains a listing of the bills used to make the change is what is needed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Didn't think much, but the simplest approach seems to be this:
CREATE TABLE dbo.BankNotes (
ID smallint IDENTITY(1,1) NOT NULL,
FaceValue smallmoney NOT NULL,
Available smallint NOT NULL -- Number of available notes
)
CREATE UNIQUE CLUSTERED INDEX BankNotes_Value ON dbo.BankNotes(FaceValue)
INSERT INTO dbo.BankNotes (FaceValue, Available)
SELECT 100, 5
UNION
SELECT 50, 20
UNION
SELECT 20, 100
UNION
SELECT 10, 100
UNION
SELECT 5, 100
UNION
SELECT 2, 100
UNION
SELECT 1, 100
-- That you must already have in DB
---------------------
-- This must become your function:
declare @Amount money, @Note smallmoney
DECLARE @Notes TABLE (Note smallmoney NOT NULL)
SET @Amount = 365
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
_____________
Code for TallyGenerator -
hi Sergiy
thanxs a lot Sergiy this seems to logicially work out for me ,this is one way of getting the currency notes . (this is the right track or apporach)
I HAVE created procedure with this procedure i will get only the greatest/highest amount
for eg: 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
-
hi coorgi,
you have edited your message before I managed to reply
... so now my reply seemed a bit out of place 
... I deleted it. -
sorry for that
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
thanxs
-
-
hi
i did not change my name i just appreciated sergiy for the response/reply once again
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 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 dynamic query array algorithm
this my script / procedure
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
when i run this i get output
>bal 14
10.0000
2.0000
2.0000
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
because i get 3 or 7 currency notes (count) but 7+7=14 is correct because it returns 2 currency notes(count)
thanks in advance all of u
-
Already answered here!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610
N 56°04'39.16"
E 12°55'05.25" -
Actually I don't see why 9+5 is wrong.
In difference between denominations less than 2 times there are too many options.
You need to work out more complex algorythm.
_____________
Code for TallyGenerator -
Peter, are you insane?
For many years here nobody saw me copying anyone's algorythm.
Are you sure that one is good enough to start?
I even avoid to copy my own solutions, always provide links when I can find old posts.
It's obvious that script I posted was quickly baked "on the knee" at the morning. I did not even bother to wrap it into function or procedure.
Not a rocket technology, I have a lot of such "genius" solutions in every project.
BTW, thanks for the link, never have been to that forum.
And your final solution is not absolutely right as well.
It will prefer 9+5 before 7+7, despite of they are equal.
And it cannot handle more than 5 notes.
_____________
Code for TallyGenerator -
I did not call you insane. Believe me. You are one of the clever people here.
I call oracle_corrgi insane. It is not good to put same question on several forums, occupying more people with same problem.
And oracle_corrig did not even care to "anonymize" your solution when posting back at sqlteam.
I apologize if I have insulted you. It was not my intention.
Five notes should be enough for any change up to 999 rupees with all the notes ha has now.
I am working on a solution which iterates until the correct change has come up with as few notes as possible.
My algorithm will prefer 7+7 before 9+5 it both order by are there (SIGN and POWER).
N 56°04'39.16"
E 12°55'05.25" -
And welcome to SQLTeam, you are much needed there.
N 56°04'39.16"
E 12°55'05.25" -
And performance must be terrible.
5 cross joins - I'm not sure it's something I would ever accept. I stopped thinking about solution with Numbers table when I realised it gonna have not less than 3 cross joins.
Yes, table @bills is small, only 12 rows, but POWER(12,5) gives you 248832 rows in hash table. And than you need to scan that table looking for
b1.Value + b2.Value + b3.Value + b4.Value + b5.Value = @Amount.
I don't want even try it on my server.
P.S. Of course, you did not intent to insult anybody, especially by this:
"And it seems Sergiy copied the algorithm from me, an hour later..."
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply