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
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/17/2006 :  22:15:36  Show Profile  Reply with Quote
bills table data
Value
1000
500
100
50
20
10
9
7
5
2
1
0


database
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/17/2006 :  22:18:23  Show Profile  Reply with Quote
bills table data
Value
1000
500
100
50
20
10
9
7
5
2
1
0


database
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/17/2006 :  22:19:40  Show Profile  Reply with Quote
hi
there is all data currencies in the bill table
thanxs

database
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  00:56:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Extend the cross joins to hold 6 joins and corresponding WHEREs.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/18/2006 :  02:26:38  Show Profile  Reply with Quote
hi
any one there to help me out

thanxs


coorgi
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/18/2006 :  02:28:22  Show Profile  Reply with Quote
hi
every body there i could not find a sloution yet nor any one is continuing with this topic

thanxs



coorgi
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  02:34:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Here is a temporary solution for you.
-- 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, Bill6 INT)

INSERT		@Stage
SELECT TOP 1	b1.Value,
		b2.Value,
		b3.Value,
		b4.Value,
		b5.Value,
		b6.Value
FROM		@Bills b1
CROSS JOIN	@Bills b2
CROSS JOIN	@Bills b3
CROSS JOIN	@Bills b4
CROSS JOIN	@Bills b5
CROSS JOIN	@Bills b6
WHERE		b1.Value + b2.Value + b3.Value + b4.Value + b5.Value + b6.Value = @Amount
ORDER BY	SIGN(b1.Value) + SIGN(b2.Value) + SIGN(b3.Value) + SIGN(b4.Value) + SIGN(b5.Value) + SIGN(b6.Value),
		POWER(b1.Value, 2) + POWER(b2.Value, 2) + POWER(b3.Value, 2) + POWER(b4.Value, 2) + POWER(b5.Value, 2) + POWER(b6.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
			UNION ALL
			SELECT		Bill6
			FROM		@Stage
		) d
WHERE		d.Bill > 0		
GROUP BY	d.Bill


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  02:35:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Next time, try to understand the suggestion and you will find that extending the code is easy.
Or, you can try to come up with a solution of your own.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/18/2006 :  02:47:43  Show Profile  Reply with Quote
hi
pls dry run this i have commented ---SELECT 100 UNION ALL --SELECT 50 UNION ALL

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 = 213

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

INSERT @Stage
SELECT TOP 1 b1.Value,
b2.Value,
b3.Value,
b4.Value,
b5.Value,
b6.Value
FROM @Bills b1
CROSS JOIN @Bills b2
CROSS JOIN @Bills b3
CROSS JOIN @Bills b4
CROSS JOIN @Bills b5
CROSS JOIN @Bills b6
WHERE b1.Value + b2.Value + b3.Value + b4.Value + b5.Value + b6.Value = @Amount
ORDER BY SIGN(b1.Value) + SIGN(b2.Value) + SIGN(b3.Value) + SIGN(b4.Value) + SIGN(b5.Value) + SIGN(b6.Value),
POWER(b1.Value, 2) + POWER(b2.Value, 2) + POWER(b3.Value, 2) + POWER(b4.Value, 2) + POWER(b5.Value, 2) + POWER(b6.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
UNION ALL
SELECT Bill6
FROM @Stage
) d
WHERE d.Bill > 0
GROUP BY d.Bill

say if i dont have 100 or 50 value then i am getting null record thats not correct right
pls dry run this



coorgi
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/18/2006 :  02:50:08  Show Profile  Reply with Quote
i dont find any loop
eg:WHILE @Amount > 0
BEGIN
SELECT @BILLS = MAX(Value)
FROM dbo.Bills
WHERE Value <= @Amount and Available > 0

coorgi
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 10/18/2006 :  02:51:00  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
So Peter,

in this cross join solution as the factor of the amount increases, you will have to increase the number of cross joins?

For e.g. say amount 1950 can be factorised into

1000 + 500 + 100 + 100 + 100 + 100 + 50

but since we are having only 6 bills, we can't find out bills for this amount unless we add one more cross join, right?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/18/2006 :  02:57:49  Show Profile  Reply with Quote
hope iam not confusing u all ? i need ur help


