CODEWITHSUNDEEP
sql
limlim
limlim

Reputation: 405

SQL Find next Date for each Customer, SQL For each?

I have some problems with an SQL statement. I need to find the next DeliveryDate for each Customer in the following setup.

Tables

Each Customer may have several DeliveryOrders on the same deliveryDate. I just can't figure out how to only get one deliveryDate for each customer. The date should be the next upcoming DeliveryDate after today. I feel like I would need some sort of "for each" here but I don't know how to solve it in SQL.

Upvotes: 2

Views: 472

Answers (3)

mslliviu
mslliviu

Reputation: 1138

Another simpler version

select c.id, min(o.date)
from customer c 
    inner join deliveryordercustomer co o on co.customerId = c.id 
    inner join deliveryorder o on co.deliveryOrderId = o.id and o.date>getdate()
group by c.id

Upvotes: 1

TheCodeKing
TheCodeKing

Reputation: 19230

You need to use a group by. There's a lot of ways to do this, here's my solution that takes into account multiple orders on same day for customer, and allows you to query different delivery slots, first, second etc. This assumes Sql Server 2005 and above.

;with CustomerDeliveries as
(
  Select c.id, do.deliveryDate, Rank() 
               over (Partition BY c.id order by do.deliveryDate) as DeliverySlot
  From   Customer c
         inner join DeliveryOrderCustomer doc on c.id = doc.customerId
         inner join DeliveryOrder do on do.id = doc.deliveryOrderId
  Where  do.deliveryDate>GETDATE()
  Group By c.id, do.deliveryDate
)
Select id, deliveryDate
From   CustomerDeliveries
Where  DeliverySlot = 1

Upvotes: 1

Xavi López
Xavi López

Reputation: 27880

This would give the expected results using a subselect. Take into account that current_date may be rdbms specific, it works for Oracle.

select c.id, o.date 
from customer c 
    inner join deliveryordercustomer co o on co.customerId = c.id 
    inner join deliveryorder o on co.deliveryOrderId = o.id 
where o.date = 
     (select min(o2.date) 
      from deliveryorder o2 
      where o2.id = co.deliveryOrderId and o2.date > current_date)

Upvotes: 1

Related Questions