select least currency notes - 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

SQL Server 2000 Forums

Transact-SQL (2000)

select least currency notes

Author

Topic

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-17 : 03:18:15

hi
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

database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 03:30:14

You mean this FUNCTION posted to you earlier?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540

Where is the question this time? I don't follow you.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 03:33:07

Peter,

Have you changed your name to "Sergiy"?

Apparently, your logic was way too hi-fi for him/her to understand

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 03:37:20

No, haven't changed name. I think someone posted to him by message or email and he doesn't want to share with us.

He rewrote the code with his own column names and table names, so I don't think so.
I think the problem is that he does not know exactly what he wants.
If he did, he could write the problem statement more clearly...

If the problem is that he want the algorithm "reverted", that is, he wants all the lower bills first, why doesn't he tell us?
And more valuable, why would he want that? Then all there is, is to return everything in 1 rupee bills!

Above he writes about "7" bills. Is there anywhere in the world bills with the value of 7?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 03:44:01

quote:
Is there anywhere in the world bills with the value of 7?

At least not here in India

But even that was handled in your previous SP, right?

I mean the only change he has to make is to add an entry for Rs.7 in the @CashCounter table, Isn't it?

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 03:46:48

Seems right to me. But that case (with 7) only happens when the 10's are out.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 03:55:56

I think this is just some kind of SQL puzzle, nothing to do with real-time scenario. Although his condition can be incorporated but that will unncessarily complicate the logic.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 04:19:44

I got an idea for the experiment above. If he adds a new bit column named PREFERRED, and change the MAX query to

SELECT TOP 1 @Note = FaceValue FROM dbo.BankNotes
WHERE FaceValue <= @Amount and Available > 0
ORDER BY Preferred DESC, FaceValue DESC

he can easily switch between which notes that are to be taken first.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 05:39:55

Ingenious indeed ! (Of course, if he prefers to add PREFERRED column to the table)

I think this problem is somewhat similar to Dijkstra's shortest path problem or mimimum spanning tree problem

Harsh Athalye
India.
"Nothing is Impossible"

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-17 : 05:44:35

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 function/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

database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 05:49:31

Why not 9 and 5? This is two notes (count) also...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 06:05:51

Got it covered now. If 9 and 5 can be treated equally as 7 and 7, just remove the WHERE part with POWER.

-- Prepare test data
DECLARE	@Bills TABLE (Value INT)

INSERT	@Bills
SELECT	1000 UNION ALL
SELECT	500 UNION ALL
SELECT	100 UNION ALL
SELECT	50 UNION ALL
SELECT	20 UNION ALL
SELECT	10 UNION ALL
SELECT	9 UNION ALL
SELECT	7 UNION ALL
SELECT	5 UNION ALL
SELECT	2 UNION ALL
SELECT	1 UNION ALL
SELECT	0

-- Stage the data
DECLARE @Amount INT

SELECT	@Amount = 14

DECLARE	@Stage TABLE (Bill1 INT, Bill2 INT, Bill3 INT, Bill4 INT, Bill5 INT)

INSERT		@Stage
SELECT TOP 1	b1.Value,
		b2.Value,
		b3.Value,
		b4.Value,
		b5.Value
FROM		@Bills b1
CROSS JOIN	@Bills b2
CROSS JOIN	@Bills b3
CROSS JOIN	@Bills b4
CROSS JOIN	@Bills b5
WHERE		b1.Value + b2.Value + b3.Value + b4.Value + b5.Value = @Amount
ORDER BY	SIGN(b1.Value) + SIGN(b2.Value) + SIGN(b3.Value) + SIGN(b4.Value) + SIGN(b5.Value),
		POWER(b1.Value, 2) + POWER(b2.Value, 2) + POWER(b3.Value, 2) + POWER(b4.Value, 2) + POWER(b5.Value, 2)

-- Show the data
SELECT		COUNT(*) Items,
		d.Bill
FROM		(
			SELECT		Bill1 Bill
			FROM		@Stage
			UNION ALL
			SELECT		Bill2
			FROM		@Stage
			UNION ALL
			SELECT		Bill3
			FROM		@Stage
			UNION ALL
			SELECT		Bill4
			FROM		@Stage
			UNION ALL
			SELECT		Bill5
			FROM		@Stage
		) d
WHERE		d.Bill > 0		
GROUP BY	d.Bill

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 06:14:43

quote:
Originally posted by harsh_athalye

Peter,

Have you changed your name to "Sergiy"?

No, Sergiy is found here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=315535
where oracle_corrgi also put this query.
And it seems Sergiy copied the algorithm from me, an hour later...

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-17 : 06:21:03

good i did not think of this

database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 06:28:37

I realize you didn't. That's why you ask for help in all places.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 06:58:16

Here is another alternative of you want all possible solutions for change.

-- Prepare test data
DECLARE	@Bills TABLE (Value INT)

INSERT	@Bills
SELECT	1000 UNION ALL
SELECT	500 UNION ALL
SELECT	100 UNION ALL
SELECT	50 UNION ALL
SELECT	20 UNION ALL
SELECT	10 UNION ALL
SELECT	9 UNION ALL
SELECT	7 UNION ALL
SELECT	5 UNION ALL
SELECT	2 UNION ALL
SELECT	1 UNION ALL
SELECT	0

