Form condition from table without dynamic SQL-CodePudding

Get the license details that are going to expire before the given days of interval. There are two tables, one is the 'Licenses' which holds the licenses details and expiration date. Another table is 'IntervalDays' which holds the days.

We could write the query as below if we hardcode the interval days, BUt how could we use the other table for where conditions. Note: we shouldn't use dynamic sql for the performance considertion, Appreciate it if somebody could help here at the earliest.

SELECT LicenceId
           FROM Licences lic                   
           WHERE  (CONVERT(DATE, vip.ExpirationDate) = CONVERT(DATE, DATEADD(Day, 30, GETDATE()))
                      OR CONVERT(DATE, vip.ExpirationDate) = CONVERT(DATE, DATEADD(Day, 15, GETDATE()))
                      OR CONVERT(DATE, vip.ExpirationDate) = CONVERT(DATE, DATEADD(Day, 7, GETDATE()))
                      OR CONVERT(DATE, vip.ExpirationDate) = CONVERT(DATE, DATEADD(Day, 3, GETDATE()))
                      OR CONVERT(DATE, vip.ExpirationDate) = CONVERT(DATE, DATEADD(Day, 2, GETDATE()))
                      OR CONVERT(DATE, vip.ExpirationDate) = CONVERT(DATE, DATEADD(Day, 1, GETDATE())))

CodePudding user response：

Will this work for you?

    SELECT LicenceId
       FROM Licences lic                   
       WHERE CONVERT(DATE, vip.ExpirationDate) IN (SELECT CONVERT(DATE, DATEADD(Day, [day], GETDATE())) FROM IntervalDays)