Rounding Up Prices

The problem was posted by Steve Ledbetter:

I am trying to do some slightly complicated rounding in transact SQL in a
stored proc. I need to round UP, and have implemented this for straight
rounding up, for rounding up to nearest nickel/dime/quarter/half dollar, and
rounding up to next .49/.99 using addition and CEILING and formulas..


My difficulty is that in addition to the above options I need the option to
allow the user to select two values to round to. Example, user selects
'.03' as 'low value' and '.07' as 'high value', I need to round up to the
next .x3 or .x7, so that 1.22 would become 1.23, 1.25 would become 1.27,
1.29 would become 1.33, etc. The same idea would apply to dollars
separately, so picking .03 and .07 for cents, and 5 and 8 for dollars: 3.21
would round to 5.23 ($3 would round up to $5 .21 would round up to .23 -
the neareast .03), 6.35 would round to 8.37, 9.99 would round to 15.03,
etc.

It's for a retail products management module where clients can generate
their selling prices from costs (product cost, freight, installation cost,
etc) using formulas and rounding options (like rounding up to nearest .99.
.49 , nickel, dime, etc. or to a user defined pair of numbers (which was
what spurred my questions). I'll be updating 10's of thousands of records
at a time and needed to do it in a stored proc for speed.

My solution:

You can round up dollars as follows:

DECLARE @numberToRoundUp INT
SET @numberToRoundUp = 9
------- Assuming Numbers table stores numbers from 1 to 1000
SELECT TOP 1 Number FROM Numbers
WHERE Number % 10 IN(5,8) AND Number > @numberToRoundUp
ORDER BY Number


You can round up cents in the same way.

Choosing Only One Feature

Problem posted by Wade Fleming:

I have the following tables:


Person with fields (RecordNum,Name)
Ethnicity with fields (RecordNum,Name)
EthnicityLink with fields (PersonRecNum,EthnicityRecnum)


The idea is that one person can have many ethnicities.
So I could have:
Person: (0001,'Joe Bloggs'),(0002,'John Smith')
Ethnicity: (0001,'Asian'),(0002,'African'),(0003,'European')
EthnicityLink: (0001,0002),(0002,0001),(0002,0003)


meaning that Joe Bloggs is African, and John Smith is European/Asian.


Now I have some rules about what actual ethnicity will be designated to
a given person, one of which is:
- If any of the persons ethnicities are 'European', they are classed as
'European'. (ie: above John Smith would be classed as European)

2005 solution:

CREATE TABLE Person (RecordNum INT,Name VARCHAR(30))
go
CREATE TABLE Ethnicity (RecordNum INT,Name VARCHAR(30))
go
CREATE TABLE EthnicityLink (PersonRecNum INT, EthnicityRecnum INT)
go


INSERT Person VALUES(1,'Joe Bloggs')
INSERT Person VALUES(2,'John Smith')
INSERT Person VALUES(3,'John Doe')
INSERT Ethnicity VALUES(1,'Asian')
INSERT Ethnicity VALUES(2,'African')
INSERT Ethnicity VALUES(3,'European')
INSERT EthnicityLink VALUES(0001,0002)
INSERT EthnicityLink VALUES(0002,0001)
INSERT EthnicityLink VALUES(0002,0003)
INSERT EthnicityLink VALUES(3,0001)
INSERT EthnicityLink VALUES(3,2)
go
SELECT * FROM(
SELECT Person.Name, Ethnicity.Name as Etn_Name,
DENSE_RANK() OVER(PARTITION BY Person.Name ORDER BY CASE Ethnicity.Name
WHEN 'European' THEN 1 ELSE 2 END) rn
FROM Person
INNER JOIN EthnicityLink ON EthnicityLink.PersonRecNum =
Person.RecordNum
INNER JOIN Ethnicity ON Ethnicity.RecordNum =
EthnicityLink.EthnicityRecNum) t
WHERE rn = 1


2000 solution is more involved:

SELECT Person.Name, Ethnicity.Name as Etn_Name
FROM Person
INNER JOIN EthnicityLink ON EthnicityLink.PersonRecNum =
Person.RecordNum
INNER JOIN Ethnicity ON Ethnicity.RecordNum =
EthnicityLink.EthnicityRecNum
WHERE NOT EXISTS(SELECT 1 FROM EthnicityLink el WHERE el.PersonRecNum =
Person.RecordNum
AND el.EthnicityRecNum <> EthnicityLink.EthnicityRecNum AND
el.EthnicityRecNum = 3)

Divided By The Biggest Gap

Problem posted by Jim Geissman

I have some data -- counts ID'd by location and grid East like this --
Loc East N
CA 100 3
CA 103 5
CA 109 2
CA 110 3


I'm interested in the total of N on either side of the largest gap in
Eastings.
In this case the largest gap is 6 (between 103 and 109), and the sum of
N for the 2 rows below the gap is 8, and for the 2 above the gap it's
5.


The problem is to locate the largest gap, and compute the sum of N for
the cases on either side. There are multiple locations, multiple
Eastings
per location, but only one largest gap. (If there are two largest
gaps, it
does't matter which one is used for the sums.)

The solution illustrates the power of OLAP functions:

CREATE TABLE a(Loc CHAR(2), East INT, N INT)
go
INSERT a VALUES('CA', 100, 3)
INSERT a VALUES('CA', 103, 5)
INSERT a VALUES('CA', 109, 2)
INSERT a VALUES('CA', 110, 3)
INSERT a VALUES('OR', 100, 3)
INSERT a VALUES('OR', 108, 5)
INSERT a VALUES('OR', 109, 2)
INSERT a VALUES('OR', 110, 3)
INSERT a VALUES('WA', 108, 5)
INSERT a VALUES('WA', 109, 2)
INSERT a VALUES('WA', 110, 3)
INSERT a VALUES('WA', 115, 3)


SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East > a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1


SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East > a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1


Loc East BeforeGap AfterGap GapSize rn
---- ----------- ----------- ----------- -----------
--------------------
CA 103 8 5 6 1
OR 100 3 10 8 1
WA 110 10 3 5 1


(3 row(s) affected)