Add a filter to timeseries pairs-CodePudding

I have a time series plc tag data as below with values in '1' or '0'

| id | tagname                     | value | datetime                |
|----|-----------------------------|-------|-------------------------|
| 28 | RH.ONCHANGE.PROCESS_CYCLE_4 | 1     | 28-09-2021 17:43:38.767 |
| 29 | RH.ONCHANGE.CYCLE_COMPLETE  | 0     | 28-09-2021 17:44:39.637 |
| 30 | RH.ONCHANGE.PROCESS_CYCLE_4 | 0     | 28-09-2021 17:44:01.723 |
| 31 | RH.ONCHANGE.PROCESS_CYCLE_5 | 1     | 28-09-2021 17:44:02.070 |
| 32 | RH.ONCHANGE.CYCLE_COMPLETE  | 1     | 28-09-2021 17:44:07.637 |
| 33 | RH.ONCHANGE.PROCESS_CYCLE_5 | 0     | 28-09-2021 17:44:09.637 |
| 34 | RH.ONCHANGE.CYCLE_COMPLETE  | 0     | 28-09-2021 17:44:35.513 |
| 35 | RH.ONCHANGE.ALL_GUN_HOME    | 1     | 28-09-2021 17:44:38.890 |
| 36 | RH.ONCHANGE.PROCESS_CYCLE_1 | 1     | 28-09-2021 17:44:38.917 |
| 37 | RH.ONCHANGE.ALL_GUN_HOME    | 1     | 28-09-2021 17:44:39.110 |
| 38 | RH.ONCHANGE.ALL_GUN_HOME    | 0     | 28-09-2021 17:46:00.087 |
| 39 | RH.ONCHANGE.PROCESS_CYCLE_1 | 0     | 28-09-2021 17:46:01.710 |

Using below code I can get the output

select tOn.tagname, tOn.datetime StartTime, tOff.datetime EndTime
from (
select tagname, datetime, 
      ROW_NUMBER() Over(Partition by tagname order by datetime desc) EventID
from [a2_al].[dbo].[rbc] where value = 1
) tOn
LEFT JOIN (
select tagname, datetime, 
      ROW_NUMBER() Over(Partition by tagname order by datetime desc) EventID
from [a2_al].[dbo].[rbc] where value = 0
) tOff
on (tOn.tagname = tOff.tagname and tOn.EventID = tOff.EventID)

Output

| id | tagname                     | StartTime               | EndTime                 |
|----|-----------------------------|-------------------------|-------------------------|
| 28 | RH.ONCHANGE.PROCESS_CYCLE_4 | 28-09-2021 17:43:38.767 | 28-09-2021 17:44:01.723 |
| 31 | RH.ONCHANGE.CYCLE_COMPLETE  | 28-09-2021 17:44:39.637 | 28-09-2021 17:44:07.637 |
| 30 | RH.ONCHANGE.PROCESS_CYCLE_5 | 28-09-2021 17:44:02.070 | 28-09-2021 17:44:09.637 |
| 33 | RH.ONCHANGE.CYCLE_COMPLETE  | 28-09-2021 17:44:35.513 |                         |
| 35 | RH.ONCHANGE.ALL_GUN_HOME    | 28-09-2021 17:44:38.890 | 28-09-2021 17:44:39.10  |
| 34 | RH.ONCHANGE.PROCESS_CYCLE_1 | 28-09-2021 17:44:38.917 | 28-09-2021 17:46:01.710 |
| 36 | RH.ONCHANGE.ALL_GUN_HOME    | 28-09-2021 17:46:00.087 |                         |

But my requirement is, I only want to query going back 25 hours, so I add a predicate:

where datetime >= DATEADD(Hour, -25, GETDATE())

But this returns the error:

Msg 209 Level 16 State 1
Ambiguous column name 'datetime'.

CodePudding user response：

You have two subqueries that both contain a column named datetime. When you say:

where datetime >= DATEADD(Hour, -25, GETDATE())

You need to tell SQL Server which one you mean. So either:

where tOn.datetime >= DATEADD(Hour, -25, GETDATE())

... or ...

where tOff.datetime >= DATEADD(Hour, -25, GETDATE())

I'm going to guess it should be tOn.