Isnull, then script

Transact-SQL
1

Here is my current script

select DATENAME(month,ih.shipped_date) as [Month]
,wh.CUST_PO
,wh.DOC_NO as [WO_No]
,wh.ACCTNO
,cv.name
,wh.PARTNUMBER
,wh.SERIAL_NO
,wh.DESCRIPTN
,cast(wh.ADDED_DTE as DATE) as [RCV_Date]
,isnull(convert(varchar(11),wh.PRICED_DATE,101),'') as [Insp/Quote_Date]
,isnull(convert(varchar(11),wh.PRINTED_DATE,101),'') as [Rel_Date]
,cast(wh.POST_STK_DATE as DATE) as [Into_Stock]
,cast(ih.SHIPPED_DATE as DATE) as [Ship_Date]
,cast(ih.SHIPPED_DATE-wh.ADDED_DTE as integer) as [Total TAT]
,isnull(CAST(wh.PRINTED_DATE-wh.PRICED_DATE as integer),'0') as [Quote Hold]
,isnull(b.Days,'0') as [Total C Status]
,CAST(ih.SHIPPED_DATE-wh.POST_STK_DATE as integer) as [Final Hold]
, '' as [Holidays]
,cast((ih.SHIPPED_DATE-wh.ADDED_DTE)- (wh.PRINTED_DATE-wh.PRICED_DATE)- (ih.SHIPPED_DATE-wh.POST_STK_DATE) as integer) as [KAI TAT]
,case when cast((ih.SHIPPED_DATE-wh.ADDED_DTE)- (wh.PRINTED_DATE-wh.PRICED_DATE)- (ih.SHIPPED_DATE-wh.POST_STK_DATE) as integer) > 14 then 'Late' else 'On Time' end as [OTD (14 days]
,cast((ih.SHIPPED_DATE-wh.ADDED_DTE)- (wh.PRINTED_DATE-wh.PRICED_DATE)- (ih.SHIPPED_DATE-wh.POST_STK_DATE) as integer) as [Adjusted TAT]
, '' as [Reason for Adjustment]
,case when (ih.SHIPPED_DATE-wh.ADDED_DTE)- (wh.PRINTED_DATE-wh.PRICED_DATE)- (ih.SHIPPED_DATE-wh.POST_STK_DATE) >14 then 'Late' else 'On Time' end as [Adjusted OTD (14 days]
,'1' as [Counter]

from WO_HDR wh
join CUSTVEND cv on (wh.ACCTNO = cv.ACCTNO)
join INV_HDR ih on (wh.DOC_NO = ih.PARENT_DOC_NO)
left outer join (
select rtrim(ws1.PARENT_DOC_NO) as [WO#]
, sum(datediff(hour,ws1.START_DATE,ws1.COMPLETED_DATE))/24 as [Days]
, cast(ih1.SHIPPED_DATE as DATE) as [Shipped Date]

  			from		  WO_HDR_STATUS ws1
  					 join INV_HDR ih1 on (ws1.PARENT_DOC_NO = ih1.PARENT_DOC_NO)

  			where		  ws1.WO_STATUS like 'C%'
  						  and ih1.SHIPPED_DATE between

        dateadd(M, datediff(M, 0, getdate())-1,0) and dateadd(M, datediff(M, -1, getdate())-1, -1)

  			group by	  ws1.PARENT_DOC_NO, ih1.SHIPPED_DATE) as b on (wh.DOC_NO = b.WO#)

where ih.SHIPPED_DATE between

        dateadd(M, datediff(M, 0, getdate())-1,0) and dateadd(M, datediff(M, -1, getdate())-1, -1)
  and wh.ACCTNO in ('10126','10129','10645','12737')

In the 10th line down,

,isnull(convert(varchar(11),wh.PRICED_DATE,101),'') as [Insp/Quote_Date]

I want to replace '' with

select wo_doc_no, max(WORK_DATE)
from WO_LABOR
where DESCRIPTION = 'Inspect'
group by WO_DOC_NO
order by WO_DOC_NO

so basically, if wh.priced_date is null, I want to return the max work_date where the description is 'inspect' for that WO_No.

Thanks for your help.

2

just do the join with your subquery --
select wo_doc_no, max(WORK_DATE) MaxWrkDate
from WO_LABOR
where DESCRIPTION = 'Inspect'
group by WO_DOC_NO and join that with WO_HDR.wo_doc

and in line 10 call that column using COALESCE function, so if the first expression PRICED_DATE is null, then call that MaxWrkDate from your subquery

3

something like this--not testing

before your where clause add --

    LEFT JOIN ( SELECT  wo_doc_no ,
                        MAX(WORK_DATE) MaxWork_Date
                FROM    WO_LABOR
                WHERE   DESCRIPTION = 'Inspect'
                GROUP BY WO_DOC_NO
              ) MaxWrkDate ON MaxWrkDate.wo_doc_no = wh.wo_doc_no

WHERE ih.SHIPPED_DATE BETWEEN DATEADD(M, DATEDIFF(M, 0, GETDATE()) - 1, 0)
AND DATEADD(M, DATEDIFF(M, -1, GETDATE()) - 1, -1)
AND wh.ACCTNO IN ( '10126', '10129', '10645', '12737' )

and on line 10--
CONVERT(VARCHAR(11), COALESCE(wh.PRICED_DATE, MaxWrkDate), 101) AS [Insp/Quote_Date] ,

4

I would have added (after the FROM / JOINS)

OUTER APPLY
(
    SELECT MAX(WORK_DATE) AS MaxWork_Date
    FROM WO_LABOR AS L
    WHERE DESCRIPTION = 'Inspect'
          AND L.wo_doc_no = wh.wo_doc_no
          AND wh.PRICED_DATE IS NULL    -- This might make the query more efficient
) AS L

and in the SELECT

,convert(varchar(11), isnull(wh.PRICED_DATE, L.MaxWork_Date),101) as [Insp/Quote_Date]
5

Thank you all for the response. It is working perfectly now.

What if, I want three criteria like:
first give me printed date, if thats null
give me priced date, if thats null
give me start date

6

Look up the COALESCE function. It's a standard and you don't have to nest ISNULL functions.

COALESCE(printed_date, priced_date, start_date)

https://msdn.microsoft.com/en-us/library/ms190349.aspx

7

Perfect, that works great.