Hi,

I've got a request from a customer and I'm not sure quite how to go
about it. I'm wondering if it's possible to do in SQL, and if not - what
recommendations anyone might have.

My customers are loading containers onto a train. The train is broken up
into individual wagons, and the wagons have individual slot numbers.

The wagons on a train may differ from day to day.

My customers have a lookup table with wagon numbers. Amongst this data
is the number of slots per wagon.

(Some wagons have 2 slots, others have 3)

Let's call this the WAGON table, and it will look something like this

(Wagon Lookup table)
WAGON   MAXSLOTS
WAGON1   2
WAGON2   2
WAGON3   3
WAGON4   2
WAGON5   3
.... etc


My clients have a second table where they load the container information
into their system. On this table, they allocate the wagon number, and
the slot number that the container is loaded into...

(Container Table)
CONTAINER   TRAIN   WAGON   SLOT
CONT1      TRAIN1   WAGON1   1
CONT2      TRAIN1   WAGON1   2
CONT3      TRAIN1   WAGON2   1
CONT4      TRAIN1   WAGON3   2
CONT5      TRAIN1   WAGON3   3
.... etc



Now the task I have been assigned is to develop a result set that shows
a list of all wagons and available slots for a particular train -
regardless of whether that slot is used or not.

So, for instance with the data above you can see that WAGON 2 has 2
slots available, but only 1 container has been loaded (into slot 1) in
the 2nd table.

Likewise WAGON3 has 3 slots available, but only slots 2 and 3 have been
allocated containers.

My client would like the data to show as follows:

TRAIN   WAGON   SLOT   CONTAINER
TRAIN1   WAGON1   1   CONT1
TRAIN1   WAGON1   2   CONT2
TRAIN1   WAGON2   1   CONT3
TRAIN1   WAGON2   2   
TRAIN1   WAGON3   1   
TRAIN1   WAGON3   2   CONT4
TRAIN1   WAGON3   3   CONT4


The problem I face is that I don't currently have any source data that
holds one record per slot - only those that are either filled, or
records showing the total number of slots available.

Some how I need to be able to use the wagon lookup table to generate a
record per available slot.

If I wasn't using SQL I guess I could create a temporary table, go
through the Wagon Lookup Table, and perform a loop per wagon to add a
record for wagon and slot until I reach the max number of wagons, and
then move to the next record. I'm just wondering if I can do something
similar in SQL?

Cheers

Adam.

Hi Adam

Bearing in I'm using DBISAM3  (yes 3), the following SQL seems to do what
you want.

================================
DROP TABLE IF EXISTS MEMORY Slots
;
CREATE TABLE MEMORY Slots
(Wagon VARCHAR(10), Slot INTEGER)
;
INSERT INTO MEMORY Slots
SELECT Wagon, 1 FROM Wagons
;
INSERT INTO MEMORY Slots
SELECT Wagon, 2 FROM Wagons
;
INSERT INTO MEMORY Slots
SELECT Wagon, 3 FROM Wagons WHERE MaxSlots > 2
;
SELECT C.Train, S.Wagon, S.Slot, C.Container
FROM MEMORY  Slots S
LEFT OUTER JOIN Containers C ON (C.Wagon = S.Wagon AND C.Slot = S.Slot)
WHERE NOT C.Train IS NULL
ORDER BY C.Train, S.Wagon, S.Slot, C.Container
================
Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Adam H." <ahairsub5@removeme.jvxp.com> wrote in message
news:33F07D20-6649-49C1-A15E-1B588E15ABA6@news.elevatesoft.com...
> Hi,
>
> I've got a request from a customer and I'm not sure quite how to go about
> it. I'm wondering if it's possible to do in SQL, and if not - what
> recommendations anyone might have.
>
> My customers are loading containers onto a train. The train is broken up
> into individual wagons, and the wagons have individual slot numbers.
>
> The wagons on a train may differ from day to day.
>
> My customers have a lookup table with wagon numbers. Amongst this data is
> the number of slots per wagon.
>
> (Some wagons have 2 slots, others have 3)
>
> Let's call this the WAGON table, and it will look something like this
>
> (Wagon Lookup table)
> WAGON MAXSLOTS
> WAGON1 2
> WAGON2 2
> WAGON3 3
> WAGON4 2
> WAGON5 3
> ... etc
>
>
> My clients have a second table where they load the container information
> into their system. On this table, they allocate the wagon number, and the
> slot number that the container is loaded into...
>
> (Container Table)
> CONTAINER TRAIN WAGON SLOT
> CONT1 TRAIN1 WAGON1 1
> CONT2 TRAIN1 WAGON1 2
> CONT3 TRAIN1 WAGON2 1
> CONT4 TRAIN1 WAGON3 2
> CONT5 TRAIN1 WAGON3 3
> ... etc
>
>
>
> Now the task I have been assigned is to develop a result set that shows a
> list of all wagons and available slots for a particular train - regardless
> of whether that slot is used or not.
>
> So, for instance with the data above you can see that WAGON 2 has 2 slots
> available, but only 1 container has been loaded (into slot 1) in the 2nd
> table.
>
> Likewise WAGON3 has 3 slots available, but only slots 2 and 3 have been
> allocated containers.
>
> My client would like the data to show as follows:
>
> TRAIN WAGON SLOT CONTAINER
> TRAIN1 WAGON1 1 CONT1
> TRAIN1 WAGON1 2 CONT2
> TRAIN1 WAGON2 1 CONT3
> TRAIN1 WAGON2 2 TRAIN1 WAGON3 1 TRAIN1 WAGON3 2 CONT4
> TRAIN1 WAGON3 3 CONT4
>
>
> The problem I face is that I don't currently have any source data that
> holds one record per slot - only those that are either filled, or records
> showing the total number of slots available.
>
> Some how I need to be able to use the wagon lookup table to generate a
> record per available slot.
>
> If I wasn't using SQL I guess I could create a temporary table, go through
> the Wagon Lookup Table, and perform a loop per wagon to add a record for
> wagon and slot until I reach the max number of wagons, and then move to
> the next record. I'm just wondering if I can do something similar in SQL?
>
> Cheers
>
> Adam.

