Hi I’m hoping someone can help me with a time series query using MSSQL as the data source.
I want to have a time-series graph that will display the total number of jobs that are open vs number closed from the point in time A to point in time B
the data is structured like this:
Column A: Job ID
Column B: Date and time open
Column C: Date and time closed
Column D: Status (Open / Closed)
Any help appreciated! thanks
I can’t help feeling I’m misunderstanding the requirement, because the
solution seems rather simple to me:
select count(status) from datatable where status=‘Open’ and $timeFilter
select count(status) from datatable where status=‘Closed’ and $timeFilter
what am I missing?
Antony.
Sorry, yes the query is easy but for the time series query it requies it to be formatted like this:
SELECT
$__timeEpoch(<time_column>),
as value,
as metric
FROM
WHERE $__timeFilter(time_column)
ORDER BY
<time_column> ASC
However when i put it into Grafana as:
SELECT
__timeEpoch(datetimeopen),
Count(jobID) as value,
Status as metric
FROM jobs
WHERE __timeFilter(datetimeopen)
ORDER BY
datetimeopen ASC
Is errors with:
ssql: Column ‘Jobs.Dateentered’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.