sql-server - 传入工作日名称以获取 SQL 中最接近的日期

Question

我正在处理一个处理频率值的查询(即星期一、星期二等 - 考虑作业)。

因此，在我的查询中，我目前的结果是

jobId:1, personId:100, frequencyVal: 'Mondays'
jobId:2, personId:101, frequencyVal: 'Saturdays'

我需要的是接下来的 4 个 future (或当前)频率Val 日期。

如果今天是 2015 年 1 月 3 日

我需要我的结果集是

jobId:1, personId:100, frequencyVal: 'Mondays', futureDates: '1/5,1/12,1/19,1/26'
jobId:2, personId:102, frequencyVal: 'Saturdays', futureDates: '1/3,1/10,1/17,1/24'

我正在查看以下帖子: How to find the Nearest (day of the week) for a given date

但这将其设置为特定日期。我正在查看这是一个网络应用程序，我想要当前日期的日期。因此，如果我尝试在下周二运行此查询，jobId:1 的 future 日期将删除 1/5 并添加 2/2。

有没有办法传入工作日值来获取下一个最近的日期？

Answer 1

我更喜欢使用日历表来进行此类查询。实际上，对于大多数查询，我更喜欢日历表而不是日期函数。这是一个最小的。我在生产中使用的有更多的列和更多的行。 (100 年的数据只有 37k 行。)

create table calendar (
  cal_date date not null primary key,
  day_of_week varchar(15)
  );

insert into calendar (cal_date) values 
('2015-01-01'), ('2015-01-02'), ('2015-01-03'), ('2015-01-04'),
('2015-01-05'), ('2015-01-06'), ('2015-01-07'), ('2015-01-08'),
('2015-01-09'), ('2015-01-10'), ('2015-01-11'), ('2015-01-12'),
('2015-01-13'), ('2015-01-14'), ('2015-01-15'), ('2015-01-16'),
('2015-01-17'), ('2015-01-18'), ('2015-01-19'), ('2015-01-20'),
('2015-01-21'), ('2015-01-22'), ('2015-01-23'), ('2015-01-24'),
('2015-01-25'), ('2015-01-26'), ('2015-01-27'), ('2015-01-28'),
('2015-01-29'), ('2015-01-30'), ('2015-01-31'),

('2015-02-01'), ('2015-02-02'), ('2015-02-03'), ('2015-02-04'),
('2015-02-05'), ('2015-02-06'), ('2015-02-07'), ('2015-02-08'),
('2015-02-09'), ('2015-02-10'), ('2015-02-11'), ('2015-02-12'),
('2015-02-13'), ('2015-02-14'), ('2015-02-15'), ('2015-02-16'),
('2015-02-17'), ('2015-02-18'), ('2015-02-19'), ('2015-02-20'),
('2015-02-21'), ('2015-02-22'), ('2015-02-23'), ('2015-02-24'),
('2015-02-25'), ('2015-02-26'), ('2015-02-27'), ('2015-02-28')
;

update calendar
set day_of_week = datename(weekday, cal_date);

alter table calendar 
alter column day_of_week varchar(15) not null;

alter table calendar
add constraint cal_date_matches_dow
check (datename(weekday, cal_date) = day_of_week);

create index day_of_week_ix on calendar (day_of_week);

设置权限以便

• 每个人都可以选择，但是
• 几乎没有人可以插入新行，并且
• 能够删除行的人更少。

(或者编写一个可以保证没有间隙的约束。我认为您可以在 SQL Server 中做到这一点。)

您可以使用非常简单的 SQL 语句选择今天之后接下来的四个星期一。 (当前日期为2015年1月5日，周一。)

select top 4 cal_date
from calendar
where cal_date > convert(date, getdate())
  and day_of_week = 'Monday'
order by cal_date;

CAL_DATE--2015-01-122015-01-192015-01-262015-02-02

对我来说，这是一个巨大的优势。没有程序代码。简单的 SQL 显然是正确的。大胜利。