SQL View to select data dynamically

Question

Hello

I have a requirement to create a SQL view that dynamically selects data from one of two tables, T1 and T2, based on the current date.

Here's the requirement: When the current date is between the 21st of this month and the 7th working day ( working day excludes Saturdays and Sundays ) of the following month , the view should pull data from Table T1. For all other dates (i.e., from the 8th working day to the 20th of the current month), the view should use data from Table T2.

Can some help / guide me with this ?

Answer 1

First, you need a calendar table, so that you easily can look up which is the 7th workday and so on. (I assume that beside Sat and Sun, national holidays are also excluded.) Ed Pollack has a good article about how to design such a table: https://www.sqlshack.com/designing-a-calendar-table/

Once you have this table, you can create your view as something like this:

CREATE VIEW MyView AS 
    SELECT ...
    FROM  T1
    WHERE (SELECT workday FROM Calendar WHERE date = convert(date, sysdatetime()) <= 7
      OR    DAY(sysdatetime()) => 21
   UNION ALL
   SELECT ...
   FROM  T2
   WHERE (SELECT workday FROM Calendar WHERE date = convert(date, sysdatetime()) > 7
       AND DAY(sysdatetime()) < 21

That is, by using UNION ALL, you can combine SELECT with mutually exclusive conditions to only get data from one table.