Seeking help fixing a small sql query

I am needing a query to return a list of time entries from the last 21 days, sort that list by member id and then by start time, then compare the start time in each row against the end time of the previous row, then display the rows where the start time is larger than the previous rows end time.

Unfortunately I am not very knowledgeable in all things sql and the query I have so far returns an error and I have no idea what I need to change/add/remove to fix it.

Here is an example of the view I am starting with (imagine this with a few thousand more rows). starting view

When I run the following query I get the data arranged how I want.

SELECT
t.time_recID AS id
,t.time_recID AS time_recid
,LOWER(t.Member_ID) AS member_id
,CAST(t.Time_Start_UTC AS DATETIME) AS evt_start
,CAST(t.Time_End_UTC AS DATETIME) AS evt_end
,LAG(t.Time_End_UTC,1,0) OVER (PARTITION BY t.Member_ID ORDER BY t.Time_Start_UTC) AS prev_evtend
FROM v_evt_time AS t
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
ORDER BY member_id, evt_start
OFFSET 0 ROWS

query results getting close

But when I add a line in the WHERE clause to compare the evt_start and prev_evtend columns like this…

WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
AND evt_start < prev_evtend

I get an error that says, Invalid column name ‘evt_start’. Invalid column name ‘prev_evtstart’.

What do I need to do to fix the query to return the results I need? Any help is greatly appreciated, and thanks very much in advance.

Answer

The column names evt_start and prev_evtend are not available inside the select statement in which they were defined.

You can use a Common Table Expression as follows:

With entries as (
SELECT
t.time_recID AS id
,t.time_recID AS time_recid
,LOWER(t.Member_ID) AS member_id
,CAST(t.Time_Start_UTC AS DATETIME) AS evt_start
,CAST(t.Time_End_UTC AS DATETIME) AS evt_end
,LAG(t.Time_End_UTC,1,0) OVER (PARTITION BY t.Member_ID ORDER BY t.Time_Start_UTC) AS prev_evtend
FROM v_evt_time AS t
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
ORDER BY member_id, evt_start
OFFSET 0 ROWS)
select * 
from entries
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
AND evt_start < prev_evtend

which is functionally equivalent to a query on top of your original one:

select *
from (
SELECT
t.time_recID AS id
,t.time_recID AS time_recid
,LOWER(t.Member_ID) AS member_id
,CAST(t.Time_Start_UTC AS DATETIME) AS evt_start
,CAST(t.Time_End_UTC AS DATETIME) AS evt_end
,LAG(t.Time_End_UTC,1,0) OVER (PARTITION BY t.Member_ID ORDER BY t.Time_Start_UTC) AS prev_evtend
FROM v_evt_time AS t
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
ORDER BY member_id, evt_start
OFFSET 0 ROWS) sub
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
AND evt_start < prev_evtend