SQLTeam.com | Weblogs | Forums

SQL Query for list of Group Numbers


#1

Hi All,

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

Tablename: Shipments

Field Names:

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.

Many thanks,
SQLNewbee


#2
SELECT GroupNumber
FROM dbo.shipments
WHERE GroupNumber > ''
GROUP BY GroupNumber
HAVING MAX(CASE WHEN SUBSTRING(UserDefined1, PATINDEX('%[0-9][0-9][0-9][0-9]%', UserDefined1), 4) > 1000 THEN 1 ELSE 0 END) = 0

#3

Thank you kindly ScottPletcher. I'll give it a whirl.


#4

Received error:

[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the nvarchar value 'GB' to data type int.

This is due to text in the UserDefined1 field.

Like I had mentioned, this field could have text in it, it could be blank or null, and this is okay.

We just don't want the entire back if any one of the order have a number greater than 1,000 in the UserDefined1 field.

Thanks!


#5

oops, "entire batch" not "back"


#6

Yep, sorry. Need to adjust the HAVING:

HAVING MAX(CASE 
    WHEN PATINDEX('%[0-9][0-9][0-9][0-9]%', UserDefined1) = 0 THEN 0
    WHEN SUBSTRING(UserDefined1, PATINDEX('%[0-9][0-9][0-9][0-9]%', UserDefined1), 4) > 1000 THEN 1 ELSE 0 END
    ) = 0

#7

Yay... I'm actually getting results instead of the errors I was getting. I'll have to do some checking to make sure it's returning the desired results, but this is fantastic! Thank you ScottPletcher


#8

Everything checked out perfectly. You don't know how much I appreciate this. Thank you!!!


#9

You're welcome!!!