I am trying to select data from four different tables: orders, appointment, plan, and scorecard. For the appointment, plan, and scorecard tables I am trying to extract pickup and delivery dates. All orders have plan and scorecard dates but not all have appointment dates. Appointment and plan tables have one row for pickup and one row for delivery, scorecard table has both in a single row. The scorecard table just has one pickup and one delivery date for each order that never changes, but for the appointment and plan tables, the dates can be revised for a single order due to reschedules, order updates, etc. Each time an appointment or plan date is revised, a new entry is added to the table. I am looking for the latest revised appointment and plan dates. Not necessarily the max date, because sometimes the appointments and plan dates are rescheduled to earlier dates.
This query is just for a single order id (eventually I would like to select a number of orders for a specific date range), and it returns three rows because there were two additional revisions to the appointment. Leaving the query like this would reflect all revisions of appointment and plan dates. I want just the one row with the latest appointment and plan dates.
select o.order_primary_reference, o.order_id,
pu.appointment_date as latest_pickup_appt,
dl.appointment_date as latest_del_appt,
pick.plan_date as latest_pickup_plandate, del.plan_date as latest_del_plandate,
sc.scorecard_exp_pickup_date as scorecard_expected_pickup, sc.scorecard_exp_delivery_date as scorecard_expected_del
from orders o
join plan pick
on o.order_id = pick.plan_order_id and pick.stop_type = 'P'
join plan del
on o.order_id = del.plan_order_id and del.stop_type = 'D'
join scorecard sc
on o.order_id = sc.scorecard_order_id
left outer join appointment pu
on o.order_id = pu.appointment_order_id and pu.appointment_type = 'P'
left outer join appointment dl
on o.order_id = dl.appointment_order_id and dl.appointment_type = 'D'
where o.order_id = 123456
This query will get the date that I want in this instance (the most recently added pickup appointment from the appointment table), but when I tried incorporating this into my larger query as a subquery I was getting a too many values error. How can I incorporate selecting the most recently added appointment and plan dates into my query?
select *
from
(select pu.appointment_date,
row_number() over (partition by pu.appointment_order_id
order by pu.appointment_date dsc) rn
from appointment pu
where pu.appointment_type = 'P'
and pu.appointment_order_id = 123456
)
where rn = 1
