Well I apologize as I am not very good at explaining this. We have a computer aided dispatch system with a sql database that stores information about 911 calls. Such as address, caller, time the call time came in, time units dispatched, arrived and so on.
We have quite a lot of apartment buildings and we are trying find a way to run reports on those apartments. In the table where call information is stored there is a street number field and a street name field. There is also another table that stores information about street ranges. There is from street#, a to street# and the street name. There is also a category field. What we planned on doing is getting the from street, to street and street name from this table based on a certain category. Then using this information get the information from the other table from the street# and street name columns.
I have tried subqueries and derived columns to get the data and I don't think a join will work in this case.
Here is kind of what I think I need to do.
Query:select rangefr, rangeto, street from premrang where category = 'CFMH'
3900 3900 N FULTON AVE
600 600 RESERVE BLVD
700 700 RESERVE BLVD
400 499 APPLEWOOD CT
4400 4699 APPLEWOOD CT
Take that information and loop it through this query.
select * from inmain
where calltime > '2013-01-01'
and streetno > 400
and streetno < 499
and streetonly = 'APPLEWOOD CT'
I am think I should be able to get the data I want but I have not put it together yet.