coorgi
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  03:26:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. As long as you definitely want an optimization in "least bills back".
If you don't care what number of bills to be handed over to customer, use code posted earlier.

I am working on an algorithm where you don't have to add CROSS JOINs to suite the needs.
SO PLEASE BE PATIENT, or I will loose my interest and leave you on your own.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/18/2006 :  03:40:54  Show Profile  Reply with Quote
ok

coorgi
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  06:12:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This will work, bit it can take a lot of time...
SET NOCOUNT ON

DECLARE @Amount INT

SELECT	@Amount = 14--212

CREATE TABLE #BillTypes (FaceValue INT)

INSERT	#BillTypes
	(
		FaceValue
	)
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

DELETE
FROM	#BillTypes
WHERE	FaceValue > @Amount

DECLARE @Index INT,
	@MaxIndex INT,
	@SQL VARCHAR(8000),
	@INSERT VARCHAR(8000),
	@SELECT VARCHAR(8000),
	@FROM VARCHAR(8000),
	@WHERE VARCHAR(8000),
	@ORDERBY1 VARCHAR(8000),
	@ORDERBY2 VARCHAR(8000)

SELECT	@Index = 1,
	@MaxIndex = 1 + COUNT(*)
FROM	#BillTypes

CREATE TABLE #Stage (RowID INT IDENTITY(0, 1), Bills INT)

WHILE @Index <= @MaxIndex
	BEGIN
		SELECT	@SQL = 'ALTER TABLE #Stage ADD Bill' + CONVERT(varchar, @Index) + ' INT',
			@INSERT = CASE
					WHEN @INSERT IS NULL THEN 'INSERT #Stage (Bill' + CONVERT(varchar, @Index)
					ELSE @INSERT + ',Bill' + CONVERT(varchar, @Index)
				  END,
			@SELECT = CASE
					WHEN @SELECT IS NULL THEN ' SELECT TOP 1 Bill' + CONVERT(varchar, @Index) + '.FaceValue'
					ELSE @SELECT + ',Bill' + CONVERT(varchar, @Index) + '.FaceValue'
				  END,
			@FROM = CASE
					WHEN @FROM IS NULL THEN ' FROM #BillTypes Bill' + CONVERT(varchar, @Index)
					ELSE @FROM + ' CROSS JOIN #BillTypes Bill' + CONVERT(varchar, @Index)
				  END,
			@WHERE = CASE
					WHEN @WHERE IS NULL THEN ' WHERE Bill' + CONVERT(varchar, @Index) + '.FaceValue'
					ELSE @WHERE + '+Bill' + CONVERT(varchar, @Index) + '.FaceValue'
				  END,
			@ORDERBY1 = CASE
					WHEN @ORDERBY1 IS NULL THEN 'SIGN(Bill' + CONVERT(varchar, @Index) + '.FaceValue)'
					ELSE @ORDERBY1 + '+SIGN(Bill' + CONVERT(varchar, @Index) + '.FaceValue)'
				  END,
			@ORDERBY2 = CASE
					WHEN @ORDERBY2 IS NULL THEN ',POWER(Bill' + CONVERT(varchar, @Index) + '.FaceValue,2)'
					ELSE @ORDERBY2 + '+POWER(Bill' + CONVERT(varchar, @Index) + '.FaceValue,2)'
				  END,
			@Index = @Index + 1

		EXEC	(@SQL)
	END

SELECT	@INSERT = @INSERT + ',Bills)',
	@SELECT = @SELECT + ',' + @ORDERBY1,
	@WHERE = @WHERE + ' = ' + CONVERT(varchar, @Amount),
	@ORDERBY1 = ' ORDER BY ' + @ORDERBY1

EXEC	(@INSERT + @SELECT + @FROM + @WHERE + @ORDERBY1 + @ORDERBY2)

