user2838738 user2838738 - 27 days ago 9
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
AND
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' )
END
GROUP BY reportedby

Answer

You may looking for this

AND 
  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 
      END 
Comments