Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need a Query where with case statement Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 7:51 AM
Points: 9, Visits: 23

This Query is not working please help........

select * from tbl e
INNER JOIN LKUP L ON e.ID=L.LKUP_ID
WHERE
CASE WHEN ISNULL(DATEPART(YY,(SU_DT)),0)=2014 OR ISNULL(DATEPART(mm,(SU_DT)),0)>=11
THEN (LKUP_VAL_EN LIKE('V2%') OR LKUP_VAL_EN LIKE('V3%'))
ELSE LKUP_VAL_EN LIKE('V2%')
END
AND e.Active=1
Post #1491361
Posted Wednesday, September 4, 2013 9:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
Sometimes it's easier to work with the expression in an APPLY block, dropping the results down to the WHERE clause like this:
SELECT e.*, 
x.Datefilter,
x.LookupFilter
FROM tbl e
INNER JOIN LKUP L
ON e.ID = L.LKUP_ID
CROSS APPLY (
SELECT
Datefilter = CASE
WHEN YEAR(SU_DT) = 2014
OR MONTH(SU_DT) >= 11 THEN 1
ELSE 0 END,
LookupFilter = CASE
WHEN LKUP_VAL_EN LIKE 'V2%' THEN 1
WHEN LKUP_VAL_EN LIKE 'V3%' THEN 2
ELSE 0 END
) x
WHERE e.Active = 1
AND (
(x.Datefilter = 1 AND x.LookupFilter = 1)
OR (x.Datefilter = 0 AND x.LookupFilter = 2)
)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1491374
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse