一个月的第N周的SQL第N天 [英] SQL Nth Day of Nth Week of a Month

问题描述

我想使用以下功能来安排每月按照月份的星期和工作日进行的俱乐部会议。在下面的示例中，我有一个（将要）的函数返回本月的第三个星期三。如果那天发生在过去，那么它将在下个月的第三个星期三返回。

I want to use the following function for scheduling club meetings which occur on a monthly basis based on the week and weekday of the month. In the example below I have a (to be) function that returns the Third Wednesday of the month. If that day occurs in the past then it returns the next month's 3rd Wednesday.

我想离开循环，我觉得有一个更好的计算方法。是否有更多的OO流程？您的意见？

I want to get away from the Loops and I feel that there is a better method for calculation. Is there a more OO process? Your opinion?

--CREATE FUNCTION NextWeekDayofMonth

DECLARE 
--(
        @WEEK INT,
        @WEEKDAY INT,
        @REFERENCEDATE DATETIME
--) 
--RETURNS DATETIME
--AS

-------------------------------
--Values for testing - Third Wednesday of the Month
set @WEEK = 3   --Third Week
set @WEEKDAY = 4    --Wednesday
set @REFERENCEDATE = '08/20/2011'
-------------------------------

BEGIN

    DECLARE @WEEKSEARCH INT
    DECLARE @FDOM DATETIME
    DECLARE @RETURNDATE DATETIME
    SET @FDOM = DATEADD(M,DATEDIFF(M,0,@REFERENCEDATE),0)
    SET @RETURNDATE = DATEADD(M,0,@FDOM)


    WHILE (@RETURNDATE < @REFERENCEDATE)
    --If the calculated date occurs in the past then it 
    --finds the appropriate date in the next month
    BEGIN

    SET @WEEKSEARCH = 1
    SET @RETURNDATE = @FDOM

    --Finds the first weekday of the month that matches the provided weekday value
        WHILE ( DATEPART(DW,@RETURNDATE) <> @WEEKDAY)
            BEGIN
            SET @RETURNDATE = DATEADD(D,1,@RETURNDATE)
            END

    --Iterates through the weeks without going into next month
        WHILE @WEEKSEARCH < @WEEK
            BEGIN
            IF MONTH(DATEADD(WK,1,@RETURNDATE)) = MONTH(@FDOM) 
                BEGIN
                    SET @RETURNDATE = DATEADD(WK,1,@RETURNDATE)
                    SET @WEEKSEARCH = @WEEKSEARCH+1
                END 
                ELSE
                    BREAK
            END
        SET @FDOM = DATEADD(M,1,@FDOM)
    END

    --RETURN @RETURNDATE
    select @ReturnDate
    END

推荐答案

IMO，最好的过程是将重要的业务信息存储在数据库表中的行中。如果你建立一个日历表，你可以通过一个简单的查询获得所有的第三个星期三。

IMO, the best process is to store important business information as rows in tables in a database. If you build a calendar table, you can get all the third Wednesdays by a simple query. Not only are the queries simple, they can be seen to be obviously correct.

select cal_date 
from calendar
where day_of_week_ordinal = 3
  and day_of_week = 'Wed';

今天或之后的第三个星期三也很简单。

The third Wednesday that's on or after today is also simple.

select min(cal_date)
from calendar
where day_of_week_ordinal = 3
  and day_of_week = 'Wed'
  and cal_date >= CURRENT_DATE;

创建日历表很简单。这是为PostgreSQL编写的，但它是完全标准的SQL（我认为），除了与ISO年和ISO周相关的列。

Creating a calendar table is straightforward. This was written for PostgreSQL, but it's entirely standard SQL (I think) except for the columns relating to ISO years and ISO weeks.

create table calendar (
  cal_date date primary key,
  year_of_date integer not null 
    check (year_of_date = extract(year from cal_date)),
  month_of_year integer not null 
    check (month_of_year = extract(month from cal_date)),
  day_of_month integer not null 
    check (day_of_month = extract(day from cal_date)),
  day_of_week char(3) not null 
    check (day_of_week = 
    case when extract(dow from cal_date) = 0 then 'Sun'
         when extract(dow from cal_date) = 1 then 'Mon'
         when extract(dow from cal_date) = 2 then 'Tue'
         when extract(dow from cal_date) = 3 then 'Wed'
         when extract(dow from cal_date) = 4 then 'Thu'
         when extract(dow from cal_date) = 5 then 'Fri'
         when extract(dow from cal_date) = 6 then 'Sat'
    end),
  day_of_week_ordinal integer not null
    check (day_of_week_ordinal = 
      case
        when day_of_month >= 1 and day_of_month <= 7 then 1
        when day_of_month >= 8 and day_of_month <= 14 then 2
        when day_of_month >= 15 and day_of_month <= 21 then 3
        when day_of_month >= 22 and day_of_month <= 28 then 4
        else 5
      end),
  iso_year integer not null 
    check (iso_year = extract(isoyear from cal_date)),
  iso_week integer not null 
    check (iso_week = extract(week from cal_date))
);

您可以使用电子表格或UDF填充该表。电子表格通常具有非常好的日期和时间功能。我有一个UDF，但它是为PostgreSQL（PL / PGSQL）编写的，所以我不知道它可以帮助你多少。但如果你愿意，我会稍后再发布。

You can populate that table with a spreadsheet or with a UDF. Spreadsheets usually have pretty good date and time functions. I have a UDF, but it's written for PostgreSQL (PL/PGSQL), so I'm not sure how much it would help you. But I'll post it later if you like.

这篇关于一个月的第N周的SQL第N天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！