Adam


As far as I can see you only have a link between trains and wagons when at least one slot is allocated. If I'm right then on that basis you can not identify totally empty wagons eg if wagon 4 is on train 1 you have no way of knowing. You need an extra table to say which wagons are on which trains.

Unless I'm missing something as things stand there is no way you can give the client what they want.

Roy Lambert [Team Elevate]

Hi Adam

I have read and agree with Roy's post about linking Wagons to Trains - I
assumed that you had simplified the scenario for the sake of the newsgroup
question.

However, with a "Trains" table that listed the Wagons in each Train it
should be easy enough to generate a "Slots" temporary table for a particular
train (or all trains) using the same method as in my suggested SQL.   Even
simpler if the "Wagons" table has a column for the train that the wagon is
currently on.

How nice to be able to play with trains and get paid for it!!!

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:7D67BB7C-3067-466B-AEA9-AEE25C476C91@news.elevatesoft.com...
> Hi Adam
>
> Bearing in I'm using DBISAM3  (yes 3), the following SQL seems to do what
> you want.
>
> ================================
> DROP TABLE IF EXISTS MEMORY Slots
> ;
> CREATE TABLE MEMORY Slots
> (Wagon VARCHAR(10), Slot INTEGER)
> ;
> INSERT INTO MEMORY Slots
> SELECT Wagon, 1 FROM Wagons
> ;
> INSERT INTO MEMORY Slots
> SELECT Wagon, 2 FROM Wagons
> ;
> INSERT INTO MEMORY Slots
> SELECT Wagon, 3 FROM Wagons WHERE MaxSlots > 2
> ;
> SELECT C.Train, S.Wagon, S.Slot, C.Container
> FROM MEMORY  Slots S
> LEFT OUTER JOIN Containers C ON (C.Wagon = S.Wagon AND C.Slot = S.Slot)
> WHERE NOT C.Train IS NULL
> ORDER BY C.Train, S.Wagon, S.Slot, C.Container
> ================
> Cheers
>
> Jeff
>
> --
> Jeff Cook
> Aspect Systems Ltd
> www.aspect.co.nz
>
> "Adam H." <ahairsub5@removeme.jvxp.com> wrote in message
> news:33F07D20-6649-49C1-A15E-1B588E15ABA6@news.elevatesoft.com...
>> Hi,
>>
>> I've got a request from a customer and I'm not sure quite how to go about
>> it. I'm wondering if it's possible to do in SQL, and if not - what
>> recommendations anyone might have.
>>
>> My customers are loading containers onto a train. The train is broken up
>> into individual wagons, and the wagons have individual slot numbers.
>>
>> The wagons on a train may differ from day to day.
>>
>> My customers have a lookup table with wagon numbers. Amongst this data is
>> the number of slots per wagon.
>>
>> (Some wagons have 2 slots, others have 3)
>>
>> Let's call this the WAGON table, and it will look something like this
>>
>> (Wagon Lookup table)
>> WAGON MAXSLOTS
>> WAGON1 2
>> WAGON2 2
>> WAGON3 3
>> WAGON4 2
>> WAGON5 3
>> ... etc
>>
>>
>> My clients have a second table where they load the container information
>> into their system. On this table, they allocate the wagon number, and the
>> slot number that the container is loaded into...
>>
>> (Container Table)
>> CONTAINER TRAIN WAGON SLOT
>> CONT1 TRAIN1 WAGON1 1
>> CONT2 TRAIN1 WAGON1 2
>> CONT3 TRAIN1 WAGON2 1
>> CONT4 TRAIN1 WAGON3 2
>> CONT5 TRAIN1 WAGON3 3
>> ... etc
>>
>>
>>
>> Now the task I have been assigned is to develop a result set that shows a
>> list of all wagons and available slots for a particular train -
>> regardless of whether that slot is used or not.
>>
>> So, for instance with the data above you can see that WAGON 2 has 2 slots
>> available, but only 1 container has been loaded (into slot 1) in the 2nd
>> table.
>>
>> Likewise WAGON3 has 3 slots available, but only slots 2 and 3 have been
>> allocated containers.
>>
>> My client would like the data to show as follows:
>>
>> TRAIN WAGON SLOT CONTAINER
>> TRAIN1 WAGON1 1 CONT1
>> TRAIN1 WAGON1 2 CONT2
>> TRAIN1 WAGON2 1 CONT3
>> TRAIN1 WAGON2 2 TRAIN1 WAGON3 1 TRAIN1 WAGON3 2 CONT4
>> TRAIN1 WAGON3 3 CONT4
>>
>>
>> The problem I face is that I don't currently have any source data that
>> holds one record per slot - only those that are either filled, or records
>> showing the total number of slots available.
>>
>> Some how I need to be able to use the wagon lookup table to generate a
>> record per available slot.
>>
>> If I wasn't using SQL I guess I could create a temporary table, go
>> through the Wagon Lookup Table, and perform a loop per wagon to add a
>> record for wagon and slot until I reach the max number of wagons, and
>> then move to the next record. I'm just wondering if I can do something
>> similar in SQL?
>>
>> Cheers
>>
>> Adam.
>
>

