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’.
Advertisement
Answer
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
.