Odtnhj

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.

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))

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'))