Only integers

Question

Post reply

Only integers

Amy.G

SSCrazy

Points: 2305
More actions
October 14, 2011 at 1:19 pm

#249418

I have a list of cummulative percents. Depending on the year, this list can be 500,000 to a millions of records, which makes charting a problem. I had the idea to shorten the list to those numbers where the cum percent was closest to the interger. This way, not only would the list be much smaller, but I could chart various years on the same graph since they would all have the same number of rows. But I'm stuck on how to do this. For example, here is a pertial list of the 99s:
98.934800
99.001200 <<--
99.044100
99.096800
99.128900
99.186500
99.253600
99.275600
99.319700
99.364500
99.429100
since 99.001200 is the closest to the integer 99, this is the one to keep. And then do this for every number 0-100. I'm not sure how to query for 99, let alone all 101 numbers at once.
Any one have an idea? Thank you, Amy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Amy.G SSCrazy Points: 2305 More actions · Answer 1

I actually just figured it out. If anyone is curious, here it is:

SELECT cast(var1 as int) as var1

from table

GROUP BY cast(var1 as int)

101 rows, just like I wanted. I'm open to better/faster methods tho.

Amy

Lowell SSC Guru Points: 323518 More actions · Answer 2

if you convert to say, decimal(5,0) in your calcualtions, would that work for you ?

/*

ThePCTFiveOhFiveTwo

98.9348009998.93

99.0012009999.00

99.0441009999.04

99.0968009999.10

99.1289009999.13

99.1865009999.19

99.2536009999.25

99.2756009999.28

99.3197009999.32

99.3645009999.36

99.4291009999.43

*/

With MySampleData

AS (

SELECT '98.934800' AS ThePCT UNION ALL

SELECT '99.001200' UNION ALL

SELECT '99.044100' UNION ALL

SELECT '99.096800' UNION ALL

SELECT '99.128900' UNION ALL

SELECT '99.186500' UNION ALL

SELECT '99.253600' UNION ALL

SELECT '99.275600' UNION ALL

SELECT '99.319700' UNION ALL

SELECT '99.364500' UNION ALL

SELECT '99.429100' )

SELECT

ThePCT,

convert(decimal(5,0),ThePCT) AS FiveOh,

convert(decimal(5,2),ThePCT) as FiveTwo

FROM MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Amy.G SSCrazy Points: 2305 More actions · Answer 3

Ah, the CAST vs. CONVERT debate finally gets personal. Well played, Lowell. 😀

Lowell SSC Guru Points: 323518 More actions · Answer 4

Amy.G (10/14/2011)
Ah, the CAST vs. CONVERT debate finally gets personal. Well played, Lowell. 😀

lol, so now it's time for my obligatory reply:

"CAST?!?! you used CAST?!! you're doing it wrong!" 😀

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!