where case when sql

multiple case when sql
sql case when multiple values
sql case statement in where clause multiple values
nested case statement in sql
case statement with multiple conditions in sql server
where case when sql example
sql case statement in where clause with parameters
sql else if

I have a sales table

I wish to do the following select the data date that is available if the top date SalePeriodFrom = 20181101

(if the top value SalePeriodFrom 01/11/2018 - first of the month data is available)

if not take the data from the day before (last day of the previous month 31/10/2018)

the code I have is

Select *
from Sales

Where case when SalePeriodFrom > DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) then   

  (SalePeriodFrom  <= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
  and (SalePeriodTo > '20010101' or
  SalePeriodTo is null))

   else (SalePeriodFrom <= DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
  and (SalePeriodTo > '20010101' or
  SalePeriodTo is null)) end 

I can't seem to get this to work, any ideas please team

This appears to replicate the boolean expressions you have inside your CASE expression, however, I can't check this without sample data:

SELECT {List of Columns} --You should probably list your columns here
FROM dbo.Sales --Assumed dbo schema
WHERE (SalePeriodFrom > DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
  AND  (SalePeriodTo > '20181101' OR SalePeriodTo IS NULL))
   OR (SalePeriodFrom <= DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
  AND (SalePeriodTo > '20010101' OR SalePeriodTo IS NULL));

Like mentioned into the comments on the question, this doesn't use a CASE expression at all. Using a CASE on your columns would cause the query to become non-SARGable, so (generally) they are best avoided.

Note I've taken out the clause SalePeriodFrom <= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0). This actually made no sense, as it looked like you were trying to check that the value of SalePeriodFrom was both greater than (>) and less than or equal to (<=) the expression DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0). It is impossible to something to be greater than something and less than or equal to it. For example 1 is greater than 0 but it is not equal or less than it. 0 is less than or equal to 0, but it is not greater than it.

SQL CASE Statement, The SQL CASE Statement​​ The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. The SQL CASE Statement. The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

As mentioned above, the first WHEN condition is always false so you may discard it.

SELECT * FROM Sales
WHERE SalePeriodFrom <= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) AND
      (SalePeriodFrom <= DATEADD(DAY, -(DAY(GETDATE())), GETDATE()) AND 
      (SalePeriodTo > '20010101' OR SalePeriodTo is null))

"CASE" statement within "WHERE" clause in SQL Server 2008 , First off, the CASE statement must be part of the expression, not the expression itself. In other words, you can have: WHERE co.DTEntered = CASE WHEN  In T-SQL, CASE is an expression that returns a single value from one of the branches. It is not a statement , and cannot be used for control of flow like it can in other languages. You cannot put the entire conditional inside.

Your question logic is really garbled and hard to make sense of. Read it back to yourself, or read it aloud to a co-worker and see if they get what you're on about

My understanding of your requirement is:

If the sales table contains any data from 01-Nov-2018, return all the data with a date of 01-Nov-2018, otherwise return any data with a date of 31-Oct-2018

select *
from sales
where SalePeriodFrom = 
    (SELECT MAX(saleperiodfrom) FROM sales WHERE saleperiodfrom IN ('2018-11-01', '2018-10-30'))

If there is data for both dates or only 1st Nov, the MAX() will return the 1st Nov date. If there is no data from 1st Nov but there is data from 30th Oct then the MAX will return the 30th Oct date. If there is no data for either date, the max will return nothing and you'll get no data

If I've misunderstood your requirement, please state it more clearly

——————————————————————————————-

Edit:

Or maybe you mean:

If the sales table contains any data with a date 01-Nov-2018, return all the data after midnight on 01-Nov-2018, otherwise return any data after midnight on 31-Oct-2018

select *
from sales
where SalePeriodFrom >= 
    (SELECT MAX(saleperiodfrom) FROM sales WHERE saleperiodfrom IN ('2018-11-01', '2018-10-30'))

How to Write Case Statement in WHERE Clause , In this example, I will only two columns and will demonstrate to you how you can write a dynamic SQL like query based on if the condition has  We could have spent countless hours to optimize their performance for dynamic SQL, but the better option would have been us the CASE expression in the WHERE clause. In this example, I will only two columns and will demonstrate to you how you can write a dynamic SQL like query based on if the condition has value or not.

CASE statement in SQL, The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By  -- Do the comparison, OR'd with a check on the @Country=0 case WHERE (a.Country = @Country OR @Country = 0) -- compare the Country field to itself WHERE a.Country = CASE WHEN @Country > 0 THEN @Country ELSE a.Country END Or, use a dynamically generated statement and only add in the Country condition if appropriate.

Querying data using the SQL Case statement, This article explains the usage of the SQL Case in Where clause with examples when to use the Case statement in SQL Server. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more SQL use CASE statement in WHERE IN clause

SQL CASE, This SQL tutorial for data analysis covers using if/then logic with the SQL CASE statement. CASE can include multiple conditions and be used with aggregate  For more information, see Data Type Precedence (Transact-SQL). Return Values. Simple CASE expression: The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned. Allows only an equality check.

Comments
  • You're trying to use a CASE expression as a statement. A CASE expression returns a scalar value (that needs to be compared to another expression in the WHERE to create a Boolean result). A CASE expression doesn't return a Boolean result on its own. For example CASE ColumnA WHEN 'A' THEN 1 ELSE 2 END = 2;
  • T-SQL does not have Boolean values. It has Boolean expressions, but these are only valid in particular contexts. And yes, this is annoying. You can have your CASE return a 1 or 0 and check on that, or split the query in two and use a UNION [ALL] to combine the results, or figure out universal expressions to compare SalePeriodFrom and SalePeriodTo to (I can't immediately see what those would be).
  • I can't figure out what you want to accomplish. Can you provide sample data and desired results?
  • It's generally better to use AND/OR constructions instead of case expressions in the WHERE clause.
  • this does not seem to work, I have tried similar to answer by serge below. There is data available in the dataset that SalePeriodFrom = 01/11/2018,
  • @pete then provide sample data and expected results, like was asked for please. Have you tried altering this example (based on guess work). Maybe it's as simple as using >= instead; we can't know without knowing your requirements.
  • I mention this is my answer, however, SalePeriodFrom > DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) AND SalePeriodFrom <= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) can never be true.
  • @Larnu yes, it's very strange logical expression obfuscated by CASE
  • hi this does not seem to work , there is data available in the dataset that SalePeriodFrom = 01/11/2018, it seems to have done the query for all records after the OR . any more ideas please
  • hi, this returned no rows, any more ideas please?
  • hi I tried the following where saleperiodFrom = (SELECT MAX(saleperiodFrom) FROM Sales WHERE saleperiodFrom IN ('2018-11-01', '2018-10-31') and (saleperiodTo> '20010101' or ActiveTo is null)) I get no records. to clarify I want the data to go from saleperiodFrom 01/11/2018, if the max value 01/11/2018 has not been entered into the data set yet then the saleperiodFrom need to go from 31/10/2018 - last day of the previous month. please help / more ideas.. do appreciate
  • Make the = into >= instead? You’re not making a lot of sense to be honest..