Dynamically Truncate Int 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

SQL Server Development (2000)

Dynamically Truncate Int Column - Help!

Author

Topic

Vedder
Starting Member

8 Posts

Posted - 2004-08-27 : 20:01:39

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!!!

Thanks in advance.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-27 : 20:10:41

If you post some CREATE TABLE and INSERT INTO statements we can help you out much easier and quicker. It sounds like you need a derived table though.


SELECT MAX(whatever)
FROM (
SELECT Field1, Field2 FROM MyBigTable
WHERE RealColumnName <= @UserUpperLimit
) A

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Vedder
Starting Member

8 Posts

Posted - 2004-08-27 : 20:29:18

Okay, sorry, I should provided a bit more detail on the problem.

The column holds Weight values , and I want to truncate weight over a certain limit.

INSERT @WPPK
SELECT MyDate,
Sum(TWeight*(AnotherColumn))/Sum(Weight) As PPK
WHERE MyDate BETWEEN @Date1 AND @Date2 ......

where 'TWeight' would be the truncated equivalent of 'Weight'

(cheers for the speedy reply btw)

Vedder
Starting Member

8 Posts

Posted - 2004-08-27 : 20:34:47

More detail:

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 07:50:28

quote:
Originally posted by Vedder

More detail:

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

Ok, you would most certainly need to join with the table with the "upper limit".
Something along the lines of:


SELECT
	t.Mydate,
	t.UserID,
	SUM( (CASE WHEN t.Weight > lt.UpperLimit THEN lt.UpperLimit ELSE t.Weight END) * (@BP-t.Penalty) ) / SUM(t.Weight) AS PPK,
	SUM( t.Weight ) As TotalWeight,
	COUNT(*) AS MyCount
FROM
	MyBigTable t
	JOIN LimitTable lt ON t.UserId = lt.UserID
WHERE
	( t.Mydate BETWEEN @fromdate AND @todate )
	AND t.UserID NOT IN ( SELECT UserID FROM @UserIDTemp )
GROUP BY
	t.MyDate,
	t.UserID

/rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources