通过星期日名称在SQL中获取最近的日期 [英] Passing in Week Day name to get nearest date in SQL

问题描述

我正在处理一个处理频次值的查询（例如星期一，星期二等） - 考虑分配。

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.

所以如果今天是1/3/2015

So if today is 1/3/2015

我需要我的结果集将被

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

Bu t将其设置为特定日期。我正在看这是一个Web应用程序，我想要当前日期的日期。所以如果下周二尝试运行这个查询， jobId：1的未来日期将会删除1/5并添加2/2 。

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?

推荐答案

您的样本表

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

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

QUERY 1：选择本月最近4周的特定周日strong>

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



例如，在Query2中，最近的日期（这里我们以星期六为例）



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：选择下个4周的特定星期几与日期无关的日期



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



如果 GETDATE（）（如果它的星期六）是

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

这篇关于通过星期日名称在SQL中获取最近的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！