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)

Items And Boxes

The problem: find all the boxes that an item
can fit into. The only rule is that the item's dimensions (length,
width, height) must be smaller (or equal) to the boxes dimensions.

The solution:

If you absolutely cannot enforce length <= width <= height in the
database, use OLAP functions.

select boxId, itemId from(
select boxId, dim, row_number() over(partition by boxId order by dim
desc) rn from(
select boxId, length dim
union all
select boxId, width dim
union all
select boxId, height dim) t
) box_dim
join
select itemId, dim, row_number() over(partition by itemId order by dim
desc) rn from(
select itemId, length dim
union all
select itemId, width dim
union all
select itemId, height dim) t
) item_dim
on box_dim.rn = item_dim.rn and box_dim.dim >= item_dim.dim
group by boxId, itemId
having count(*)=3


on SQL 2000 you can use subqueries instead of row_number().

Loading Data From Staging Table

The problem:

"... I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column.... "

The solution:

SET NOCOUNT ON
go


CREATE TABLE Target(TargetID INT PRIMARY KEY, someData CHAR(1))
INSERT Target VALUES(1, 'A')
INSERT Target VALUES(2, 'B')
go
create table staging(someData CHAR(1))
INSERT staging VALUES('C')
INSERT staging VALUES('D')
go
SELECT IDENTITY(INT, 1,1) AS TargetID, someData
INTO #t FROM staging


INSERT Target
SELECT TargetID + (SELECT COALESCE(MAX(TargetID), 0) FROM Target),
someData
FROM #t


go
SELECT * FROM Target
go


TargetID someData
----------- --------
1 A
2 B
3 C
4 D


DROP TABLE Target
DROP TABLE Staging
DROP TABLE #t

How To Compare Data in Two Talbes Real Quick

create table #a1(i int primary key, c char(1))
insert into #a1 values(1, 'A')
insert into #a1 values(2, 'B')
insert into #a1 values(3, 'C')
insert into #a1 values(5, null)
go
create table #a2(i int primary key, c char(1))
insert into #a2 values(1, 'A')
insert into #a2 values(2, 'U')
insert into #a2 values(4, 'V')
insert into #a2 values(5, null)
go
--- rows in only one table
select #a1.i, #a2.i
from #a1 full outer join #a2 on #a1.i = #a2.i
where #a1.i is null or #a2.i is null


/*


i i
----------- -----------
3 NULL
NULL 4


(2 row(s) affected)
*/


-- different rows
-- instead of comparing all the nullable columns
-- as follows
-- #a1.c = #a2.c
-- or #a1.c is null and #a2.c is not null
-- or #a2.c is null and #a1.c is not null
-- just let UNION do it for you
select i from(
select #a1.* from #a1 join #a2 on #a1.i = #a2.i
union
select #a2.* from #a2 join #a1 on #a1.i = #a2.i
) t
group by i
having count(*)>1


i
-----------
2

Service Dates

Here's the problem:


We have a table of services for our clients (about 2 million rows).
The rows are simply the client's ID and the date of service. Rows may
dupe as clients may have more than one service per date.


We need to be able to define a case start and end date for each client
ID. The end date is defined as the last service date with no further
activity within some number (usually 180) of days. Furthermore, I'd
need to enumerate the cases per client.


So using the example below, I'd be looking for results such as:


Client Start End Case
55577 2/01/2004 5/11/2004 1
55577 1/09/2005 1/09/2005 2
55577 3/04/2006 OPEN 3
72395 4/04/2006 OPEN 1


In these cases, the OPEN dates indicate that there has not been a 180
day period of inactivity since the most recent date.

Solution #1 (me):

SELECT DISTINCT ClientID, ServDate AS Start
, (
SELECT MIN(s1.ServDate)
FROM #ServList s1
WHERE s1.ClientID = s.ClientID
AND s1.ServDate >= s.ServDate
---------- instead of getdate(), use last midnight
AND s1.ServDate < DATEADD(DAY, -179, GETDATE())
AND NOT EXISTS(SELECT 1 FROM #ServList s2 WHERE s2.ClientID =
s1.ClientID AND s2.ServDate > s1.ServDate AND s2.ServDate <=
DATEADD(DAY, 179, s1.ServDate))
)
FROM #ServList s
WHERE NOT EXISTS(SELECT 1 FROM #ServList s1 WHERE s1.ClientID =
s.ClientID AND s1.ServDate >= DATEADD(DAY, -179, s.ServDate) AND
s1.ServDate < s.ServDate)

Solution #2

Bus Stops

The Problem

I have a table that holds records for a stops along a bus route. Each
records has a number of people getting on, number of people getting
off, a spot check, and a current load column.
The spot check column is the to verify that the sensors on the bus are
working correctly; the most reliable number to be use in the is
SPOT_CHECK.


Table Structure as follows:


ID (identity column)
ROUTE (Description of the bus route)
ONS (# of people getting on)
OFFS (# of people getting off)
SPOT_CHECK (visual spot count of people on the bus)
LOAD (Calculated load on the bus)

Solution 1 (me):

CREATE TABLE BusLoad(
ROUTE CHAR(4) NOT NULL,
StopNumber INT NOT NULL,
ONS INT,
OFFS INT,
SPOT_CHECK INT)
go


INSERT BusLoad VALUES('AAAA', 1, 5, 0, null)
INSERT BusLoad VALUES('AAAA', 2, 0, 0, null)
INSERT BusLoad VALUES('AAAA', 3, 2, 1, null)
INSERT BusLoad VALUES('AAAA', 4, 6, 3, 8)
INSERT BusLoad VALUES('AAAA', 5, 1, 0, null)
INSERT BusLoad VALUES('AAAA', 6, 0, 1, 7)
INSERT BusLoad VALUES('AAAA', 7, 0, 3, null)
go
SELECT b.ROUTE, b.StopNumber, b.ONS, b.OFFS, b.SPOT_CHECK,
SUM(COALESCE(b1.SPOT_CHECK, 0) +
CASE WHEN b1.SPOT_CHECK IS NULL THEN b1.ONS ELSE 0 END - CASE WHEN
b1.SPOT_CHECK IS NULL THEN b1.OFFS ELSE 0 END)
FROM BusLoad b JOIN BusLoad b1 ON b1.StopNumber <= b.StopNumber AND
b1.ROUTE = b.ROUTE
WHERE NOT EXISTS(SELECT 1 FROM BusLoad b2 WHERE b2.ROUTE = b.ROUTE
AND b2.StopNumber <= b.StopNumber AND b1.StopNumber < b2.StopNumber
AND b2.SPOT_CHECK IS NOT NULL)
GROUP BY b.ROUTE, b.StopNumber, b.ONS, b.OFFS, b.SPOT_CHECK
go
DROP TABLE BusLoad


Solution 2 (Jim Underwood):

select a.StopNumber
, a.ROUTE
, a.ONS
, a.OFFS
, a.SPOT_CHECK
, sum(case when c.spot_check is null
then c.ons-c.offs else c.spot_check end)
from BusLoad as a
left outer join BusLoad as b
on b.route = a.route
and b.StopNumber =
(
select max(b1.StopNumber)
from BusLoad b1
where b1.route = b.route
and b1.StopNumber <= a.StopNumber
and b1.spot_check is not null
)
inner join BusLoad as c
on c.route = a.route
and c.StopNumber between (case when b.StopNumber is null
then 0 else b.StopNumber end) and a.StopNumber
group by a.StopNumber
, a.ROUTE
, a.ONS
, a.OFFS
, a.SPOT_CHECK;