-- Stage the data
DECLARE @Amount INT

SELECT	@Amount = 14

DECLARE	@Stage TABLE (Row INT IDENTITY(0, 1), Bill1 INT, Bill2 INT, Bill3 INT, Bill4 INT, Bill5 INT)

INSERT		@Stage
		(
			Bill1,
			Bill2,
			Bill3,
			Bill4,
			Bill5
		)
SELECT		b1.Value,
		b2.Value,
		b3.Value,
		b4.Value,
		b5.Value
FROM		@Bills b1
CROSS JOIN	@Bills b2
CROSS JOIN	@Bills b3
CROSS JOIN	@Bills b4
CROSS JOIN	@Bills b5
WHERE		b1.Value + b2.Value + b3.Value + b4.Value + b5.Value = @Amount
ORDER BY	SIGN(b1.Value) + SIGN(b2.Value) + SIGN(b3.Value) + SIGN(b4.Value) + SIGN(b5.Value),
		POWER(b1.Value, 2) + POWER(b2.Value, 2) + POWER(b3.Value, 2) + POWER(b4.Value, 2) + POWER(b5.Value, 2)

DECLARE	@Temp TABLE (Row INT, Bill INT, Items INT)

INSERT		@Temp
		(
			Row,
			Bill,
			Items
		)
SELECT		d.Row,
		d.Bill,
		COUNT(*) Items
FROM		(
			SELECT		Row,
					Bill1 Bill
			FROM		@Stage
			UNION ALL
			SELECT		Row,
					Bill2
			FROM		@Stage
			UNION ALL
			SELECT		Row,
					Bill3
			FROM		@Stage
			UNION ALL
			SELECT		Row,
					Bill4
			FROM		@Stage
			UNION ALL
			SELECT		Row,
					Bill5
			FROM		@Stage
		) d
WHERE		d.Bill > 0		
GROUP BY	d.Row,
		d.Bill

DECLARE	@Buffer TABLE (Row INT, Bill INT, Items INT)

INSERT		@buffer
		(
			Row,
			Bill,
			Items
		)
select		t1.row,
		t1.bill,
		t1.items
from		@temp t1
inner join	@temp t2 on t2.bill = t1.bill and t2.items = t1.items
group by	t1.row, t1.bill, t1.items
having		t1.row = min(t2.row)

delete b
from @buffer b
where	b.row in (select a.row from @buffer a group by a.row having sum(a.bill * a.items) <> @amount)

select		(SELECT COUNT(DISTINCT b.Row) FROM @Buffer b WHERE b.Row <= q.Row) Alternative,
		q.Items,
		q.Bill
from		@Buffer q
order by	(select count(*) from @buffer b where b.row = q.row)

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-17 : 07:02:57

hi
thanxs peter i am dry running this i think this is the solution i needed ,i will confrim it and let u know
thanxs

database

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-17 : 09:13:16

hi
thanxs peter i dry ran this Posted - 10/17/2006 : 06:05:51
i think there is a SMALL BUG
comment ----SELECT 100 UNION ALL
give SELECT @Amount = 213 or 211
it does not retrun any value null data is retruned

thanxs

database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 10:03:38

That's because you now need 6 notes to give back.

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-17 : 21:17:15

hi Peter Good morning
my question
i think there is a SMALL BUG
comment ----SELECT 100 UNION ALL
give SELECT @Amount = 213 or 211
it does not retrun any value null data is retruned
is that output for some amount does not return eg: 950,900,850,800,213,312 etc why

one more question i have created procedure according to Posted - 10/17/2006 : 06:05:51

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure oldfinbal (@Amount money)
as
BEGIN
DECLARE @Bills TABLE (Value INT)
DECLARE @Stage TABLE (Bill1 INT, Bill2 INT, Bill3 INT, Bill4 INT, Bill5 INT)

INSERT @Stage
SELECT TOP 1 b1.Value,
b2.Value,
b3.Value,
b4.Value,
b5.Value
FROM @Bills b1
CROSS JOIN @Bills b2
CROSS JOIN @Bills b3
CROSS JOIN @Bills b4
CROSS JOIN @Bills b5
WHERE b1.Value + b2.Value + b3.Value + b4.Value + b5.Value = @Amount
ORDER BY SIGN(b1.Value) + SIGN(b2.Value) + SIGN(b3.Value) + SIGN(b4.Value) + SIGN(b5.Value),
POWER(b1.Value, 2) + POWER(b2.Value, 2) + POWER(b3.Value, 2) + POWER(b4.Value, 2) + POWER(b5.Value, 2)

-- Show the data
SELECT COUNT(*) Items,
d.Bill
FROM (
SELECT Bill1 Bill
FROM @Stage
UNION ALL
SELECT Bill2
FROM @Stage
UNION ALL
SELECT Bill3
FROM @Stage
UNION ALL
SELECT Bill4
FROM @Stage
UNION ALL
SELECT Bill5
FROM @Stage
) d
WHERE d.Bill > 0
GROUP BY d.Bill

SELECT * FROM @stage
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

this also retruns me no values why?

thanxs

database

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 22:12:23

quote:
this also retruns me no values why?

That's because you are inserting nothing in @Bills table ! Insert currencies in @Bills table

Harsh Athalye
India.
"Nothing is Impossible"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources