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

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
– GMB, Commented Mar 17, 2023 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
– Stu, Commented Mar 17, 2023 at 16:49
(You would need to turn your query to a subquery, then filter in the outer query.) This worked — Taylor
– Taylor, Commented Mar 17, 2023 at 17:58

Dale K · Accepted Answer · 2024-01-18 19:50:09Z

1

I don't think you can use window functions in the 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

edited Jan 18, 2024 at 19:50

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Mar 17, 2023 at 16:49

JonWay

1,7471 gold badge21 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taylor Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related