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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 select least currency notes
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/17/2006 :  03:18:15  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 10/17/2006 :  03:30:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/17/2006 :  03:33:07  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  03:37:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 10/17/2006 03:41:53
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/17/2006 :  03:44:01  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  03:46:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Seems right to me. But that case (with 7) only happens when the 10's are out.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/17/2006 :  03:55:56  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  04:19:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/17/2006 :  05:39:55  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/17/2006 :  05:44:35  Show Profile  Reply with Quote
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

Edited by - oracle_corrgi on 10/17/2006 05:50:48
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  05:49:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why not 9 and 5? This is two notes (count) also...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  06:05:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 10/17/2006 06:06:04
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  06:14:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/17/2006 :  06:21:03  Show Profile  Reply with Quote
good i did not think of this

database
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  06:28:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I realize you didn't. That's why you ask for help in all places.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  06:58:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/17/2006 :  07:02:57  Show Profile  Reply with Quote
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
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/17/2006 :  09:13:16  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/17/2006 :  10:03:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
That's because you now need 6 notes to give back.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 10/17/2006 :  21:17:15  Show Profile  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/17/2006 :  22:12:23  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000