Passing in Week Day name to get nearest date in SQL

Question

I'm working on a query that deals with a frequency value (i.e. Mondays, Tuesdays, etc. - Think assignments).

So in my query I currently have a result of

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

What I need is the next the 4 future(or current) dates for the frequencyVal.

So if today is 1/3/2015

I would need my result set to be

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'

I was looking at the following post: How to find the Nearest (day of the week) for a given date

But that sets it for a specific date. And I'm looking at this being a web application and I want the dates for the current date. So if I try to run this query next Tuesday the future dates for jobId:1 would remove the 1/5 and add the 2/2.

Is there a way to pass in a weekday value to get the next nearest date?

Answer 1

I prefer a calendar table for this kind of query. Actually, I prefer a calendar table over date functions for most queries. Here's a minimal one. The one I use in production has more columns and more rows. (100 years of data is only 37k rows.)

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);

Set the privileges so that

• everyone can select, but
• almost nobody can insert new rows, and
• even fewer people can delete rows.

(Or write a constraint that can guarantee there are no gaps. I think you can do that in SQL Server.)

You can select the next four Mondays after today with a very simple SQL statement. (The current date is 2015-01-05, which is a Monday.)

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-12
2015-01-19
2015-01-26
2015-02-02

For me, this is a huge advantage. No procedural code. Simple SQL that is obviously right. Big win.

Answer 2

Your sample table

create table #t
(
    jobId int,
    personId int,
    frequencyVal varchar(10)
);

insert into #t values (1,100,'Mondays'),(2,101,'Saturdays');

QUERY 1 : Select nearest 4 week of days in current month for particular week day

-- Gets first day of month
DECLARE @FIRSTDAY DATE=DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

;WITH  CTE as
(
     -- Will find all dates in current month
     SELECT CAST(@FIRSTDAY AS DATE) as DATES
     UNION ALL
     SELECT DATEADD(DAY,1,DATES)    
     FROM    CTE
     WHERE   DATES < DATEADD(MONTH,1,@FIRSTDAY)
 )
,CTE2 AS
(
   -- Join the #t table with  CTE on the datename+'s' 
   SELECT jobId,personId,frequencyVal,DATES,
   -- Get week difference for each weekday        
   DATEDIFF(WEEK,DATES,GETDATE()) WEEKDIFF,
   -- Count the number of weekdays in a month
   COUNT(DATES) OVER(PARTITION BY DATENAME(WEEKDAY,CTE.DATES)) WEEKCOUNT
   FROM CTE
   JOIN #t ON DATENAME(WEEKDAY,CTE.DATES)+'s' = #t.frequencyVal 
   WHERE MONTH(DATES)= MONTH(GETDATE())   
)
-- Converts to CSV and make sure that only nearest 4 week of days are generated for month
SELECT  DISTINCT C2.jobId,C2.personId,frequencyVal,
         SUBSTRING(
        (SELECT ', ' + CAST(DATEPART(MONTH,DATES) AS VARCHAR(2)) + '/'  + 
                       CAST(DATEPART(DAY,DATES) AS VARCHAR(2))
        FROM CTE2 
        WHERE C2.jobId=jobId AND C2.personId=personId AND C2.frequencyVal=frequencyVal AND
                       ((WEEKDIFF<3 AND WEEKDIFF>-3 AND WEEKCOUNT = 5) OR WEEKCOUNT <= 4)
        ORDER BY CTE2.DATES
        FOR XML PATH('')),2,200000) futureDates
FROM CTE2 C2

• SQL FIDDLE

For example, in Query2 the nearest date(here we take example as Saturday) of

2015-Jan-10 will be 01/03,01/10,01/17,01/24
2015-Jan-24 will be 01/10,01/17,01/24,01/31

QUERY 2 : Select next 4 week's dates for particular week day irrelevant of month

;WITH  CTE as
(
     -- Will find the next 4 week details
     SELECT CAST(GETDATE() AS DATE) as DATES
     UNION ALL
     SELECT DATEADD(DAY,1,DATES)    
     FROM    CTE
     WHERE   DATES < DATEADD(DAY,28,GETDATE())
 )
,CTE2 AS
(
   -- Join the #t table with  CTE on the datename+'s' 
   SELECT jobId,personId,frequencyVal, DATES,
   ROW_NUMBER() OVER(PARTITION BY DATENAME(WEEKDAY,CTE.DATES) ORDER BY CTE.DATES) DATECNT
   FROM CTE
   JOIN #t ON DATENAME(WEEKDAY,CTE.DATES)+'s' = #t.frequencyVal  
)
-- Converts to CSV and make sure that only 4 days are generated for month
SELECT  DISTINCT C2.jobId,C2.personId,frequencyVal,   
        SUBSTRING(
        (SELECT ', ' + CAST(DATEPART(MONTH,DATES) AS VARCHAR(2)) + '/'  + 
                       CAST(DATEPART(DAY,DATES) AS VARCHAR(2))
        FROM CTE2 
        WHERE C2.jobId=jobId AND C2.personId=personId AND C2.frequencyVal=frequencyVal 
              AND DATECNT < 5
        ORDER BY CTE2.DATES
        FOR XML PATH('')),2,200000) futureDates
        FROM CTE2 C2

• SQL FIDDLE

The following would be the output if the GETDATE() (if its Saturday) is

2015-01-05 - 1/10, 1/17, 1/24, 1/31
2015-01-24 - 1/24, 1/31, 2/7, 2/14