Hi Roy,

Nicely picked up. My client has not mentioned that there will be any
empty wagons, only empty slots.

But if this changes, you are right - I will need to create an additional
table.

For the other part though, I think Jeff's idea will work nicely.

Cheers

Adam.

Hi Jeff,

How's it going over there across the pond. We've had some decent rain
the last week or so (unusual for this century) - so not sure if you're
about to cop a bit too.

Thanks for your reply. On the contrary your suggestion is exactly what
we're needing at this point.

Even if we do require an additional table (and I think this is something
that may occur down the track that they just haven't made themselves
aware of yet) - the same / similar approach will be easy to do.
Appreciate your help!

> How nice to be able to play with trains and get paid for it!!!

LOL - I wish. I'm stuck in an office. Don't get to go out on the tracks.

Maybe I can buy Train Sim and claim it as a work expense though.

Cheers

Adam.

Adam


>Nicely picked up. My client has not mentioned that there will be any
>empty wagons, only empty slots.

It just looked like a standard spec that hasn't been thought out fully. I'm sure they'll spot the need after you've delivered what they ask for

>But if this changes, you are right - I will need to create an additional
>table.
>
>For the other part though, I think Jeff's idea will work nicely.

So do I

Roy

> It just looked like a standard spec that hasn't been thought out fully. I'm sure they'll spot the need after you've delivered what they ask for

Hmm. Roy - you make me suspicious in thinking that you've been here
before yourself.

Adam


>> It just looked like a standard spec that hasn't been thought out fully. I'm sure they'll spot the need after you've delivered what they ask for
>
>Hmm. Roy - you make me suspicious in thinking that you've been here
>before yourself.

How can you say that. All of my users throughout my career have been caring and thoughtful, have always considered all options and possibilities and never asked for the impossible, or the possible in impossible timescales.

Roy