January 2, 2012 at 10:22 am
I have several tables with the following fields and datatypes
query involving multiple tables
1. State definition table
StateID char(2), AgencyName Varchar(35)
AL, Alaska
GA, Georgia
CO, Colorado
FL, Florida
2. Cities definition table
StateID int, Branch Name varchar(35), StateIDchar(2)
1000, Ancorage, AK
1001, Lakes, AK
1002, Kodiac, AK
2000, Atlanta, GA
2001, Berlin, GA
3000, Aurora, CO
3001, Denver, CO
3002, Boulder, CO
4000, Orlando, FL
4001, Tampa, FL
3. Offices definition table
OfficeName Varchar(35), OfficeDesc Varchar(50)
1000 Office10, .....
1001 Office11,......
1002 Office12,......
2000 Office20,......
2001 Office21,......
3000 Office30,......
3001 Office31,......
3002 Office32,......
4000 Office40,......
4001 Office41,......
here, each city has just one office( office name is preceded by cityid), but a city can have many offices in it
4.Vehicles definition table
VehicleID int, OfficeName, VehicleDesc
100, 1000 Office1,.....
101, 1000 Office1,.....
102, 1000 Office1,.....
103, 1000 Office1,.....
104, 1001 office2,.....
105, 1001 office2,.....
106, 1002 office3,.....
107, 2000 office4,.....
108, 2000 office4,.....
109, 2000 office4,.....
110, 2001 office5,.....
111, 2001 office5,.....
112, 2001 office5,.....
113, 3000 office6,.....
114, 3000 office6,.....
There are several other tables which store only the vehicle id and other related atributes of the vehicle
for eg. voltage table
vehicle id int, batteryvolts int,eventdate datetime
100, 10286, 8/1/2007 9:20:25 AM
100, 10289, 8/1/2007 9:20:28 AM
100, 10289, 8/1/2007 9:21:18 AM
100, 10286, 8/1/2007 9:21:25 AM
101, 10286, 8/1/2007 9:20:25 AM
101, 10286, 8/1/2007 9:20:25 AM
101, 10286, 8/1/2007 9:20:25 AM
102, 10286, 8/1/2007 9:20:25 AM
102, 10286, 8/1/2007 9:20:25 AM
103, 10286, 8/1/2007 9:20:25 AM
103, 10286, 8/1/2007 9:20:25 AM
103, 10286, 8/1/2007 9:20:25 AM
109, 10286, 8/1/2007 9:20:25 AM
109, 10286, 8/1/2007 9:20:25 AM
109, 10286, 8/1/2007 9:20:25 AM
VehicleUsage table
vehicleid int, odometer int, ignition int, recorddate datetime
I have to generate a report where the user can query either by state, or city, or office or vehicles and you can imagine all possible combinations of parameters there by all state, one or multiple states, all offices and all vehicles, one office or all its vehicles, one or multiple offices and one or more vehicles of the selected offices, all vehicles only, one or multiple vehicles only, etc
what i want to do is produce a report which lists state, city, office, vehicleid, batteryvolts, odometer, ignition where battereyvolts, odometer, igntion
are fields in other tables and are calulated values summed based on the max or min date or other conditions in the where clause
Currently I am doing a left outer join where I start with state, left outer join with city on stateid, and left outer join with office on city id and so on.But since database is farily large with large number of records and is growing everyday, I am worried about the performance of the query as it could be very quick if the user queries for just one vehicle, but could take much longer if it involves calculating for several vehicles of several offices
and so on
Any suggestions on how to tackle this problem?
January 2, 2012 at 12:00 pm
It looks like what you are trying to do falls into the category of Catch All queries. Here's an excellent article by Gail Shaw on the subject:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
You should probably have a master stored procedure into which you pass all of the parameters. Then based on which ones the user entered, call another stored procedure for those parameters. When I do this sort of thing I usually find that I can break down what the user wants into about 4 groups. Each group will have its own stored procedure.
Todd Fifield
January 2, 2012 at 12:15 pm
Yes you got me exactly. While I am trying to catch all queries, I am also dealing with the hierarchy where a state can have multiple cities, a city can have multiple offices, an office can have multiple vehicles.
the query I have is something like below
ALTER PROCEDURE [dbo].[reportOneVehicle]
@VehicleID int
AS
BEGIN
SET NOCOUNT ON;
select a.officename , b.vehicleid,
c.odometer ,c.Ignition ,e.BatteryVoltage/1000 as Voltage, f.TotalAccum
from
(
(select officename, officedesc from Offices) as a
left outer join
(select vehicleid, officename from vehicle where vehicleid = 100 ) as b
on a.officename = b.officename
left outer join
(select vehicleid, odometer/1609.344 as odometer, ignition/3600 as ignition, recorddate from vehicleusage ) as c
on b.vehicleid = c.vehicleid)
left outer join
(select vehicleid,(convert(varchar(6), (max(AccumSeconds))/3600)+ ':' +
RIGHT('0' + convert(varchar(2), ((MAX(AccumSeconds))%3600)/60),2) + ':' +
RIGHT('0' + convert(varchar(2), (max(AccumSeconds))%60),2)) as TotalAccum
from AccumulatedTotal
where Desc = 'Motion' group by VehicleID) as f
on b.vehicle = f.vehicleid
left outer join
(select p.vehicleid, p.BatteryVoltage, p.eventdate from voltage p where
p.eventdate = (select max(eventdate) as eventdate from voltage
where vehicleid = p.vehicleid)) as e
on b.vehicleid = e.vehicleid
order by b.Officename,b.vehicleid
END
it is okay with this query as long as it is just one vehicle. But if the user selects one or multiple vehicles in combination with offices or city or vehicle, I want to come up with an effecient query which will loop through all the vehicles in the user provided state or city or office.
January 2, 2012 at 12:49 pm
Suhanats - I'd recommend you start again from scratch with this query. With all the unnecessary nesting it's almost impossible to figure out what it's doing - and what it's meant to be doing. Start simple and gradually build up the query. Folks here will help you along the way and you will learn plenty en route.
Can I suggest you first read the link in my sig "please read this". Not only will it explain how best to pose a question in order to receive fast and accurate replies, it will also show you how to quickly set up sample data for you and other folks to test against - well worth doing.
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply