I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.
My table looks like this
orders ( order_id INT PRIMARY KEY , user_id INT , date_created DATE , order_value FLOAT , city_id INT )
I have used this code to get todays date in the table -
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today From orders
This works fine but when I try attempt a where clause below this
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today From orders where Today -1 = date_created;
I receive this error "Invalid column name 'Today'" So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?
Thanks in advance