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;