How do I exclude NULL values in my query not based on column name

Question

The issue I am having is the columns I want to exclude are not located in a database column. Instead the null values are showing based on a calculation we made. We want to exclude the NULL values and everything I try is not working. I tried HAVING and that didn't work.

Here is the query - I want to exclude Null values in the PreviousCurrent column.

SELECT 
    [WAAPID] AS Bus_Number,
    FWCPLM *-.01 AS Completed_Miles,
    (FWCPLM *.01) - LAG ((FWCPLM *.01)) OVER (PARTITION BY LTRIM(RTRIM([WAAPID])) ORDER BY FWCPLM, PM_Completed_date.standarddate) AS 'PreviousCurrent', --Gets the previous row field and puts it up on the next row.      
    -- ,FWLSTM *-.01 as Last_completed_Miles
    CONVERT(DATE, PM_Completed_Date.StandardDate) AS Completed_Date,
    [WAMCU],
    [WAWR01],
    CASE 
        WHEN WAWR01 = '4004' THEN 'A INSP' 
        WHEN WAWR01 = '4003' THEN 'B INSP' 
        WHEN WAWR01 = '4002' THEN 'C INSP' 
        WHEN WAWR01 = '4001' THEN 'D INSP' 
        ELSE 'Active' 
    END AS 'PM TYPE'
FROM  
    [JDE_PRODUCTION].[PRODDTA].[F4801] WO
INNER JOIN 
    PRODDTA.F1207 PM ON PM.FWWONA = WO.WADOCO
CROSS APPLY
    dbo.tvf_JulianDateToStandardDate (PM.FWCPLD) PM_Completed_Date
CROSS APPLY
    dbo.tvf_JulianDateToStandardDate (PM.FWLCPD) PM_Last_CompletedDate
WHERE 
    WAWR01 IN ('4004')
               --,'4001'
               --,'4002'
               --,'4003') 
    AND WATYPS = '6'
    AND PM_Completed_Date.StandardDate >= '01/01/2019'
    AND WASRST <> '98'
ORDER BY
    WAAPID, FWCPLM ASC, WAMCU

enter image description here

I tried Having with the column name PreviousCurrent and that did not work.

I want Null values to not show up.

You would need to turn your query to a subquery, then filter in the outer query. — GMB, Mar 17 '23 at 16:47
Have you tried using your query as a derived table and then applying a `where` criteria to remove the `null` rows? — Stu, Mar 17 '23 at 16:49
(You would need to turn your query to a subquery, then filter in the outer query.) This worked — Taylor, Mar 17 '23 at 17:58

JonWay · Accepted Answer · 2023-03-17T17:08:34.137

I don't think you can use window function in where clause in sql server, However to achieve what you want you can use subquery, cte, temp table etc like below

SELECT
*
FROM (
SELECT 

        
       [WAAPID] as Bus_Number
      ,FWCPLM *-.01 as Completed_Miles
          ,(FWCPLM *.01)-LAG ((FWCPLM *.01)) OVER (PARTITION BY LTRIM(RTRIM([WAAPID])) ORDER BY FWCPLM, PM_Completed_date.standarddate) AS 'PreviousCurrent' --Gets the previous row field and puts it up on the next row.      -- ,FWLSTM *-.01 as Last_completed_Miles
      ,CONVERT(DATE, PM_Completed_Date.StandardDate) as Completed_Date
          ,[WAMCU]
          ,[WAWR01]
       ,CASE 
            WHEN WAWR01 = '4004' THEN 'A INSP' 
            WHEN WAWR01 = '4003' THEN 'B INSP' 
            WHEN WAWR01 = '4002' THEN 'C INSP' 
            WHEN WAWR01 = '4001' THEN 'D INSP' 
               ELSE 'Active' 
      END as 'PM TYPE'

  FROM [JDE_PRODUCTION].[PRODDTA].[F4801] WO
  INNER JOIN PRODDTA.F1207 PM on  PM.FWWONA=WO.WADOCO

  Cross Apply dbo.tvf_JulianDateToStandardDate (PM.FWCPLD) PM_Completed_Date
  Cross Apply dbo.tvf_JulianDateToStandardDate (PM.FWLCPD) PM_Last_CompletedDate

  WHERE 
    WAWR01 IN ('4004')
               --,'4001'
               --,'4002'
               --,'4003') 
    AND WATYPS='6'
    AND PM_Completed_Date.StandardDate>='01/01/2019'
    AND WASRST <>'98'

 ORDER BY
    WAAPID,
    FWCPLM ASC,
    WAMCU
    ) pp
    where PreviousCurrent is not null

The subquery worked so thank you. I had to take the Order by clause out of the sub query and put it on the outside. But with that last tweak it worked. Thank you for much — Taylor, Mar 17 '23 at 17:39

How do I exclude NULL values in my query not based on column name

1 Answers1