CODEWITHSUNDEEP
sqlsql-server
webdad3
webdad3

Reputation: 9080

What is the best way to find next n week days

I got the following code from the following question I asked: Passing in Week Day name to get nearest date in SQL

I need to find next 4 Weekdays based on today's date for corresponding Day-Of-Week in my table ie, if today is 2015-01-24 the result should be 1/24, 1/31, 2/7, 2/14 for Saturdays.

TABLE

enter image description here

SAMPLE QUERY

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

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

WITH cte(n) AS
(
    SELECT 0
    UNION ALL
    SELECT n+1 FROM cte WHERE n < 3
)

select #t.jobId, #t.personId, #t.frequencyVal, STUFF(a.d, 1, 1, '') AS FutureDates
from #t
cross apply (SELECT CASE #t.frequencyVal
                         WHEN 'SUNDAYS'    THEN 1 
                         WHEN 'MONDAYS'    THEN 2 
                         WHEN 'TUESDAYS'   THEN 3 
                         WHEN 'WEDNESDAYS' THEN 4 
                         WHEN 'THURSDAYS'  THEN 5 
                         WHEN 'FRIDAYS'    THEN 6
                         WHEN 'SATURDAYS'  THEN 7 
                    END)tranlationWeekdays(n)
cross apply (select ',' +  CONVERT(varchar(10),  CONVERT(date,dateadd(WEEK, cte.n,CONVERT(DATE, DATEADD(DAY, (DATEPART(WEEKDAY, GETDATE()) + tranlationWeekdays.n) % 7, GETDATE()))))) from cte FOR XML PATH('')) a(d);

drop table #t;

EXPECTED RESULT

enter image description here

Upvotes: 1

Views: 334

Answers (2)

G B
G B

Reputation: 1462

This is a simpler way I think, and I think it fits your requirements Note that I have changed your frequency_val column to an integer that represents the day of the week from SQL servers perspective and added a calculated column to illustrate how you can easily derive the day name from that. 

/*************************************************/
--Set up our sample table
/*************************************************/
declare @t table
(
    jobId int,
    personId int,
    --frequencyVal varchar(10) -- why store a string when a tiny int will do.
    frequency_val tinyint,
    frequency_day as datename(weekday,frequency_val -1) + 's'
)



insert into @t 
values 
    (1,100,1),--'Mondays'),
    (2,101,6),--'Saturdays');
    (3,101,7),--'Sundays');
    (4,100,2)--'Tuesdays'),
--select * from @t



/*************************************************/
--Declare & initialise variables 
/*************************************************/
declare @num_occurances int = 4
declare @from_date date = dateadd(dd,3,getdate()) -- this will allow you to play with the date simply by changing the increment value



/*************************************************/
-- To get a row for each occurance
/*************************************************/
;with r_cte (days_ahead, occurance_date)
as (select 0, convert(date,@from_date,121)
    union all
    select r_cte.days_ahead +1, convert(date,dateadd(DD, r_cte.days_ahead+1, @from_date),121)
    from r_cte
    where r_cte.days_ahead < (7 * @num_occurances) -1
    )
select t.*, r_cte.occurance_date
from
    @t t
    inner join r_cte
        on DATEPART(WEEKDAY, dateadd(dd,@@DATEFIRST - 1 ,r_cte.occurance_date)) = t.frequency_val



/*************************************************/
--To get a single row with a CSV of every occurance
/*************************************************/
;with r_cte (days_ahead, occurance_date)
as (select 0, convert(date,@from_date,121)
    union all
    select r_cte.days_ahead +1, convert(date,dateadd(DD, r_cte.days_ahead+1, @from_date),121)
    from r_cte
    where r_cte.days_ahead < (7 * @num_occurances) -1
    )
select 
    t.*, 
    STUFF(  (select ', '
                + convert(varchar(2),datepart(month,occurance_date),0) + '/' 
                + convert(varchar(2),datepart(day,occurance_date),0) as occurance
            from r_cte
            where DATEPART(WEEKDAY, dateadd(dd,@@DATEFIRST - 1 ,r_cte.occurance_date)) = t.frequency_val
            FOR XML PATH (''),TYPE).value('.','varchar(30)') 
        ,1,2,'') occurance_date -- rest of STUFF() function
from
    @t t

Upvotes: 0

Sarath Subramanian
Sarath Subramanian

Reputation: 21281

Gets the first day of current month.

DECLARE @FIRSTDAY DATE=DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Create the table and insert values

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

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

You can use either of the below queries for your situation.

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

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

;WITH  CTE as
(
     -- Will find all dates in current month
     SELECT @FIRSTDAY 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,
   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
   WHERE MONTH(DATES)= MONTH(GETDATE())   
)
-- 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 DATECNT<5
        ORDER BY CTE2.DATES
        FOR XML PATH('')),2,200000) futureDates
        FROM CTE2 C2

For example, in Query1 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/03,01/10,01/17,01/24

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

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

;WITH  CTE as
(
     -- Will find all dates in current
     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

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 3 : Select next 4 week's dates for particular week day irrelevant of month

;WITH  CTE as
(
     -- Will find all dates in current month
     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

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

Upvotes: 2

Related Questions