Show only lines where acct number is null

1

Below is the code and I've attached a image of the results. I need to modify the code to just show the lines where "Customer" = 'Our Part'. Thanks

SELECT TOP (100) PERCENT

dbo.WO_HDR.USER_DOC AS [W/O #],
LEFT(dbo.WO_EXPEDITE.PN_END_AS, 15) AS [Part Number], LEFT(dbo.WO_HDR.DESCRIPTN, 20) AS Description, ISNULL
((SELECT SUM(CAST(ISNULL(COMPLETED_DATE, GETDATE()) - START_DATE AS int)) AS Expr1
FROM dbo.WO_HDR_STATUS
WHERE (dbo.WO_HDR.WO_STATUS_DOC_NO = DOC_NO)), 0) AS Days,

LEFT(ISNULL
((SELECT TOP (1) dbo.Trim(NAME) AS Expr1
FROM dbo.CUSTVEND
WHERE (dbo.WO_HDR.ACCTNO = ACCTNO) AND (dbo.WO_HDR.SUBC = SUBC) AND (CUST_VEND IN ('C', 'B'))), 'Our Part'), 25) AS Customer,

LEFT(ISNULL
((SELECT TOP (1) DESCRIPTION
FROM dbo.WO_OPR
WHERE (dbo.WO_HDR.CURRENT_OPR_SEQ = OPR_SEQ) AND (dbo.WO_HDR.DOC_NO = PARENT_DOC_NO) AND
(dbo.WO_HDR.DOC_CATEGORY = PARENT_DOCCATEGORY)), ''), 15) AS Location,

ISNULL(CONVERT(char(10), dbo.WO_HDR.DUE_DATE, 101), '') AS [Due Date]

FROM dbo.WO_HDR INNER JOIN
dbo.WO_EXPEDITE ON dbo.WO_HDR.DOC_NO = dbo.WO_EXPEDITE.DOC_NO
WHERE (dbo.WO_HDR.DOC_STATUS = 4) AND (dbo.WO_HDR.WO_STATUS IN ('K020', 'K030', 'K032'))
ORDER BY dbo.WO_HDR.DUE_DATE

Pasted image694×152 6.83 KB

2

Add a where clause like shown below

SELECT * FROM
(
--- Your existing query, except for the final ORDER BY clause here
) AS S WHERE Customer = 'Our Part'	
ORDER BY [Due Date];

By the way, the TOP (100 ) PERCENT in your query does not add any value or functionality, so you can get rid of it.

3

Could something like this work? SELECT HDR.USER_DOC AS [W/O #] ,LEFT(EXPD.PN_END_AS, 15) AS [Part Number] ,LEFT(HDR.DESCRIPTN, 20) AS Description ,ISNULL(SUM(DATEADD(DAY, HS.START_DATE, ISNULL(HS.COMPLETED_DATE, GETDATE()))), 0) AS Days ,LEFT(ISNULL(LTrim(CV.NAME), 'Our Part'), 25) AS Customer ,LEFT(ISNULL(WO.DESCRIPTION, ''), 15) AS Location ,ISNULL(CONVERT(CHAR(10), HDR.DUE_DATE, 101), '') AS [Due Date] FROM dbo.WO_HDR HDR INNER JOIN dbo.WO_EXPEDITE EXPD ON HDR.DOC_NO = EXPD.DOC_NO LEFT JOIN HDR_STATUS HS ON HDR.WO_STATUS_DOC_NO = HS.DOC_NO LEFT JOIN dbo.CUSTVEND CV ON HDR.ACCTNO = CV.ACCTNO AND HDR.SUBC = CV.SUBC AND CV.CUST_VEND IN ('C','B') LEFT JOIN dbo.WO_OPR WO ON HDR.CURRENT_OPR_SEQ = WO.OPR_SEQ AND HDR.DOC_NO = WO.PARENT_DOC_NO AND HDR.DOC_CATEGORY = WO.PARENT_DOCCATEGORY WHERE HDR.DOC_STATUS = 4 AND HDR.WO_STATUS IN ('K020','K030','K032') AND CV.NAME IS NULL ORDER BY HDR.DUE_DATE;

4

Works perfectly. Thank you and I got rid of the Top 100 percent.

5

Not sure this looks right?

ISNULL(SUM(DATEADD(DAY, HS.START_DATE, ISNULL(HS.COMPLETED_DATE, GETDATE()))), 0)
     AS Days

Should perhaps be DateDIFF() instead of DateADD() ?

Probably just a pedantic point but:

,LEFT(ISNULL(LTrim(CV.NAME), 'Our Part'), 25) AS Customer
...
WHERE ...
AND CV.NAME IS NULL

is only ever going to give you "Our Part" :slight_smile:

6

Yep the DATADIFF is what I wanted. With do data to test and trying to do it quickly I pulled the wrong one out of the hat.:relaxed:

7

Its OK - the O/P says it worked perfectly ... :smiley: