CODEWITHSUNDEEP
sql-server
mkb
mkb

Reputation: 35

SQL Server - Select from a variable amount of tables depending on date range

I have a query that joins multiple Data Sources together, I need a query that will select from a variable amount of tables depending on the date range I send it.

Joining Query

SELECT I.SerialNumber as DataSource,Deployed,Removed
  FROM InstrumentDeployments ID
  INNER JOIN Instruments I On I.Id = ID.InstrumentId
  INNER JOIN Points P On P.Id = ID.PointId
  WHERE P.Id = 1
  ORDER BY Deployed

Joining Query Result

JoingingTable

So from the above query result, if I wanted to select all of the historical information, it would go through and get the data from the specific tables (called DataSource in query above) dependant on the relevant date.

Final Query - Something like this but the variable tables from query result above.

SELECT * FROM (VariableTables) WHERE DateRange BETWEEN '2016-09-07' and '2018-07-28'

Thanks

Upvotes: 0

Views: 54

Answers (1)

Thom A
Thom A

Reputation: 95759

Please note that this is completely untested as the sample data is an image (and I can't copy and paste text from an image). If this doesn't work, please provide your sample data as text.

Anyway, the only way you'll be able to achieve this is with Dynamic SQL. This also, like in the comments, assumes that every table has the exact same definition. if it doesn't you'll likely get a failure (perhaps a conversion error, or that for a UNION query all tables must have the same number of columns). If they don't, you'll need to explicitly define your columns.

Again, this is untested, however:

DECLARE @SQL nvarchar(MAX);

SET @SQL =  STUFF((SELECT NCHAR(10) + N'UNION ALL' + NCHAR(10) +
                          N'SELECT *' + NCHAR(10) +
                          N'FROM ' + QUOTENAME(I.SerialNumber) + NCHAR(10) +
                          N'WHERE DateRange BETWEEN @dStart AND @dEnd'
                   FROM InstrumentDeployments ID
                        INNER JOIN Instruments I ON I.Id = ID.InstrumentId
                        INNER JOIN Points P ON P.Id = ID.PointId
                   WHERE P.Id = 1
                   ORDER BY Deployed
                   FOR XML PATH(N'')),1,11,N'') + N';';
PRINT @SQL; --Your best friend.
DECLARE @Start date, @End date;
SET @Start = '20160907';
SET @End = '20180728';

EXEC sp_executesql @SQL, N'@dStart date, @dEnd date', @dStart = @Start, @dEnd = @End;

Upvotes: 2

Related Questions