| Author |
 Topic  |
|
oracle_corrgi
Yak Posting Veteran
India
98 Posts |
 Posted - 10/17/2006 : 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
Sweden
29156 Posts |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/17/2006 : 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
Sweden
29156 Posts |
Posted - 10/17/2006 : 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 |
Edited by - SwePeso on 10/17/2006 03:41:53 |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/17/2006 : 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
Sweden
29156 Posts |
Posted - 10/17/2006 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/17/2006 : 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
Sweden
29156 Posts |
Posted - 10/17/2006 : 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 toSELECT 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/17/2006 : 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
India
98 Posts |
Posted - 10/17/2006 : 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 |
Edited by - oracle_corrgi on 10/17/2006 05:50:48 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/17/2006 : 05:49:31
|
Why not 9 and 5? This is two notes (count) also...
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/17/2006 : 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 |
Edited by - SwePeso on 10/17/2006 06:06:04 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
|
|
oracle_corrgi
Yak Posting Veteran
India
98 Posts |
Posted - 10/17/2006 : 06:21:03
|
good i did not think of this
database |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/17/2006 : 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
Sweden
29156 Posts |
Posted - 10/17/2006 : 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
India
98 Posts |
Posted - 10/17/2006 : 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
India
98 Posts |
Posted - 10/17/2006 : 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
Sweden
29156 Posts |
Posted - 10/17/2006 : 10:03:38
|
That's because you now need 6 notes to give back.
Peter Larsson
Helsingborg, Sweden |
|
|
|
oracle_corrgi
Yak Posting Veteran
India
98 Posts |
Posted - 10/17/2006 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/17/2006 : 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" |
|
|
|
Topic |
|
select least currency notes
