Laxminarayan Charan Laxminarayan Charan - 5 months ago 7
SQL Question

How to select 2nd column value on the basis of 1st column vale in select statement in SQL

As show in screenshot, i have one table corporate_calender containing all the working day (if holiday =0)and holiday (if holiday =1).

I want to select each working day date and last working day date
i.e i want to use output of 1st column in 2nd column
i tried below query, but it is giving error.
"Invalid column name 'workingday'."

Select Date workingday,
(select Max(Date)
from Corporate_Calendar
where holiday =0
and date>workingday)lastworkingday
from Corporate_Calendar
where holiday =0


enter image description here

Answer

Your problem is you create one alias and try to use on the inside query, and you cant do that

Also some fixs

  • Date is a reserverd word, so should be include it with [Date] or better yet use a different name
  • use alias to use the same table outiside and inside
    • CC1 and CC2

.

Select cc1.[Date] workingday,
      ( select Max(cc2.[Date])  
        from Corporate_Calendar  cc2
        where cc2.[holiday] = 0 
          and cc2.[date] > cc1.[Date]
                                ^^^^ cant use the alias
      ) as lastworkingday 
from Corporate_Calendar cc1
where cc1.[holiday] = 0

if you want use the alias you need wrap it as a sub query.

 SELECT *
 FROM (SELECT cc1.[Date] as workingDay
       FROM Corporate_Calendar as cc1
      ) T
 WHERE T.workingDay > 0