A more elegant MOD calculation anyone? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

A more elegant MOD calculation anyone?

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-08-05 : 04:37:59

Hi,

I was wondering if the "Volume_display"-column of the following query could be calculated in a more elegant way, preferably without the case. The objective is to display volumes in chunks (Volume_display_limit). Volume_total will decrease randomly until it reaches 0 but the "Volume_display" must always be between 0 and Volume_display_limit but never more than Volume_display_limit. The point of the 999999999 is to always display the full Volume_total, but if this can be done in another way then thats fine. The code provided displays the correct result:

DECLARE @table table (
  Volume_total decimal (18, 8),
  Volume_display_limit decimal (18, 8)
)

INSERT INTO @table
SELECT 15000, 1000 UNION ALL
SELECT 1026, 500 UNION ALL
SELECT 0, 500 UNION ALL
SELECT 12050, 999999999

SELECT
  Volume_total,
  Volume_display_limit,
  Volume_display = CASE 
    WHEN Volume_total = 0 THEN 0
    WHEN Volume_total % Volume_display_limit = 0 THEN Volume_display_limit
    ELSE (Volume_total % Volume_display_limit) 
    END
FROM @table

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 05:00:11

Seems a good way to go.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources