Returning data only with date field <= today - using groups

[philsivyer]

Example...... initial data set

oc_id customer_id order_no expire_date
10 001 0123 01/01/2004
10 001 0124 01/01/2005
10 001 0125 01/01/2006
11 002 4567 01/01/2005
11 002 6789 01/01/2007

Now to build a script with the following rules.....

I would like to group by oc_id then customer_id but only return data in a group where the expire_date is <= today.
Although - not grouping on order_no - I still want to see the order numbers and expire dates within those two groups.
So, when the script is run it would only return the following.....

oc_id customer_id order_no expire_date
10 001 0123 01/01/2004
10 001 0124 01/01/2005
10 001 0125 01/01/2006

Note: I do not want this

oc_id customer_id order_no expire_date
10 001 0123 01/01/2004
10 001 0124 01/01/2005
10 001 0125 01/01/2006
11 002 4567 01/01/2005

Hope this makes sense and your help much appreciated

Thanks
Phil

 

[unclerico]

try something along these lines:

SELECT a.oc_id, a.customer_id, a.order_no, a.expire_date
  FROM table1 a
 INNER JOIN (SELECT oc_id, MAX(expire_date) max_date
               FROM table1
              GROUP BY oc_id
             HAVING MAX(expire_date) <= GETDATE()) b ON a.oc_id = b.oc_id

 

[philsivyer]

Just like to say thanks for the response and will try your suggestion - I already had one reply to my query as per below and know this works.


SELECT MyTable.*
FROM MyTable
LEFT JOIN (SELECT Oc_Id, Customer_Id FROM MyTable
WHERE Expire_Date > GETDATE()) Tbl1
ON MyTable.Oc_Id = Tbl1.Oc_Id AND
MyTable.Customer_Id = Tbl1.Customer_Id
WHERE Tbl1.Oc_Id IS NULL