I want to get data from a dynamic sequence of MS SQL tables (archive) of the form TiSe1 - TiSeN.
I can get the names of all TiSe tables through SQL from a common table. The number of tables increases over time, more recent measurement archives are written to new tables.
How can I connect a sequence of tables in a query?
I am thinking of repeating UNION SELECT, but I need to get the entire list of tables in the query first and substitute it into the queryString.
This is my current Query
SELECT
MIN(NULLIF(Value,0)) AS VAL_START,
MAX(Value) AS VAL_END,
MAX(NULLIF(Value,0)) - MIN(NULLIF(Value,0)) AS VAL_DIFF,
datepart(DD,SourceTime) AS DAYS,
datepart(HH,SourceTime)+1 AS HOURS
FROM dbo.TiSe3
WHERE SourceTime BETWEEN $P{DateFrom} AND $P{DateTo}
AND HDB_DpIdTs = $P{ID}
Group by datepart(HH,SourceTime), datepart(DD,SourceTime)
ORDER BY datepart(DD,SourceTime), datepart(HH,SourceTime)
