List calculated dates per month based on parameters

Question

List calculated dates per month based on parameters

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

4

Based on two dates received per parameter, I am trying to list all the dates per month (one date per month) based on the start date and until the end date. These returned dates must check which day of the week of the start date and the number of the week to replicate that date for every month.

For example, @StartDate = 2016/04/15 and @EndDate = 2016/09/01 , I find that the @StartDate is on a first Friday in April, so up to @EndDate would date for every first Friday of every month:

For @StartDate = 2016/04/12 and @EndDate = 2016/09/01 , check that @StartDate is on the second Tuesday of April, then fetch every second Tuesday of each month:

In the case of @StartDate = 2016/04/28 and @EndDate = 2016/09/01 , I find that @StartDate is in the last week to Thursday of April:

In this last case, you would have to take into account the number of weeks of each month, since there are months with 4 and another with 5 weeks.

sql sql-server

asked by anonymous 30.03.2016 / 17:12

3 answers

How to use css in ion-list, ion-item? Align text horizontally in relation to the image

score 1 · Answer 1

You can use a recursive CTE to generate the date list for the period and then filter the dates that match the same day of the week and the number of the week in the month is the same as the start date.

declare @startDate date;
declare @endDate date;

declare @weekday int;
declare @weekmonth int;

select @startDate = '2016/02/15';
select @endDate = '2016/09/13';

select @weekday = DATEPART(dw,@startDate), -- dia da semana
    @weekmonth = DATEPART(day,@startDate)/7 + 1; -- número da semana no mês


;with dateRange as
(
  select dt = dateadd(dd, 1, @startDate)
  where dateadd(dd, 1, @startDate) < @endDate
  union all
  select dateadd(dd, 1, dt)
  from dateRange
  where dateadd(dd, 1, dt) < @endDate
)
select *
from dateRange 
where DATEPART(dw,dt) = @weekday
    and (DATEPART(day,dt)-1)/7 + 1 = @weekmonth
OPTION (MAXRECURSION 0)

score 1 · Answer 2

Here's the solution:

set @NumSemana = datepart(day, datediff(day, DATEADD(mm, DATEDIFF(mm,0,@StartDate), 0), @StartDate)/7 * 7)/7 + 1;
WITH    AllDays
  AS ( SELECT  @StartDate  AS [Date], DATEPART(month, @StartDate) as validMonth
       UNION ALL
       SELECT   DATEADD(week, 1, [Date]), 
                iif(DATEPART(month,DATEADD(week, 1, [Date])) < validMonth + @PeriodicityRepeat, validMonth, validMonth + @PeriodicityRepeat)
       FROM     AllDays
       WHERE    
            DATEPART(month,[Date]) <= DATEPART(month,@EndDate)
        and DATEPART(year,[Date]) <= DATEPART(year,@EndDate)
            ),
rankedDays 
  AS(     
    SELECT [Date], validMonth, 
           row_number() over ( partition by DATEPART( month, [Date]) order by [Date]) ascOrder,
           row_number() over ( partition by DATEPART( month, [Date]) order by [Date] desc) descOrder
    FROM   AllDays 
    WHERE DATEPART(month, [Date]) = validMonth
)
select [Date]
from rankedDays
where ((ascOrder = @NumSemana and @NumSemana <=4 )  
        or (descOrder = 1 and @NumSemana = 5) 
        or [Date] = @StartDate )
   and [Date] < @EndDate 
OPTION (MAXRECURSION 0)

score 0 · Answer 3

You can use DATENAME passing weekday to return week name varchar and DATEPART passing weekday to return week in int

declare @StartDate datetime = 2016/01/15

SELECT DATENAME(weekday,  @StartDate)  -- Retorna o nome da semana varchar
SELECT DATEPART(weekday, @StartDate) -- Retorna a semana int 




declare @StartDate datetime = getdate();

WITH CTE_PastYearinWeeks
AS (
select
DATEADD(wk, DATEDIFF(wk,0,GETDATE()-365), 0) as DateStart
UNION ALL
SELECT DATEADD(WK,DATEPART(weekday, @StartDate),DateStart)
FROM CTE_PastYearinWeeks
WHERE DATEADD(WK,DATEPART(weekday, @StartDate),DateStart) <= GETDATE()
)
select  SUBSTRING(CONVERT(varchar, DateStart, 107),1,6) as WeekTitle
from CTE_PastYearinWeeks
OPTION (MAXRECURSION 0);