Putting Envelopes in boxes - 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 2000 Forums

SQL Server Development (2000)

Putting Envelopes in boxes

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2004-09-01 : 04:41:26

I need to work out what gets shipped as part of a distribution of assets to hundreds of destinations. Each destination usually gets specific parcels, but on an ad hoc basis these parcels maybe bundled together for ease of shipping.

This means I need to workout when to send the usual parcel and when to send a parcel that contains other parcels!

I have put together a very simple example below, with the desired output at the bottom.

I am banging my head with this one and think that some kind of intermedate table is required but just cant think of the next move, any help would be much appreciated.


DECLARE @PARCELS TABLE
(
	uidparcel INTEGER PRIMARY KEY,
	toplevel INTEGER,
	parcel VARCHAR(100)
)

INSERT INTO @PARCELS
SELECT 1,1, 'Big Box'
UNION
SELECT 2,1,'Envelope A'
UNION
SELECT 3,1,'Envelope B'
UNION
SELECT 4,4,'Envelope C'

--big box is a special case created just for this run, and it will contain
--two envelopes

DECLARE @DESTINATIONS TABLE(
uiddestination INTEGER PRIMARY KEY,
destination VARCHAR(10)
)

INSERT INTO @DESTINATIONS
SELECT 1,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,'C'
UNION 
SELECT 4,'D'

DECLARE @RECEIVES TABLE(
	uiddestination INTEGER,
	uidparcel INTEGER
)

INSERT INTO @RECEIVES
SELECT 1,2
UNION
SELECT 2,3
UNION 
SELECT 3,2
UNION 
SELECT 3,3
UNION
SELECT 4,2
UNION
SELECT 4,3
UNION
SELECT 4,4

SELECT 
	D.destination,
	P.parcel
FROM
	@DESTINATIONS D
INNER JOIN
	@RECEIVES R
ON
	D.uiddestination = R.uiddestination
INNER JOIN
	@PARCELS P
ON
	P.uidparcel = R.uidparcel

--this shows that destination C would normally get two envelopes sent
--for this run I want destination C to get the big box
--destinaction D needs the big box and envelope C

--desired output
/*
A	Envelope A
B	Envelope B
C	Big Box
D 	Big Box
D	Envelope C
*/

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-01 : 06:21:51

looks a bit crude... but see if it works..

create view xyz as
SELECT uiddestination, count(*) cnt
FROM RECEIVES
group by uiddestination having count(*) > 1

SELECT distinct D.destination, Isnull(P3.parcel, P.parcel)
FROM DESTINATIONS D
INNER JOIN RECEIVES R
ON D.uiddestination = R.uiddestination
INNER JOIN PARCELS P
ON P.uidparcel = R.uidparcel
left outer join xyz X
on X.uiddestination = D.uiddestination
left outer join RECEIVES R2
ON X.uiddestination = R2.uiddestination
left outer join PARCELS P2
ON P2.uidparcel = R2.uidparcel
left outer join PARCELS P3
ON P2.toplevel = p3.uidparcel

Hemanth Gorijala
BI Architect / DBA

uberman
Posting Yak Master

159 Posts

Posted - 2004-09-01 : 07:03:21

I can see where you are going but it is not quite right

I put your suggestions into the demo script and it worked!

BUT

I then set it up so that Destination A gets Envelope A and Envelope C and the query now says destination A gets BIG BOX and Envelope C, which is not quite right.

I can see why it is going wrong (the @view sees that destination A now has multiple destinations and the outer joins pick up big box as envelope a is in the big box), but I cant work out how to exclude it from the results, it sort of needs a conditional join that is giving me nightmares


DECLARE @PARCELS TABLE
(
	uidparcel INTEGER PRIMARY KEY,
	toplevel INTEGER,
	parcel VARCHAR(100)
)

INSERT INTO @PARCELS
SELECT 1,1, 'Big Box'
UNION
SELECT 2,1,'Envelope A'
UNION
SELECT 3,1,'Envelope B'
UNION
SELECT 4,4,'Envelope C'

--big box is a special case created just for this run, and it will contain
--two envelopes

DECLARE @DESTINATIONS TABLE(
uiddestination INTEGER PRIMARY KEY,
destination VARCHAR(10)
)

INSERT INTO @DESTINATIONS
SELECT 1,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,'C'
UNION 
SELECT 4,'D'

DECLARE @RECEIVES TABLE(
	uiddestination INTEGER,
	uidparcel INTEGER
)

INSERT INTO @RECEIVES
SELECT 1,2
UNION
SELECT 1,4 -- destination A now gets envelope c
UNION
SELECT 2,3
UNION 
SELECT 3,2
UNION 
SELECT 3,3
UNION
SELECT 4,2
UNION
SELECT 4,3
UNION
SELECT 4,4

SELECT 
	D.destination,
	P.parcel
FROM
	@DESTINATIONS D
INNER JOIN
	@RECEIVES R
ON
	D.uiddestination = R.uiddestination
INNER JOIN
	@PARCELS P
ON
	P.uidparcel = R.uidparcel

--this is the raw data without taking into account that some
--items can be shipped in the BIG BOX

DECLARE @VIEW TABLE
(
	uiddestination INTEGER,
	cnt	INTEGER
)

INSERT INTO @VIEW
SELECT uiddestination, count(*)
FROM @RECEIVES
group by uiddestination having count(*) > 1



SELECT 
	 DISTINCT
	D.destination, 
	Isnull(P3.parcel, P.parcel) AS PARCEL
FROM 
	@DESTINATIONS D
INNER JOIN 
	@RECEIVES R
ON 
	D.uiddestination = R.uiddestination
INNER JOIN 
	@PARCELS P
ON 
	P.uidparcel = R.uidparcel
left outer join 
	@VIEW V
on 
	V.uiddestination = D.uiddestination 
left outer join 
	@RECEIVES R2
ON 
	V.uiddestination = R2.uiddestination
left outer join 
	@PARCELS P2
ON 
	P2.uidparcel = R2.uidparcel
left outer join 
	@PARCELS P3
ON 
	P2.toplevel = p3.uidparcel

--this works for destination C and D, but destination A is now showing a big box
--when in fact it should just get the two envelopes

--desired output
/*
A	Envelope A
A	Envelope C
B	Envelope B
C	Big Box
D 	Big Box
D	Envelope C
*/

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-01 : 07:44:43

OK.
While inserting into @View add "where uidparcel <> 4"
This will exclude anything with "Envelope C" from going into "Big Box"

Hemanth Gorijala
BI Architect / DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources