Query Help

Question

Query Help

bpowers

SSCertifiable

Points: 5483
More actions
December 10, 2009 at 10:38 am

#137626

Any help with the following would be greatly appreciated. Out of a particular table, Cust_Order_Line, I want to return all records where the ID is like F% OR S% and the Order dates on these are in the same month and year. Any idea how I could accomplish this?
Here is the script I want to modify:
SELECT CUST_ORDER_ID, PART_ID, DESIRED_SHIP_DATE
FROM CUST_ORDER_LINE
WHERE CUST_ORDER_ID LIKE 'F%' OR CUST_ORDER_ID LIKE 'S%'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bpowers SSCertifiable Points: 5483 More actions · Answer 2

Here is the data I want to retrieve. I want to find all records where there is a forecasted order (F-EMB FORECAST) and a firmed Sales Order (S31173) with the same Part ID in the same month and year (Desired Ship Date).

CUST_ORDER_ID PART_ID DESIRED_SHIP_DATE

--------------- ------------------------------ -----------------------

F-EMB FORECAST 1133710-5 2010-01-15 00:00:00.000

S31173 1133710-5 2010-01-15 00:00:00.000

(2 row(s) affected)

Does this help?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm guessing you didn't read the article.

Table definitions and sample data?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bpowers SSCertifiable Points: 5483 More actions · Answer 4

Table Definitions:

Cust_Order_ID (PK, FK, varchar(15), not null)

Part_Id (FK, varchar(30),null)

Desired_Ship_Date (datetime, null)

Sample Code (Used to return the sample data)

SELECT CUST_ORDER_ID, PART_ID, DESIRED_SHIP_DATE

FROM CUST_ORDER_LINE

WHERE CUST_ORDER_ID IN ('S31173', 'F-EMB FORECAST') AND PART_ID = PART_ID AND DESIRED_SHIP_DATE IN ('2010/1/15')

Sample Data

CUST_ORDER_ID PART_ID DESIRED_SHIP_DATE

--------------- ------------------------------ -----------------------

F-EMB FORECAST 1133710-5 2010-01-15 00:00:00.000

S31173 1133710-5 2010-01-15 00:00:00.000

(2 row(s) affected)