Getting data from a dynamic sequence of SQL tables

0

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)

 

Attachments: 
andrey.esaulov's picture
Joined: Jun 10 2020 - 5:44am
Last seen: 3 months 9 hours ago

0 Answers:

No answers yet
Feedback