I need to do the following. I have 9 records linked by a schedule_id. these 9 records are broken into 3 sets of 3 for example of Leg 1 leg 2 and leg 3, so i have the following
order sched leg site date
1 1 1 1 2014-01-01
2 1 2 34 2014-12-31
3 1 1 15 2014-04-23
4 1 3 4 2014-07-12
5 1 2 89 2014-04-10
6 1 1 13 2014-09-09
7 1 3 9 2014-03-12
8 1 3 10 2014-11-10
9 1 2 11 2014-08-08
In my sql I group by leg so I land up with 3 records. I need to extract the site ids that belong to the earliest and latest date in each leg group and then join my site information table to the resulting ids to extractcompanynames etc for these sites.
my sql code for the legs is as follows
select l.leg_no,
l.start_region,
r.region_name as end_region,
l.rate,
sum(x.est_distance) as distance,
sum(x.est_weight) as weight,
count(x.order_id) as count,
min(x.req_col_time) as earliesttime,
sum(x.total_rpb) as total_rpb,
max(x.req_del_time) as latesttime from
(select ord2.* from loads as l1
left join orders as ord2 on ord2.load_id = l1.load_id
left join debrief_docs as db2 on db2.oid = ord2.order_id
where l1.schedule_id = '.$id.'
union
select ord3.* from loads as l2
left join drops as drop1 on drop1.load_id = l2.load_id
left join orders as ord3 on ord3.drop_id = drop1.drop_id
left join debrief_docs as db3 on db3.oid = ord3.order_id
where l2.schedule_id = '.$id.')as x
join loads as l on l.schedule_id = '.$id.'
join regions as r on r.region_id = l.region_id
group by l.leg_no asc
Any ideas on how to achieve this would be greatly appreciated. ;) thank you all
Let's approach the important part of the question, which is getting the first and last site ids for each leg group. I would use variables for this purpose. The following query enumerates the leg ids:
select l.*,
(@rn := if(@l = leg_id, @rn + 1,
if(@l := leg_id, 1, 1)
)
) as seqnum
from loads l cross join
(select @rn := 0, @l := 0) vars
order by schedule_id, leg_id, date;
The following summarizes the records and produces two columns, the first and last site:
select schedule_id, leg_id,
max(case when seqnum = 1 then site_id end) as first_site,
max(case when seqnum = 3 then site_id end) as last_site
from (select l.*,
(@rn := if(@l = leg_id, @rn + 1,
if(@l := leg_id, 1, 1)
)
) as seqnum
from loads l cross join
(select @rn := 0, @l := 0) vars
order by schedule_id, leg_id, date
) l
group by schedule_id, leg_id;
You can then join in the rest of the tables to get the additional information that you want.