DELETE	s
FROM	#Stage s
WHERE	s.Bills <> (SELECT x.Bills FROM #Stage x WHERE x.RowID = 0)

CREATE TABLE	#Temp (RowID INT, Items INT, FaceValue INT)

SELECT	@Index = 1,
	@SQL = 'INSERT #Temp (RowID,Items,FaceValue) SELECT d.RowID,COUNT(*),d.FaceValue FROM (',
	@FROM = NULL

WHILE @Index <= @MaxIndex
	SELECT	@FROM = CASE
				WHEN @FROM IS NULL THEN 'SELECT RowID, Bill' + CONVERT(varchar, @Index) + ' FaceValue FROM #Stage'
				ELSE @FROM + ' UNION ALL SELECT RowID, Bill' + CONVERT(varchar, @Index) + ' FROM #Stage'
			  END,
		@Index = @Index + 1

EXEC	(@SQL + @FROM + ')d WHERE d.FaceValue > 0 GROUP BY d.RowID,d.FaceValue ORDER BY d.RowID,d.FaceValue')

DROP TABLE #Stage
DROP TABLE #BillTypes

SELECT		SUM(DISTINCT Items) Bills,
		FaceValue
FROM		#Temp
GROUP BY	RowID,
		FaceValue
ORDER BY	RowID DESC,
		FaceValue

DROP TABLE #Temp


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/18/2006 :  06:23:59  Show Profile  Reply with Quote
thanxs peter shell i run and let u know


coorgi
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  09:52:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Now I have a fast solution too, which also works with asp pages, because the output columns always are the same (equal to available unique bills).

First create this function
CREATE FUNCTION dbo.fnLCM
(
	@Number1 INT,
	@Number2 INT
)
RETURNS INT
AS

BEGIN
	DECLARE	@Temp INT,
		@GCD INT

	SELECT	@GCD = @Number1 * @Number2

	WHILE @Number1 % @Number2 <> 0
		SELECT	@Temp = @Number1,
			@Number1 = @Number2,
			@Number2 = @Temp % @Number2

	RETURN	@GCD / @Number2
END
and then create this stored procedure
CREATE PROCEDURE dbo.uspMoneyBack
(
	@Amount INT
)
AS

SET NOCOUNT ON

DECLARE	@BillTypes TABLE
	(
		BillItem INT,
		FaceValue INT
	)

INSERT	@BillTypes
	(
		FaceValue
	)
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

UPDATE	bt
SET	bt.BillItem = (SELECT COUNT(DISTINCT x.FaceValue) FROM @BillTypes x WHERE x.FaceValue < bt.FaceValue)
FROM	@BillTypes bt

-- IF NOT EXISTS (SELECT * FROM @BillTypes WHERE @Amount % FaceValue = 0)
-- 	RETURN

DECLARE	@BillItem INT,
	@MaxBillItem INT,
	@FaceValue INT,
	@NextFaceValue INT,
	@Select1 VARCHAR(8000),
	@Select2 VARCHAR(8000),
	@From1 VARCHAR(8000),
	@From2 VARCHAR(8000),
	@SQL VARCHAR(8000),
	@Where VARCHAR(8000),
	@OrderBy1 VARCHAR(8000),
	@OrderBy2 VARCHAR(8000),
	@Output VARCHAR(8000)

SELECT	@BillItem = MIN(BillItem),
	@MaxBillItem = MAX(BillItem)
FROM	@BillTypes

WHILE @BillItem <= @MaxBillItem
	BEGIN
		SELECT	@FaceValue = MIN(FaceValue)
		FROM	@BillTypes
		WHERE	BillItem = @BillItem

		SELECT	@NextFaceValue = MIN(FaceValue)
		FROM	@BillTypes
		WHERE	BillItem = @BillItem + 1

		SELECT	@Select1 =	CASE
						WHEN @Select1 IS NULL THEN 'SELECT n' + CONVERT(varchar, @BillItem)
						ELSE @Select1 + ' + n' + CONVERT(varchar, @BillItem)
					END,
			@Select2 =	CASE
						WHEN @Select2 IS NULL THEN ', n' + CONVERT(varchar, @BillItem) + ' ''' + CONVERT(varchar, @FaceValue) + ''''
						ELSE @Select2 + ', n' + CONVERT(varchar, @BillItem) + ' ''' + CONVERT(varchar, @FaceValue) + ''''
					END,
			@From1 =	CASE
						WHEN @From1 IS NULL THEN 'SELECT b' + CONVERT(varchar, @BillItem) + '.Number n' + CONVERT(varchar, @BillItem)
						ELSE @From1 + ', b' + CONVERT(varchar, @BillItem) + '.Number n' + CONVERT(varchar, @BillItem)
					END,
			@SQL =		CASE
						WHEN @NextFaceValue >= @Amount OR @NextFaceValue IS NULL THEN CONVERT(varchar, @Amount / @FaceValue)
						WHEN @FaceValue > @Amount THEN '0'
						ELSE CONVERT(varchar, dbo.fnLCM(@FaceValue, @NextFaceValue) / @FaceValue - 1)
					END,
			@From2 =	CASE
						WHEN @From2 IS NULL THEN ' FROM '
						ELSE @From2 + ' CROSS JOIN '
					END + '(SELECT DISTINCT Number FROM master.dbo.spt_values WHERE Number BETWEEN 0 AND ' + @SQL + ') b' + CONVERT(varchar, @BillItem),
			@Where =	CASE
						WHEN @Where IS NULL THEN ' WHERE ' + CONVERT(varchar, @FaceValue) + ' * n' + CONVERT(varchar, @BillItem)
						ELSE @Where + ' + '+ CONVERT(varchar, @FaceValue) + ' * n' + CONVERT(varchar, @BillItem)
					END,
			@OrderBy1 =	CASE
						WHEN @OrderBy1 IS NULL THEN ' ORDER BY n' + CONVERT(varchar, @BillItem)
						ELSE @OrderBy1 + ' + n' + CONVERT(varchar, @BillItem)
					END,
			@OrderBy2 =	CASE
						WHEN @OrderBy2 IS NULL THEN ', SIGN(n' + CONVERT(varchar, @BillItem) + ')'
						ELSE @OrderBy2 + ' + SIGN(n' + CONVERT(varchar, @BillItem) + ')'
					END,
			@BillItem = @BillItem + 1
	END

SELECT	@Select1 = @Select1 + ' Bills',
	@Select2 = @Select2 + ' INTO ##GiveAway FROM (',
	@From2 = @From2 + ') x',
	@Where = @Where + ' = ' + CONVERT(varchar, @Amount),
	@SQL = @Select1 + @Select2 + @From1 + @From2 + @Where + @OrderBy1 + @OrderBy2

EXEC	(@SQL)

DELETE	g
FROM	##GiveAway g
WHERE	g.Bills <> (SELECT MIN(x.Bills) FROM ##GiveAway x)

SELECT	*
FROM	##GiveAway

DROP TABLE ##GiveAway
Now call the stored procedure with
dbo.uspMoneyBack 212


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 10/18/2006 10:23:03
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  10:02:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Even uspMoneyBack 33 works!


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 10/18/2006 10:48:06
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 10/18/2006 :  13:34:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Now this is better algorithm! Now it also handles not only theoretically how to give money back,
now the algorithm also checks for how many notes left there are for every bill type.
CREATE PROCEDURE dbo.uspMoneyBack
(
	@Amount INT
)
AS

SET NOCOUNT ON

DECLARE	@BillTypes TABLE
	(
		BillItem INT,
		NotesLeft INT,
		FaceValue INT
	)

INSERT	@BillTypes
	(
		NotesLeft,
		FaceValue
	)
SELECT	1, 1000 UNION ALL
SELECT	3, 500  UNION ALL
--SELECT	4, 100  UNION ALL
SELECT	3, 50   UNION ALL
SELECT	11, 20  UNION ALL
SELECT	15, 10  UNION ALL
SELECT	2, 9    UNION ALL
SELECT	3, 7    UNION ALL
SELECT	20, 5   UNION ALL
SELECT	30, 2   UNION ALL
SELECT	50, 1

UPDATE	bt
SET	bt.BillItem = (SELECT COUNT(DISTINCT x.FaceValue) FROM @BillTypes x WHERE x.FaceValue < bt.FaceValue)
FROM	@BillTypes bt

DECLARE	@BillItem INT,
	@MaxBillItem INT,
	@FaceValue INT,
	@NextFaceValue INT,
	@NotesLeft INT,
	@Bills INT,
	@Select1 VARCHAR(8000),
	@Select2 VARCHAR(8000),
	@From1 VARCHAR(8000),
	@From2 VARCHAR(8000),
	@SQL VARCHAR(8000),
	@Where VARCHAR(8000),
	@OrderBy1 VARCHAR(8000),
	@OrderBy2 VARCHAR(8000),
	@Output VARCHAR(8000)

SELECT	@BillItem = MIN(BillItem),
	@MaxBillItem = MAX(BillItem)
FROM	@BillTypes

WHILE @BillItem <= @MaxBillItem
	BEGIN
		SELECT	@FaceValue = MIN(FaceValue),
			@NotesLeft = MIN(NotesLeft)
		FROM	@BillTypes
		WHERE	BillItem = @BillItem

		SELECT	@NextFaceValue = MIN(FaceValue)
		FROM	@BillTypes
		WHERE	BillItem = @BillItem + 1

		SELECT	@Select1 =	CASE
						WHEN @Select1 IS NULL THEN 'SELECT n' + CONVERT(varchar, @BillItem)
						ELSE @Select1 + ' + n' + CONVERT(varchar, @BillItem)
					END,
			@Select2 =	CASE
						WHEN @Select2 IS NULL THEN ', n' + CONVERT(varchar, @BillItem) + ' ''' + CONVERT(varchar, @FaceValue) + ''''
						ELSE @Select2 + ', n' + CONVERT(varchar, @BillItem) + ' ''' + CONVERT(varchar, @FaceValue) + ''''
					END,
			@From1 =	CASE
						WHEN @From1 IS NULL THEN 'SELECT b' + CONVERT(varchar, @BillItem) + '.Number n' + CONVERT(varchar, @BillItem)
						ELSE @From1 + ', b' + CONVERT(varchar, @BillItem) + '.Number n' + CONVERT(varchar, @BillItem)
					END,
			@Bills =	CASE
						WHEN @NextFaceValue >= @Amount OR @NextFaceValue IS NULL THEN @Amount / @FaceValue
						WHEN @FaceValue > @Amount THEN 0
						ELSE dbo.fnLCM(@FaceValue, @NextFaceValue) / @FaceValue - 1
					END,
			@NotesLeft =	CASE
						WHEN @NotesLeft <= @Bills THEN @NotesLeft
						ELSE @Bills
					END,
			@From2 =	CASE
						WHEN @From2 IS NULL THEN ' FROM '
						ELSE @From2 + ' CROSS JOIN '
					END + '(SELECT DISTINCT Number FROM master.dbo.spt_values WHERE Number BETWEEN 0 AND ' + CONVERT(varchar, @NotesLeft) + ') b' + CONVERT(varchar, @BillItem),
			@Where =	CASE
						WHEN @Where IS NULL THEN ' WHERE ' + CONVERT(varchar, @FaceValue) + ' * n' + CONVERT(varchar, @BillItem)
						ELSE @Where + ' + '+ CONVERT(varchar, @FaceValue) + ' * n' + CONVERT(varchar, @BillItem)
					END,
			@OrderBy1 =	CASE
						WHEN @OrderBy1 IS NULL THEN ' ORDER BY n' + CONVERT(varchar, @BillItem)
						ELSE @OrderBy1 + ' + n' + CONVERT(varchar, @BillItem)
					END,
			@OrderBy2 =	CASE
						WHEN @OrderBy2 IS NULL THEN ', SIGN(n' + CONVERT(varchar, @BillItem) + ')'
						ELSE @OrderBy2 + ' + SIGN(n' + CONVERT(varchar, @BillItem) + ')'
					END,
			@BillItem = @BillItem + 1
	END

SELECT	@Select1 = @Select1 + ' Bills',
	@Select2 = @Select2 + ' INTO ##GiveAway FROM (',
	@From2 = @From2 + ') x',
	@Where = @Where + ' = ' + CONVERT(varchar, @Amount),
	@SQL = @Select1 + @Select2 + @From1 + @From2 + @Where + @OrderBy1 + @OrderBy2

EXEC	(@SQL)

DELETE	g
FROM	##GiveAway g
WHERE	g.Bills <> (SELECT MIN(x.Bills) FROM ##GiveAway x)

SELECT	*
FROM	##GiveAway

DROP TABLE ##GiveAway


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts
Posted - 10/19/2006 :  01:23:31  Show Profile  Reply with Quote
hi
thanxs a lot peter, this works fine,iam implementing this in one or two days i will get back to u
thanxs once again
thanxs bye


coorgi
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | 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.2 seconds. Powered By: Snitz Forums 2000