I gave it my best shot for hours and failed misurably!
The goal is to query for a list of GroupNumber's where NONE of the Orders within the group have ever successfully shipped. Successfully shipped orders will have a VALID tracking number in the UserDefined1 field. Valid track# would be any number over 1,000, but there could be other values in this field, such as someones initials, null or blank.
Connection: ODBC via SQL Server
GroupNumber (nvarchar) - Only has a value if a group was created
OrderNumber (nvarchar) - Will always have a value
UserDefined1 (nvarchar) - May be null, blank, some text, or a valid track#
Let me explain the process...
- Orders are created.
- A value MAY be enterd into the UserDefined1 field during order entry
- Multiple orders are then assigned to a Group#
- The Group is then shipped and each Order within the group are assigned a tracking number in the UserDefined1 field.
- Some orders within the group may NOT successfully ship, so no tracking is assigned.
- If ANY of the orders within the group are successfully shipped, we don't this group to appear the next time the query is run.
Hopefully I was clear.