Excluding data in groups when met by a condition

[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

 

[bborissov]

You don't want group, you want filtered recordset.

SELECT * FROM MyTable
    WHERE Expire_Date <= GETDATE() AND Oc_Id = 10
ORDER BY Oc_Id, Customer_Id, Expire_Date

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[bborissov]

Sorry, I didn't understood your question right when I post my first reply. Try this:

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

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[philsivyer]

Thanks - but not the solution - probably my poor attempt to explain the problem.
I do not want to select just oc_id = 10 .... there will be thousands of oc_ids but I only want oc_ids (this is a type of journal by the way) and within each group of journals there will be customers (customer_id) who will have various order numbers with different expire dates. If within any grouping of oc_id then by customer_id there is an expire date <= today then I do not want any of those records returned. Another example..

Starting 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
11 007 3456 02/02/2006
11 007 2221 04/05/2006
12 003 5690 03/03/2005
12 003 7773 04/05/2006

Finishing 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 007 3456 02/02/2006
11 007 2221 04/05/2006
12 003 5690 03/03/2005
12 003 7773 04/05/2006

Now you will see the following data excluded.
11 002 4567 01/01/2005
11 002 6789 01/01/2007

this is because within the grouping of oc_id then customer-id - one line in the expiry date is > today

thanks Phil

 

[bborissov]

Phil,
Not your explanation, my reading is the problem. Did you check my second example? I think it should works.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[philsivyer]

BRILLIANT!!

Many thanks