0

I have written the below query in SQL to get the result based on the days and hours. The days,start and endtime are stored in a table 'tblHeader' and the days are stored in 7 columns(Monday, Tuesday, Wendesday etc) and the start_time and end_time in another 2 columns in the same table. The table structure will be like

Days and Time

The days and hours values is dynamic. So when I run the below query I get results of all days, but I need only the day which is 'Y'.

Eg: If I run the query on Monday, then I need to get only the Monday data based on the dynamic time condition.

My Query is

select 
-----
------
(CASE
                             
WHEN
(SELECT DATENAME(dw, getdate())) LIKE 'Monday' THEN PriceH_strMonday WHEN

(SELECT DATENAME(dw, getdate())) LIKE 'Tuesday' THEN PriceH_strTuesday WHEN

(SELECT DATENAME(dw, getdate())) LIKE 'Wednesday' THEN PriceH_strWednesday WHEN

(SELECT DATENAME(dw, getdate())) LIKE 'Thursday' THEN PriceH_strThursday WHEN

(SELECT DATENAME(dw, getdate())) LIKE 'Friday' THEN PriceH_strFriday WHEN

(SELECT DATENAME(dw, getdate())) LIKE 'Saturday' THEN PriceH_strSaturday WHEN

(SELECT DATENAME(dw, getdate())) LIKE 'Sunday' THEN PriceH_strSunday END) as today

from tblPrice a 
inner join

where   
Today = 'Y'
and c.StartTime >= @mystarttime or c.EndTime <= @mystarttime

Now the Case statement result was displayed as a column named 'Today', when I include that column in the where condition, I faced an error stating Invalid column name 'Today'. How should I solve this?

Any help? Thanks in Anticipation.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Keerthi
  • 1
  • 2
  • 2
    FYI its a case *expression* - not a statement – Dale K Oct 23 '20 at 05:48
  • You can't; you either need to replicate the expression in the where clause or use one of the other approaches described in the duplicates. – Nick Oct 23 '20 at 05:52
  • You cannot use an expression from a SELECT in a WHERE of the same query, because the WHERE is calculated before the SELECT. SELECT is typically the penultimate thing that is done in processing an SQL: FROM WHERE GROUPBY SELECT ORDERBY. If you want a SELECTed thing in a WHERE, you have to wrap the whole thing in a subquery or use a WITH if your db supports it: `SELECT * FROM (SELECT 1 as x) y WHERE x = 1` Or `WITH y AS(SELECT 1 as x) SELECT x FROM y` – Caius Jard Oct 23 '20 at 05:53
  • 1
    Note you don't need to use `(SELECT DATENAME(dw, getdate()))` you can just use `DATENAME(dw, getdate())`, and in fact it would be better to write `CASE DATENAME(dw, getdate()) WHEN 'Monday' THEN ... WHEN 'Tuesday' THEN ...` etc. – Nick Oct 23 '20 at 05:53

0 Answers0