CODEWITHSUNDEEP
sqlsql-servert-sql
Alvin Quezon
Alvin Quezon

Reputation: 189

How to get biweekly dates per month in a year in SQL Server?

I am trying to generate biweekly dates when I set a start year and end year.

DECLARE @StartYear DATETIME
DECLARE @EndYear DATETIME

SET @StartYear = '01/01/2017'
SET @EndYear = '12/31/2017'

The T-SQL should then compute the biweekly dates, example: 03/15/2017 and 03/30/2017 are the paydays.

Also if the dates fall on Saturday and Sunday, then it will generate the dates to its 1st Friday.

Hope someone could help me with this. Or someone would like to share his/her knowledge and formula about this.

UPDATE: my expected result as follows:

when I enter 01/01/2017, then it will automatically generates every 15th day.

Example:

@StartYear: 01/01/2017

Result should be:

DatesBiweeklyPerMonthInAYear
-------------------------------
01/13/2017 (since the 15th day falls on sunday)
01/30/2017
02/15/2017
02/28/2017 (since no 30th day)

If 15th day falls on sun or sat it will fall on Friday of that specific week. so on... until the end of the year.

Is this achievable? Or not?

Thanks!

Upvotes: 1

Views: 4576

Answers (3)

glennvw
glennvw

Reputation: 1

In case it helps someone, this is is a recursive CTE solution that I needed recently:

DECLARE @PayCalendarStartDate DATE = '2024-01-01';
DECLARE @PayCalendarEndDate DATE = '2024-12-31';

WITH PayCalendar AS
    (
        SELECT PayPeriodStartDate = @PayCalendarStartDate
             , PayPeriodEndDate = DATEADD (DAY, 13, @PayCalendarStartDate)
        UNION ALL
        SELECT PayPeriodStartDate = DATEADD (DAY, 14, PayCalendar.PayPeriodStartDate)
             , PayPeriodEndDate = DATEADD (DAY, 14, PayCalendar.PayPeriodEndDate)
        FROM PayCalendar
        WHERE PayPeriodEndDate <= @PayCalendarEndDate
    )
SELECT *
FROM PayCalendar;

Upvotes: 0

KumarHarsh
KumarHarsh

Reputation: 5094

your business rule is not 100% clear.

I think there can be more than one correct result.

In one place it is hard coded,because i want to be 100% sure of requirement. I am not using cursor.Its only 24 loops.

declare @StartYear datetime='2017-01-01'
declare @endYear datetime ='2017-12-31'

declare @gap int =14 --Bimonthly means gap of 14 days or 15 days whatever

;With CTE as
(
select dateadd(day,@gap, @StartYear) Bimonthly
,1 rn

UNION ALL

select  
case 

when (rn+1)%2=0 and datename(m, Bimonthly)='February' THEN
      '2017-02-28'
      when (rn+1)%2=0 and datename(m, Bimonthly)!='February' 

then dateadd(day,@gap, Bimonthly)
else 
dateadd(day,@gap, dateadd(month, datediff(month,0,dateadd(month,1,Bimonthly)),0))
END
,rn+1
from cte
where rn< (datediff(month,@StartYear, @endYear)+1)*2

)
select 
case WHEN datename(dw, Bimonthly)='Saturday' THEN
      dateadd(day,-1, Bimonthly) 
      WHEN datename(dw, Bimonthly)='Sunday' THEN 
      dateadd(day,-2, Bimonthly) 
     else 
      Bimonthly
     end 

     ,rn

from cte

Upvotes: 2

Ahsan
Ahsan

Reputation: 2518

Here is a start. you will have to tweak the logic to get your desired results.

DECLARE @MinDate DATE = '20170101', 
        @MaxDate DATE = '20171231'; 
DECLARE @date DATE; 
DECLARE @counter INT = 0; 
DECLARE my_cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
  SELECT TOP (Datediff(day, @MinDate, @MaxDate) + 1) Date = 
  Dateadd(day, Row_number() 
                 OVER( 
                   ORDER 
  BY a.object_id) - 1, @MinDate) 
  FROM   sys.all_objects a 
         CROSS JOIN sys.all_objects b; 

OPEN my_cursor 

FETCH next FROM my_cursor INTO @date 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF( @counter = 15 ) 
        BEGIN 
            PRINT @date -- here is where you get the 15th date
            SET @counter = 0 
        END 
      SET @counter = @counter + 1 
      FETCH next FROM my_cursor INTO @date 
  END 

CLOSE my_cursor 

DEALLOCATE my_cursor

Upvotes: 2

Related Questions