user2838738 user2838738 - 1 year ago 75
SQL Question

sql case statement in where clause , need to join check on different tables on different cases

SELECT nc.status
FROM newcustomer nc
INNER JOIN #salesexec UserFiltr
ON UserFiltr.userid = nc.reportedby
LEFT JOIN distributor d
ON nc.distributorid = d.distributorid
LEFT JOIN shop s
ON S.shopid = Nc.shopid
LEFT JOIN shoptype st
ON st.shoptypeid = s.shoptypeid
LEFT JOIN #orgshop Orgfiltr
ON Orgfiltr.organizationid = D.organizationid
WHERE CONVERT(VARCHAR, nc.mobiletransactiondate, 101) BETWEEN @FromDate AND @ToDate
CASE nc.status
WHEN 1 THEN (st.shoptype IS NULL
OR st.shoptype =''
OR st.shoptype<> 'DISTRIBUTOR' )
ELSE (nc.shoptype IS NULL
OR nc.shoptype =''
OR nc.shoptype<> 'DISTRIBUTOR' )
GROUP BY reportedby

Answer Source

You may looking for this

  1 = CASE WHEN nc.status = 1 AND (st.shoptype IS NULL OR st.shoptype ='' OR st.shoptype <> 'DISTRIBUTOR' ) 
           THEN 1
           WHEN (nc.shoptype IS NULL OR  nc.shoptype ='' OR nc.shoptype<> 'DISTRIBUTOR' )
           THEN 1 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download