Having a case or if statement in where clause on Max Date-CodePudding

The issue is that I need to select the MAX date when:

If the Max Date from my_column is equal to today's date, I need to select the Max Date from yesterday. If the Max Date from my_column is not equal to today's date, I can select the Max Date from today.

I am trying to do this using "case", but it is not working how I thought it would, sample code here is more like a sudo code to show the issue:

select account, name, street, due_date from my_table

where CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) IN (
-- if the date in the due_date column is not equal to today's date, I need to select today's -1 or yesterday's
case when MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) = CONVERT(VARCHAR,GETDATE() ,23)
-- otherwise it can just get the max today's date from the due_date column
 then ( SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) -1 ) FROM my_table
 else  MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) = CONVERT(VARCHAR,GETDATE() ,23)
)

)

and  account = '012345'

CodePudding user response：

This is what worked for me, but any rooms for improvement or suggestions is gratefully appreciated:

SELECT account, name, street, due_date
FROM my_table 
WHERE 
 CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) = (

  case when CONVERT(VARCHAR,due_date,102)  =  CONVERT(VARCHAR,GETDATE()  ,102)
   then (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) -1  ) FROM my_table )
   else (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) FROM my_table )
  end  

 )
 and account = '012345'

CodePudding user response：

I would suggest trying something like the following. It's not specified in your question but this assumes due_date is a date type:

select account, name, street, due_date 
from my_table t
where t.account='012345'
and due_date = DateAdd(
    day,
    case when Cast(GetDate() as date) 
      = (select Cast(Max(due_date) as date) from my_table) then -1 else 0
    end,
  Cast(GetDate() as date)
);