Select data based on certain set frequency

I have the following table

settings

**id** ->number

**trigger_metric** -> MONTHS, WEEKS, DAYS

**trigger_frequency** -> number

**trigger_start_date** -> date

What I need is to select the entry based on whether it is allowed to trigger on date that or not.

So lets say for example for the following entry id:xxx, trigger_metric: MONTHS, trigger_frequency:1 , trigger_start_date:'2021-09-01'

the row should be selected on the trigger_start_date and then every trigger_frequency trigger_metric i.e. 1 MONTHS in this case. so it should be selected on 2021-09-01 and every 1 months after that so 2021-10-01, 2022-11-01

Same will be the case for DAYS and WEEKS as well.

I wrote the following query to handle this case

SELECT
 *
FROM settings fs
WHERE (fs.trigger_start_date = Convert(DATE, GETDATE()) OR CAST((
  CASE fs.trigger_metric
    WHEN 'MONTHS' THEN
        IIF((DATEDIFF(month, fs.trigger_start_date, GETDATE()) %
             fs.trigger_frequency) = 0 AND
            DATEPART(dd, fs.trigger_start_date) = DATEPART(dd, GETDATE())
          , 1, 0)


    WHEN 'WEEKS' THEN
      IIF(DATEDIFF(week, fs.trigger_start_date, GETDATE()) % fs.trigger_frequency =0 
          AND DATEPART(dw, fs.trigger_start_date) = DATEPART(dw, GETDATE()), 1, 0)


    WHEN 'DAYS' THEN
      IIF((DATEDIFF(day, fs.trigger_start_date, GETDATE()) % fs.trigger_frequency) = 0, 1, 0)

    ELSE 0
    END
  ) AS BIT) = 1);

my logic here for months is that diff the two dates and see if they are at the trigger_frequency difference and also check and see if the date are equal. Meaning if 2 dates are 2021-09-01 and 2021-11-02 and the frequency is 2. Then the dates diff would be divisible by the frequency (2) and since the dates are not equal it would not trigger.

So this code works in most cases except for 1. So lets say the setting is set for AUGUST 31 and the frequency is 1, then since September does not have 31, the query will not work.

So my question is. Is there any way to modify this query to handle the above case that I had mentioned?

Answer

For the limited purpose of determining whether a given date is the last day of a month, you could use the EOMONTH function. You would still need to perform other conditional checks relevant to your requirements, but this should help get around the difficulty of varying month-ends, leap days, etc.

Documentation: https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-ver15

Advertisement

Answer