SQL command required to show the first delivery type for each customer and also a distinct count

[japeconsulting]

I need a sql command that can show the first delivery type for each customer and I have managed to achieve this with OVER PARTITION BY using ROW NUMBER 1 but I also need a distinct count for each order and apparently this is not possible with this clause. Can anyone come up with a a simple sql command I can use for both.

Example as follows:

Customer ID Order ID Delivery Type Order Date
1.00 1 Pickup 26/01/2005
1.00 1 Parcel Post 30/01/2005
1.00 1 UPS 02/02/2005
1.00 2 Loomis 04/02/2005
1.00 2 Loomis 28/02/2005
1.00 3 FedEx 09/04/2005
2.00 4 Loomis 07/01/2005
2.00 4 Loomis 15/01/2005
2.00 4 FedEx 29/01/2005
2.00 5 Loomis 29/01/2005
2.00 5 Pickup 13/02/2005

Results needed are:
Customer ID Distinct Count of orders Delivery type entry for first order date First Order Date
1.00 3 Pickup 26/01/2005
2.00 2 Loomis 07/01/2005

Thanks
Jackie

 

[hilfy]

Ok, assuming that this is all in one table called Pickups and you have start and end date parameters, here's what I would do:

Select 
  p.customerID,
  Count(p.orderDate) as OrderCount,
  firstDate.firstDay
  firstDelivery.deliveryType
from Pickups p
  inner join (
    select customerID, min(orderDate) as firstDay
    from pickups
    where orderDate >= {?Start Date}
      and order Date < {?End Date}) as firstDate
   on p.CustomerID = firstDate.CustomerID
  inner join (
    select customerID, deliveryType, orderDate
    from Pickups
    where orderDate >= {?Start Date}
      and order Date < {?End Date}) as firstDelivery
  on firstDate.CustomerId = firstDelivery.CustomerID
    and firstDate.firstDay = firstDelivery.orderDate
where p.orderDate >= {?Start Date}
  and p.orderDate < {?End Date}
group by
  p.CustomerId,
  firstDate.firstDay,
  firstDelivery.deliveryType

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com