I have a table
DATATABLE that contains a timeseries that looks something like this:
2018-01-11 09:00:00.000 2018-01-11 09:00:00.000 2018-01-11 09:45:00.000 2018-01-11 09:45:00.000 2018-01-11 10:00:00.000 2018-01-11 10:00:00.000 2018-01-11 10:15:00.000 2018-01-11 10:15:00.000
So if I want to extract the timeseries, I would query:
SELECT * FROM DATATABLE
Now what I want, is to be able to extract part of the timeseries that satisfies specific criteria, which changes each time. The criteria are stored as ranges of values, and so far I have created a procedure that extracts those criteria. One sample criterion would be that the time part of the timeseries should have a value that is contained int he following:
00:00:00 00:30:00 01:00:00 01:30:00 02:00:00 02:30:00 03:00:00 03:30:00 04:00:00 04:30:00 05:00:00 05:30:00 06:00:00 06:30:00 07:00:00 07:30:00 08:00:00 08:30:00 09:00:00 09:30:00 10:00:00 10:30:00 11:00:00 11:30:00 12:00:00 12:30:00
In other words, the values
2018-01-11 09:45:00.000 and
2018-01-11 10:15:00.000 should not be part of my final result. The final result would therefore look like:
2018-01-11 09:00:00.000 2018-01-11 09:00:00.000 2018-01-11 10:00:00.000 2018-01-11 10:00:00.000
The criterion that I want to set, in English would be:
''Select all the values from my timeseries that have a time part that is (exactly) contained in the series of times I provide you.''
What I wrote so far is:
SELECT * FROM DATATABLE WHERE DATEPART(MINUTE,[DATATABLE].[ISSUETIMES])=(SELECT IssueTimes FROM @IssueTimes)
@IssueTimes is a temporary table variable where I store the range of values I want to use as criteria and
[DATATABLE].[ISSUETIMES] is the column that contains the timeseries that I want to filter.
The error I get is the following:
Operand type clash: time is incompatible with int
So i get that this probably means that am trying to compare times with int, but i do not know how to proceed as I am kind of new to SQL.
I hope my formulation was good enough to be understandable :)