June 29, 2010 at 8:08 am
(Short) Description of goal:
This "results" of the query I am asking for help with will aid in dispatching the correct fire trucks for a specific area.
(Long) Description of goal:
There are 9 fire stations in our "network" that cover approx 100 square miles. The area is divided up into small geographic areas called ZONES (approx 1 square each).
There are 5 FIRE TRUCK TYPES at each station: Medic, Engine, Ladder, Rescue, and Battalion
There are 4 CALL CATEGORIES: Medical1, Medical2, Fire1, Fire2.
For each ZONE, there is a pre-determined order of different types of trucks that respond to certain types of calls. For instance, a Medical1 call will have "1 Medic" respond, a Medical2 call will have "1 Medic, 1 Engine, and 1 Battalion" respond, a Fire1 call will have "2 Engines, 1 Ladder, and 1 Battalion", and a Fire2 call will have 3 Engines, 2 Ladders, 1 Rescue, 1 Medic, and 1 Battalion" respond.
The order at which the trucks respond also varies depending on what ZONE the emergency occurs in. For instance, for a Fire1 call in ZONE 1A, the trucks that respond would be ENG1, ENG2, LAD1, BAT1, but for a Fire1 call in ZONE 5C, the trucks might be ENG8, ENG6, LAD8, and BAT8.
All of this information is stored in 2 tables FZZone, and FZOrder. In FZZone the layout looks like this...
TABLE: FZZone:
(Field FZCallOrderID is ForeignKey to Table FZOrder)
ZONE, ZONECALLID, TruckType, TruckQty, FZCallOrderID
1A, Medical1.1A, MED, 1, MED.1A
1A, Medical2.1A, MED, 1, MED.1A
1A, Medical2.1A, ENG, 1, ENG.1A
1A, Medical2.1A, BAT, 1, BAT.1A
1A, Fire1.1A, ENG, 2, ENG.1A
1A, Fire1.1A, LAD, 1, LAD.1A
1A, Fire1.1A, BAT, 1, BAT.1A
1A, Fire2.1A, ENG, 3, ENG.1A
1A, Fire2.1A, LAD, 2, LAD.1A
1A, Fire2.1A, RESC, 1, RESC.1A
1A, Fire2.1A, MED, 1, MED.1A
1A, Fire2.1A, BAT, 1, BAT.1A
5C, Medical1.5C, MED, 1, MED.5C
5C, Medical2.5C, MED, 1, MED.5C
5C, Medical2.5C, ENG, 1, ENG.5C
5C, Medical2.5C, BAT, 1, BAT.5C
5C, Fire1.5C, ENG, 2, ENG.5C
5C, Fire1.5C, LAD, 1, LAD.5C
5C, Fire1.5C, BAT, 1, BAT.5C
5C, Fire2.5C, ENG, 3, ENG.5C
5C, Fire2.5C, LAD, 2, LAD.5C
5C, Fire2.5C, RESC, 1, RESC.5C
5C, Fire2.5C, MED, 1, MED.5C
5C, Fire2.5C, BAT, 1, BAT.5C
The FZOrder table contains different types of trucks and what order they should be called for that particular zone. (It contains 9 entries for each type of truck in case there are multiple calls, and say the first Engine in a list is on another run...)
TABLE: FZOrder:
(Field FZCallOrderID is PrimaryKey to Table FZZone)
FZCallOrderID, CallOrder, TruckDesc
ENG.1A, 1, ENG1
ENG.1A, 2, ENG2
ENG.1A, 3, ENG3
ENG.1A, 4, ENG4
ENG.1A, 5, ENG5
ENG.1A, 6, ENG6
ENG.1A, 7, ENG7
ENG.1A, 8, ENG8
ENG.1A, 9, ENG9
LAD.1A, 1, LAD1
LAD.1A, 2, LAD2
LAD.1A, 3, LAD3
LAD.1A, 4, LAD4
LAD.1A, 5, LAD5
LAD.1A, 6, LAD6
LAD.1A, 7, LAD7
LAD.1A, 8, LAD8
LAD.1A, 9, LAD9
MED.1A, 1, MED1
MED.1A, 2, MED2
MED.1A, 3, MED3
MED.1A, 4, MED4
MED.1A, 5, MED5
MED.1A, 6, MED6
MED.1A, 7, MED7
MED.1A, 8, MED8
MED.1A, 9, MED9
RESC.1A, 1, RESC1
RESC.1A, 2, RESC2
RESC.1A, 3, RESC3
RESC.1A, 4, RESC4
RESC.1A, 5, RESC5
RESC.1A, 6, RESC6
RESC.1A, 7, RESC7
RESC.1A, 8, RESC8
RESC.1A, 9, RESC9
BAT.1A, 1, BAT1
BAT.1A, 2, BAT2
BAT.1A, 3, BAT3
BAT.1A, 4, BAT4
BAT.1A, 5, BAT5
BAT.1A, 6, BAT6
BAT.1A, 7, BAT7
BAT.1A, 8, BAT8
BAT.1A, 9, BAT9
ENG.5C, 1, ENG8
ENG.5C, 2, ENG6
ENG.5C, 3, ENG7
ENG.5C, 4, ENG5
ENG.5C, 5, ENG3
ENG.5C, 6, ENG9
ENG.5C, 7, ENG4
ENG.5C, 8, ENG2
ENG.5C, 9, ENG1
LAD.5C, 1, LAD8
LAD.5C, 2, LAD6
LAD.5C, 3, LAD7
LAD.5C, 4, LAD5
LAD.5C, 5, LAD3
LAD.5C, 6, LAD9
LAD.5C, 7, LAD4
LAD.5C, 8, LAD2
LAD.5C, 9, LAD1
MED.5C, 1, MED8
MED.5C, 2, MED6
MED.5C, 3, MED7
MED.5C, 4, MED5
MED.5C, 5, MED3
MED.5C, 6, MED9
MED.5C, 7, MED4
MED.5C, 8, MED2
MED.5C, 9, MED1
RESC.5C, 1, RESC8
RESC.5C, 2, RESC6
RESC.5C, 3, RESC7
RESC.5C, 4, RESC5
RESC.5C, 5, RESC3
RESC.5C, 6, RESC9
RESC.5C, 7, RESC4
RESC.5C, 8, RESC2
RESC.5C, 9, RESC1
BAT.5C, 1, BAT8
BAT.5C, 2, BAT6
BAT.5C, 3, BAT7
BAT.5C, 4, BAT5
BAT.5C, 5, BAT3
BAT.5C, 6, BAT9
BAT.5C, 7, BAT4
BAT.5C, 8, BAT2
BAT.5C, 9, BAT1
So I want my query result to look something like this.... I'm thinking that I would be something like a function that I would call....
GetTruckList(Fire2.5C) would return (3 Engines, 2 Ladders, 1 Rescue, 1 Medic, 1 Battalion)...
The result would look like this:
ENG8
ENG6
ENG7
LAD8
LAD6
RESC8
MED8
BAT8
Any help or suggestions would be greatly appreciated....
Thanks!
J Miller
June 29, 2010 at 8:39 am
Dear J Miller,
Please click the link at the botom of my signature. It will help you to present your question in a right way. I believe you will quickly get some good answers if you follow my advice...
June 29, 2010 at 8:51 am
You're getting there, but you need to take another look at your tables, there should be many more than the two listed. I'd guess about 15.
For instance:
Table StationVehicles
StationVehTypeID VehicleTypeStation Quantity
1ENG8 1
2ENG6 1
3LAD8 1
4BAT8 1
Table CallResponses
CallResponseIDCallCategoryVehicleTypeQuantity
1"Medical1" MED1
2"Medical2" MED1
3"Medical2" ENG1
4"Medical2" BAT1
You can already see how this would change your existing design, which has a number of entity pairs (Medical1.1A) where they should be single entities (call category=Medical1, zone=1A).
Your post is far from trivial, it will help yourself and other contributors for you to get stuck into a decent textbook of database design.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply