Problems using multiple where conditions to run specific criteria

Question

I am attempting to create a query that will allow me to run several table of information through a series of criteria however I can not seem to get this to work when it comes to using multiple WHERE clauses of the same type.

use leads;

select s.knum as Knum
    , s.customer_id as Cust_#
    , s.[full down] as FD_Date
    , s.season, s.inv as Room_Type
    , s.Owner_type
    , s.status
    , s.trw1
    , s.trw2
    , s.trw21
    , s.state
    , m.Knum as MagnaK
    , c.[pbs id] as MF_ID
    , c.[pbs disp] as MF_disp
    , c.[pbs freq] as Freq
    , c.[PBS DAYS DELQ] as MF_del
    , c.[PBS TOTAL DUE] as MF_Due
    , c.[LN ID]
    , c.[LN DISP]
    , c.[LN CURR BAL]
    , c.[LN NPDD]
    , st.customer_id as ST_custID
    , p.[LOAN #] as P_Knum
    , p.pmts_made as Paymnts_made
    , p.int
    , p.d30
    , p.d60
    , p.d90
    , d.knum as DVN_Knum
    , (
            select max(HighTrw) 
            from (values (trw1), (trw2), (trw21)) as Value(HighTrw)
        ) as [high_trw]
from dbo.spi s
LEFT JOIN dbo.magna m on s.knum = m.knum
LEFT JOIN dbo.book7 C on s.knum = C.K
LEFT JOIN dbo.spi_tours ST on s.customer_id = st.customer_id 
LEFT JOIN dbo.payments P on s.knum = P.[LOAN #]
LEFT JOIN dbo.dvn D on s.knum = d.knum
Where m.knum = null 
    and d.knum = null 
    and st.customer_id = null 
    or 
    (
        select max(HighTrw)
        from (values (trw1), (trw2), (trw21)) As updatedate (HighTrw)
    ) between 600 and 625 
    and p.current_bal >= '15000' 
    and p.CURRENT_Bal >= '7500' 
    or 
    (
        select max(HighTrw)
        from (values (trw1), (trw2), (trw21)) As updatedate (HighTrw)
    ) between 575 and 599 
    and p.current_bal >= '7499' 
    and p.CURRENT_Bal >= '5000';

I would like to be able to use the 6 tables linked in the code along with specific criteria to come up with a final list based on that criteria.

Answer 1

My best guess at your predicates is something like this.

Where m.knum IS null 
    and d.knum IS null 
    and st.customer_id IS null 
    AND
    (
        (
            select max(HighTrw)
            from (values (trw1), (trw2), (trw21)) As updatedate (HighTrw)
        ) between 600 and 625 
        and p.current_bal >= '15000' 
        and p.CURRENT_Bal >= '7500' 
    )
    OR 
    (
        (
            select max(HighTrw)
            from (values (trw1), (trw2), (trw21)) As updatedate (HighTrw)
        ) between 575 and 599 
        and p.current_bal >= '7499' 
        and p.CURRENT_Bal >= '5000'
    )

But that could be greatly simplified using CROSS APPLY to your values instead of doing it over and over.

Answer 2

There are several typos in your query. I've tried to format and simplify it to be able to correct it.

SELECT s.knum as Knum, 
    s.customer_id as Cust_#,
    s.[full down] as FD_Date, 
    s.season, 
    s.inv as Room_Type, 
    s.Owner_type, 
    s.status, 
    s.trw1, 
    s.trw2, 
    s.trw21, 
    s.state, 
    m.Knum as MagnaK,
    c.[pbs id] as MF_ID, 
    c.[pbs disp] as MF_disp, 
    c.[pbs freq] as Freq, 
    c.[PBS DAYS DELQ] as MF_del, 
    c.[PBS TOTAL DUE] as MF_Due, 
    c.[LN ID], 
    c.[LN DISP], 
    c.[LN CURR BAL], 
    c.[LN NPDD], 
    st.customer_id as ST_custID, 
    p.[LOAN #] as P_Knum, 
    p.pmts_made as Paymnts_made, 
    p.int, 
    p.d30, 
    p.d60, 
    p.d90, 
    d.knum as DVN_Knum, 
    v.HighTrw as [high_trw]
FROM      dbo.spi       s
LEFT JOIN dbo.magna     m  on s.knum = m.knum
LEFT JOIN dbo.book7     C  on s.knum = C.K
LEFT JOIN dbo.spi_tours ST on s.customer_id = st.customer_id 
LEFT JOIN dbo.payments  P  on s.knum = P.[LOAN #]
LEFT JOIN dbo.dvn       D  on s.knum = d.knum
CROSS APPLY (select max(HighTrw) 
            from (values (s.trw1), (s.trw2), (s.trw21))val(trw)) as v(HighTrw)
Where m.knum        IS NULL 
and d.knum          IS NULL 
and st.customer_id  IS NULL 
or (v.HighTrw between 600 and 625 and p.current_bal BETWEEN '7500' AND '15000')
or (v.HighTrw between 575 and 599 and p.current_bal BETWEEN '5000' AND '7499');