Dynamically Truncate Real Column - Help!

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)

Dynamically Truncate Real Column - Help!

Author

Topic

Vedder
Starting Member

8 Posts

Posted - 2004-08-27 : 21:26:22

Hey, have been trying to work this problem out for a while, and now my head hurts! Hopefully there'll be a simple answer to this one.

Okay, here goes:

I've got a table which will be holding ~2million records, where one of the columns (of type real) holds a number between 0-150. I'm trying to perform various aggregate functions on this column. The problem i'm having is that while the number in this column can be anything between 0 and 150, I want to be able to select the number up to a variable upper limit (which is held in another table).

So, for example, I may have a user which has a predefined 'upper limit' of 80. So when performing calculations that involve the aforementioned column, I need the store procedure to select the number as is, unless it is over the 'upper limit' in which case the number should be taken as the value of the upper limit (in this case, 80).

Due to the nature of the calculation, I can't just do two select ('select .... where mycol < upperlimit' and 'select 80 where mycol >= upperlimit), and also, I can't use a function as this slows things terribly. So, is there anyway I can effectively truncate the number in the column based on a variable upper limit without modifying the original data?

Hope this makes sense!! Any help was be greatly appreciated as I've been beating myself up over this one for ages!!!

Here's a snippet of code from the SP:

INSERT @WPPK
SELECT Mydate,UserID,
Sum(TWeight*(@BP-Penalty))/Sum(Weight) As PPK,
Sum(Weight) As TotalWeight,
Count(*) As MyCount
FROM MyBigTable
WHERE (Mydate BETWEEN @fromdate AND @todate)
AND UserID NOT IN (SELECT UserID FROM @UserIDTemp)
GROUP BY MyDate,UserID

Where Weight is the column in question and TWeight is the truncate equivalent.

Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-28 : 06:11:49

instead of weight use
case when weight > TWeight then TWeight else Weight end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources