I was wondering if somebody could help me, or tell me if this is even possible.
I have a query that returns what I need, but now I have been asked to add a count to it that comes from another query.
This is what I am working with so far.
(My addition to the previously working query is the (select count(...)part of this.
select
tractor.id tractor_id,
tractor.dispatcher driver_manager,
tractor.driver1_id driver_id,
(select count(*)
from movement, equipment_item
where movement.equipment_group_id = equipment_item.equipment_group_id
and movement.status = 'A'
and equipment_item.equipment_type_id = 'T'
and equipment_item.equipment_id = tractor.id) as preassignments,
movement_order.order_id order_id,
DATEDIFF(day, stop.actual_arrival, GETDATE()) days,
stop.actual_arrival actual_arrival,
stop.city_name city_name,
stop.state stop_state,
location.name loc_name
from
tractor,
equipment_group left outer join equipment_item equipment_item on equipment_item.equipment_group_id = equipment_group.id
and equipment_item.equipment_type_id = 'T' and equipment_item.type_sequence = 0 and equipment_item.company_id = 'TMS',
movement left outer join movement_order on movement_order.movement_id = movement.id and movement_order.company_id = 'TMS',
stop left outer join location on location.id = stop.location_id and location.company_id = 'TMS'
where tractor.company_id = 'TMS'
and tractor.service_status = 'A'
and tractor.fleet_id <> '99'
and tractor.currenteqpgrpid = equipment_group.id
and equipment_group.company_id = 'TMS'
and movement.equipment_group_id = equipment_group.id
and equipment_group.currentmovement_id=movement.id
and movement.company_id = 'TMS'
and stop.id = movement.dest_stop_id
and stop.actual_arrival < DATEADD(day, -5, GETDATE())
and stop.company_id = 'TMS'
order by days desc
This does run, but I get an error: Could not parse sql statement to get count.
Am I even close to getting what I want?
What I would like to see is:
tractor_id | driver_man | driver_id | preassignments | order_id | days | actual_arrival | city_name | stop_state | loc_name.
If this is not possible, I could just exclude rows that have a count > ZERO. That is my ultimate goal, but I can filter those out in my report if needed.
Thanks in advance.....
- Chris
I have a query that returns what I need, but now I have been asked to add a count to it that comes from another query.
This is what I am working with so far.
(My addition to the previously working query is the (select count(...)part of this.
select
tractor.id tractor_id,
tractor.dispatcher driver_manager,
tractor.driver1_id driver_id,
(select count(*)
from movement, equipment_item
where movement.equipment_group_id = equipment_item.equipment_group_id
and movement.status = 'A'
and equipment_item.equipment_type_id = 'T'
and equipment_item.equipment_id = tractor.id) as preassignments,
movement_order.order_id order_id,
DATEDIFF(day, stop.actual_arrival, GETDATE()) days,
stop.actual_arrival actual_arrival,
stop.city_name city_name,
stop.state stop_state,
location.name loc_name
from
tractor,
equipment_group left outer join equipment_item equipment_item on equipment_item.equipment_group_id = equipment_group.id
and equipment_item.equipment_type_id = 'T' and equipment_item.type_sequence = 0 and equipment_item.company_id = 'TMS',
movement left outer join movement_order on movement_order.movement_id = movement.id and movement_order.company_id = 'TMS',
stop left outer join location on location.id = stop.location_id and location.company_id = 'TMS'
where tractor.company_id = 'TMS'
and tractor.service_status = 'A'
and tractor.fleet_id <> '99'
and tractor.currenteqpgrpid = equipment_group.id
and equipment_group.company_id = 'TMS'
and movement.equipment_group_id = equipment_group.id
and equipment_group.currentmovement_id=movement.id
and movement.company_id = 'TMS'
and stop.id = movement.dest_stop_id
and stop.actual_arrival < DATEADD(day, -5, GETDATE())
and stop.company_id = 'TMS'
order by days desc
This does run, but I get an error: Could not parse sql statement to get count.
Am I even close to getting what I want?
What I would like to see is:
tractor_id | driver_man | driver_id | preassignments | order_id | days | actual_arrival | city_name | stop_state | loc_name.
If this is not possible, I could just exclude rows that have a count > ZERO. That is my ultimate goal, but I can filter those out in my report if needed.
Thanks in advance.....
- Chris