SQL Server, Efficient way to query multiple tables for a specific day of data

We have started saving the daily change data for a bunch of tables. We first put in all of the original data and than the change data with start and end dates for each table record (So not all records are saved each day just changed records). So to pull the data for a specific date I have to look at the beginning and end dates (end date might be null) and pull the MAX begin date to get the right records.

SELECT *
FROM dbo.DIM_EX_NAME_MASTER AS DEXNM
    INNER JOIN 
     (SELECT APPID, MAX(DW_RECORD_START) AS StartDate
      FROM dbo.DIM_EX_NAME_MASTER
      WHERE (DW_RECORD_START < '4/4/2020') AND (DW_RECORD_END > '4/4/2020') 
        OR (DW_RECORD_START < '4/4/2020') AND (DW_RECORD_END IS NULL)
      GROUP BY APPID) AS INNER_DEXNM ON DEXNM.APPID = INNER_DEXNM.APPID AND DEXNM.DW_RECORD_START = INNER_DEXNM.StartDate

So that’s not so bad for one table but we want to build a report with a query that pulls from 25 tables with subqueries where the user selects the date to pull for.

That’s going to be some really messy sql. Ideally I would like to create a view for each table and pass in the date as a parameter but SQL server doesn’t allow for parameterized views.

Anyone have any ideas on how I can build multi-table date based queries without adding all of this extra sql per table?

Thanks for any help you can give!

Answer

SQL server doesn’t allow for parameterized views.

No, but SQL Server does support user-defined table-valued functions.

This is pretty much exactly what you are asking for — they can accept a date parameter and return the results as a table.

Advertisement

Answer