Get each denominations :D - 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)

Get each denominations :D

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-07-29 : 05:42:10

Gud day once again!

I have this sample data...

select '5525.35'
union
select '9455.95'
union
select '11250.00'

Now, what i want is to get the sum of number/s of following denominations...


     --1000
     --500
     --100
     --50
     --20
     --10
     --5
     --1
     --.25
     --.05

Heres mine.



SELECT   [1000] = floor(@MyMoney / 1000)
	,[500] =  floor(
		     (@MyMoney 
		     - floor(@MyMoney / 1000) * 1000)  
		      / 500)
	,[100] =  floor(
		  (
		  @MyMoney -
		  (
		  (floor(@MyMoney / 1000) * 1000) 
		+ (floor(
		     (@MyMoney 
		     - floor(@MyMoney / 1000) * 1000)  
		      / 500) * 500)
		  )
		  ) / 100 )
	,[50] = floor(
		(
		@MyMoney -
		(
		  (floor(@MyMoney / 1000) * 1000)
		+ (floor(
		     (@MyMoney 
		     - floor(@MyMoney / 1000) * 1000)  
		      / 500) * 500)
		+ (floor(
		  (
		  @MyMoney -
		  (
		  (floor(@MyMoney / 1000) * 1000) 
		+ (floor(
		     (@MyMoney 
		     - floor(@MyMoney / 1000) * 1000)  
		      / 500) * 500)
		  )
		  ) / 100 ) * 100)
		)
		) / 50)
-- up to .05 denomination

Actually i want to have the shortest statement.

Results should be...


Money        1000    500    100   50   20   10   5   1   .25   .05
------------------------------------------------------------------
5525.35      5       1      0     0    1    0    1   0   1     2
9455.95      9       0      4     1    0    0    1   0   3     3
11250.00     11      0      2     1    0    0    0   0   0     0

How is your own approach?

Want Philippines to become 1st World COuntry? Go for World War 3...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-29 : 05:58:13

Probably best to build yourself a function that accepts the value and the denomination, then you can do something like:
SELECT MoneyValue, GetDenomination(MoneyValue, 1000) as [1000], GetDenomination(MoneyValue, 500) as [500], etc etc
Have a look on BOL on how to write user defined functions (UDF's)
Tim

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-29 : 06:34:54

>> How is your own approach?

I use VISA

DECLARE @m MONEY, @r INT
DECLARE @monetarytable TABLE
(
	AMOUNT MONEY
	,[1000] INT
	,[500] INT
	,[100] INT
	,[50] INT
	,[20] INT
	,[10] INT
	,[5] INT
	,[1] INT
	,[.25] INT
	,[.05] INT
	,LEFTOVER MONEY
)

INSERT @monetarytable(AMOUNT)
SELECT 5525.35
UNION
SELECT 9455.99
UNION
SELECT 11250.00

UPDATE @monetarytable SET
	@m = AMOUNT
	,@r = [1000] = FLOOR(@m/1000.0)
	,@m = @m-1000*@r
	,@r = [500] = @m/500.0
	,@m = @m-500*@r
	,@r = [100] = @m/100.0
	,@m = @m-100*@r
	,@r = [50] = @m/50.0
	,@m = @m-50*@r
	,@r = [20] = @m/20.0
	,@m = @m-20*@r
	,@r = [10] = @m/10.0
	,@m = @m-10*@r
	,@r = [5] = @m/5.0
	,@m = @m-5*@r
	,@r = [1] = @m/1.0
	,@m = @m-1*@r
	,@r = [.25] = @m/.25
	,@m = @m-.25*@r
	,@r = [.05] = @m/.05
	,LEFTOVER = @m-.05*@r


SELECT * FROM @monetarytable

Edit: added the LEFTOVER column

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-29 : 06:40:44

quote:
Originally posted by timmy

Probably best to build yourself a function that accepts the value and the denomination, then you can do something like:
SELECT MoneyValue, GetDenomination(MoneyValue, 1000) as [1000], GetDenomination(MoneyValue, 500) as [500], etc etc
Have a look on BOL on how to write user defined functions (UDF's)
Tim

I bet You could do it recursive as well.
But then You can only have 32 denominations.

Good approach, The fn would hold the logic.
This is where I would want to be ale to raise an error from the function, if the user does: GetDenomination(MoneyValue,632).

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources