Complicated data capture and processing...

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

General SQL Server Forums

New to SQL Server Programming

Complicated data capture and processing...

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2009-11-26 : 07:40:20

Hi,

The second part of my development problem is (I think) far more complex indeed. I'll try and describe it in words first, provide some tables and then hope someone out there in SQL land is able (willing :-)) to come up with some workable suggestions!!!

a. One customer could have more than one EXPORT event (not really relevant here)
b. One customer can, of course, have more than one animal
c. In relocating animals, any number of animals can share 1 container.

1. Here's some animals that are being relocated:

--------------------
CREATE TABLE dbo.pets(petID INT, customerID INT, animalName nvarchar (25))

INSERT INTO pets values (1 , 1, 'Howl')
INSERT INTO pets values (2 , 1, 'Meow')
INSERT INTO pets values (3 , 1, 'Woof')
INSERT INTO pets values (4 , 2, 'Bark')
INSERT INTO pets values (5 , 2, 'Poop')
INSERT INTO pets values (6 , 3, 'Ruff')
--------------------

AS you can see
~ customer1 has 3 animals travelling
~ customer2 has 2 animals travelling
~ customer3 has 1 animal travelling

2. And here are the EXPORT shipments that will relocate them

--------------------
CREATE TABLE dbo.exports(exportID int, customerID INT, destination nvarchar(25), shipmentWeight decimal (18,2))

INSERT INTO dbo.exports values (1, 1, 'London', 0.00)
INSERT INTO dbo.exports values (2, 2, 'Trinidad', 0.00)
INSERT INTO dbo.exports values (3, 3, 'Beijing', 0.00)
--------------------

3. NOW, here's where it gets tricky AND I DON'T KNOW WHAT TO DO or HOW TO DO IT...

I must:

A. Capture in a grid, which pets will travel in which containers
B. Record in the DB details of ALL pets in EACH container

This must also be flexible so that the user can change which pet travels in which container.

Here is the basic container table that reflects:

export 1 (customer 1) has two containers - container 1 has 2 animals, container 2 has 1 animal
export 2 (customer 2) has one container - with 2 animals
export 3 (customer 3) has one container - with 1 animal

--------------------
CREATE TABLE dbo.containers(containerID int, exportID int, containerWeight decimal (18,2))

INSERT INTO containers values (1 , 1, 42.00)
INSERT INTO containers values (2 , 1, 32.00)
INSERT INTO containers values (3 , 2, 40.00)
INSERT INTO containers values (4 , 3, 32.00)
--------------------

Really friends, I am stumped with how to (a) capture the options and (b) record/process the options.

Any suggestions or advice will - be assured - be hugely appreciated and given credit whereever I can!

With appreciation and thanks in advance from a quite warm Dubai!

Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 01:43:52

so is your problem in distributing animals to containers based on weight? in that case, which table currently has the weights of animals stored?

nick_dkc
Starting Member

25 Posts

Posted - 2009-11-27 : 23:36:53

Hi Visakh16 - obviously I didn't explain my problem clearly enough - apologies! :-) It has nothing to do with distributing pets by weight.

Which pet is sharing a container with which other pet(s) is set by the user in the primary application. By default, each container is assigned to one animal. However, although usually it does not exceed 2 pets sharing one container, it can be more (for example rabbits, tortoises, birds) so my solution needs to be able to deal with this.

I have come up with (perhaps) one idea of how to capture the sharing situation, that is assigning 1 pet to 1 container by default and then have a column in which the user can say if a pet is sharing that container (petShareID).

CREATE TABLE dbo.pets(petID INT, customerID INT, animalName nvarchar (25), description nvarchar(50))

INSERT INTO pets values (1 , 1, 'Howl', 'Dog, 'Howl, Male, 5Y 4M')
INSERT INTO pets values (2 , 1, 'Meow', 'Dog, 'Meow, Female, 5Y 4M')
INSERT INTO pets values (3 , 1, 'Woof', 'Dog, 'Woof, Male, 5Y 4M')
INSERT INTO pets values (4 , 1, 'Woof', 'Dog, 'Woof, Male, 5Y 4M')
INSERT INTO pets values (5 , 2, 'Bark', 'Dog, 'Bark, Female, 5Y 4M')
INSERT INTO pets values (6 , 2, 'Poop', 'Dog, 'Poop, Male, 5Y 4M')
INSERT INTO pets values (7 , 3, 'Ruff', 'Dog, 'Ruff, Female, 5Y 4M')

CREATE TABLE dbo.exports(exportID int, customerID INT, shipmentWeight decimal (18,2), expString nvarchar(500))

INSERT INTO dbo.exports values (1, 1, 0.00, NULL)
INSERT INTO dbo.exports values (2, 2, 0.00, NULL)
INSERT INTO dbo.exports values (3, 3, 0.00, NULL)

CREATE TABLE dbo.containers(exportID int, boxNumber, petID, petShareID, cnDimensions nvarchar(15), cnWeight decimal (18,2))

INSERT INTO containers values (1, 1, 1, 2, 100 x 55 x 90, 42.00)
INSERT INTO containers values (1, 1, 2, 1, 100 x 55 x 90, 42.00)
INSERT INTO containers values (1, 1, 3, 1, 100 x 55 x 90, 42.00)
INSERT INTO containers values (1, 2, 4, NULL, 100 x 55 x 90, 42.00)
INSERT INTO containers values (2, 1, 5, 6, 100 x 55 x 90, 32.00)
INSERT INTO containers values (2, 1, 6, 5, 100 x 55 x 90, 40.00)
INSERT INTO containers values (3, 1, 6, NULL, 100 x 55 x 90, 32.00)

~ dbo.pets has a 'petDescription' for EACH pet used in official forms.

~ dbo.containers has a string 'cnDimensions' holding the dimensions of EACH container.

~ dbo.exports has a field to hold a string 'expString' which concatenates the petDescription(s) for ALL pet(s) associated with one export record.

Desired outcome is to be able to populate dbo.exports.expString with a concatenated string that can produce all of the following:

FOR ONE CONTAINER CONTAINING ONE ANIMAL: boxNumber + ' ' + petDescription + ' ' + containerDimensions

FOR ONE CONTAINER CONTAINING MORE THAN ONE ANIMAL: boxNumber + ' ' + petDescription + ' sharing with ' + petDescription + ' sharing with' + petDescription + ' ' + containerDimensions

FOR MULTIPLE CONTAINERs, SOME CONTAINING MORE THAN ONE ANIMAL:
boxNumber + ' ' + petDescription + ' sharing with ' + petDescription + cnDimensions +
boxNumber + ' ' + petDescription + ' sharing with ' + petDescription + ' sharing with' + petDescription + cnDimensions +
boxNumber + ' ' + petDescription + ' ' + cnDimensions

I have a feeling that this may have to be written as a function, but I don;t have any idea how to start doing that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources