give or take decimal points - 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 2005 Forums

Transact-SQL (2005)

give or take decimal points

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-05 : 06:16:49

Hi,
There is a table which holds prices. The data type is decimal(12, 4)
There are many prices. The sample prices in this field is something like:

100.1875
100.1870
98.390
98.395
99.000
100.1880
...
...

In a select query, I would like to treat numbers close to each other the same.
For example:
In the above sample prices i.e. 100.1875, 100.1880, 100.1870
I would like to show only ONE of them i.e min value is 100.1870
For the other prices;

98.390
98.395

I would like the select query to show one of them (It is not important which one. i.e min value is 98.390

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 06:19:24

Do you mean you want to consider value only upto two decimal places?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 06:21:40

How close should your threshold be?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 06:24:04

[code]DECLARE @Sample TABLE (Data MONEY)

INSERT @Sample
SELECT 100.1875 UNION ALL
SELECT 100.1870 UNION ALL
SELECT 98.390 UNION ALL
SELECT 98.395 UNION ALL
SELECT 99.000 UNION ALL
SELECT 100.1880

SELECT MIN(Data) AS LowestInGroup
FROM @Sample
GROUP BY ROUND(Data, 1, 0)
ORDER BY MIN(Data)

SELECT MIN(Data) AS LowestInGroup
FROM @Sample
GROUP BY ROUND(Data, 2, 1)
ORDER BY MIN(Data)[/code]

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

kutumbarao
Starting Member

13 Posts

Posted - 2007-12-05 : 06:31:42

Hi arkiboys,

Try with this below query

DECLARE @prices TABLE(price NUMERIC(18,4))
INSERT INTO @prices
SELECT 100.1875 UNION ALL
SELECT 100.1870 UNION ALL
SELECT 98.390 UNION ALL
SELECT 98.395 UNION ALL
SELECT 99.000 UNION ALL
SELECT 100.1880

SELECT MIN(price) FROM @prices
GROUP BY ROUND(price,0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 06:48:58

Nice work!

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-05 : 07:01:53

That will not quite work for me because let's say the prices are:
114.18700000
114.37000000
114.18700000
114.18800000
114.18750000
114.18700000

Then;
I woould like to see the following figures (Notice, 114.37000000 is missing)
114.18700000
114.18700000
114.18800000
114.18750000
114.18700000

so we are taking 114.18750000 as the starting point and then go half either way. Hope you know what I mean

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 07:08:26

No, we don't.

1) Why is 114.37000000 removed?
2) Why is 114.18700000 (a dupe) retained?

Please explain your business rules in this case as clearly as you possible can!

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-05 : 07:28:57

Ok, I know exactly what is required now;
This is what the select query should do.
Take the Min price i.e. MinPrice,
ADD 0.01 to it i.e. minPrice + 0.01 = higherPrice
show all prices within this range i.e prices that fall within higher price and MinPrice

Lets say we have these prices:
114.18700000
114.18700000
114.18800000
114.18750000
114.18700000
114.18700000

The Min is: 114.18700000
then 114.18700000 + 0.01 = 114.197
Now, from the above prices I would like to get all prices that fall between 114.197 and 114.18700000
Therefore; we should get:
114.18700000
114.18700000
114.18800000
114.18750000
114.18700000
114.18700000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 07:35:41

You want prices that are less then minimum price in table?
Can't do that.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 07:37:08

[code]DECLARE @Sample TABLE (Price SMALLMONEY)

INSERT @Sample
SELECT 114.1870 UNION ALL
SELECT 114.3700 UNION ALL
SELECT 114.1870 UNION ALL
SELECT 114.1880 UNION ALL
SELECT 114.1875 UNION ALL
SELECT 114.1870

SELECT s.Price
FROM @Sample AS s
CROSS JOIN (
SELECT MIN(Price) AS p1,
MIN(Price) + 0.1 AS p2
FROM @Sample
) AS c
WHERE s.Price BETWEEN c.p1 AND c.p2[/code]

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-05 : 07:45:56

Please note that I changed my previous post to be more precise. Apologiese for inconvenience

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 07:47:09

Yes, I figured that out and supplied a solution for the new information.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 07:47:57

But I don't see the connection between this last piece of information and first post...

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-05 : 07:57:07

It seems we are getting to the correct solution.
I have added more test data as below but is is not quite there yet.
Please see below...Notice the min and max with + and -

This first query seems to be correct:

DECLARE @Sample TABLE (Price SMALLMONEY)

INSERT @Sample
SELECT 114.18700000 UNION ALL
SELECT 114.37000000 UNION ALL
SELECT 114.18700000 UNION ALL
SELECT 114.18800000 UNION ALL
SELECT 114.18750000 UNION ALL
SELECT 114.18700000 UNION ALL
SELECT 114.18700000

SELECT s.Price
FROM @Sample AS s
CROSS JOIN (
SELECT MIN(Price) AS p1,
MIN(Price) + 0.01 AS p2
FROM @Sample
) AS c
WHERE s.Price BETWEEN c.p1 AND c.p2

returns:
114.187
114.187
114.188
114.1875
114.187
114.187
---------------------------------
This second query does not return anything:
DECLARE @Sample TABLE (Price SMALLMONEY)

INSERT @Sample

SELECT NULL UNION ALL
SELECT 113.87000000 UNION ALL
SELECT 114.12500000 UNION ALL
SELECT 114.06300000 UNION ALL
SELECT 114.06250000 UNION ALL
SELECT 114.06200000 UNION ALL
SELECT 114.06200000

SELECT s.Price
FROM @Sample AS s
CROSS JOIN (
SELECT max(Price) AS p1,
max(Price) - 0.01 AS p2
FROM @Sample
) AS c
WHERE s.Price BETWEEN c.p1 AND c.p2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 07:59:49

SELECT s.Price
FROM @Sample AS s
CROSS JOIN (
SELECT max(Price) - 0.01 AS p1,
max(Price) AS p2
FROM @Sample
) AS c
WHERE s.Price BETWEEN c.p1 AND c.p2

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-05 : 08:16:46

Will it be ok to have decimal(12, 4) instead of smallmoney?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 08:18:07

Of course.
But SMALLMONEY or MONEY are slightly faster since they internally are integers (only scaled down by 10000).

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts