|
1 |
originally posted 2013-10-4 |
There are probably way more elegant ways to do this… but “Mission Accomplished”.
The gotcha in all of this is the biweekly (26 payments per year) schedule, because you need to know which week in a given month should be the chosen one. You can’t just assume it’s always the second week of a given month because the second week in that month may land on the first week of a month down the line. The SQL function below starts from a reference date with which it calibrates the start of the bi-weekly pay schedule and calculates the rest of the pay dates through the end date. Most organizations have a payroll system that calculates new pay dates from the last pay date of record (that’s the reference date here). The bottom line is an actual pay date of record must be known from which future pay dates can be calculated. The weekly (52 payments per year) and semi-monthly (24 payments per year) don’t have this problem, though the latter can have pay days that land on weekend days for which special handling is required… an aspect which is included in the T-SQL below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 |
create function [dbo].[SIP_MIP_GetWeekly52PayDate] ( @startDate date ,@dayOfWeek int ,@nextDateOnly bit = 1 ) returns date as begin declare @outDate date = null; if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day if(@dayOfWeek is null or @dayOfWeek <1) return null; declare @dow int = datepart(weekday, @startDate) if(@dow=@dayOfWeek) set @outDate = @startDate; else set @outDate = dateadd(d,@dayofWeek-@dow,@startDate) if(@outDate<@startDate) set @outDate = dateadd(ww,1,@outDate) return @outDate end GO create function [dbo].[SIP_MIP_GetSemiMonthly24PayDate] ( @startDate date ,@payDay1 int ,@payDay2 int ,@nextDateOnly bit = 1 ) returns date as begin declare @outDate date = null; if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day if(@payDay1>@payDay2) begin --swap sm24PayDay values declare @tmp int = @payDay1; set @payDay1 = @payDay2; set @payDay2 = @tmp; end --adjust if the last day of the month doesn't have @payDay2 days declare @endOfMonth date = dateadd(s,-1,dateadd(mm, datediff(m,0,@startDate)+1,0)) if(@payDay2>day(@endOfMonth)) set @payDay2 = day(@endOfMonth) --use the last day of the month if the month doesn't have @payDay2 days declare @firstPayDateOfMonth date = convert(date, convert(nvarchar(5),Year(@startDate)) + '-' + convert(nvarchar(2),Month(@startDate)) + '-' + convert(nvarchar(2),@payDay1)); if(datepart(weekday, @firstPayDateOfMonth) = 1) set @firstPayDateOfMonth = dateadd(d,-2,@firstPayDateOfMonth) else if(datepart(weekday, @firstPayDateOfMonth) = 7) set @firstPayDateOfMonth = dateadd(d,-1,@firstPayDateOfMonth) if(@startDate<=@firstPayDateOfMonth) set @outDate = @firstPayDateOfMonth; else begin declare @secondPayDateOfMonth date = convert(date, convert(nvarchar(5),Year(@startDate)) + '-' + convert(nvarchar(2),Month(@startDate)) + '-' + convert(nvarchar(2),@payDay2)); if(datepart(weekday, @secondPayDateOfMonth) = 1) set @secondPayDateOfMonth = dateadd(d,-2,@secondPayDateOfMonth) else if(datepart(weekday, @secondPayDateOfMonth) = 7) set @secondPayDateOfMonth = dateadd(d,-1,@secondPayDateOfMonth) if(@startDate<=@secondPayDateOfMonth) set @outDate = @secondPayDateOfMonth; else begin declare @firstPayDateOfNextMonth date = dateadd(m,1,@startDate); set @firstPayDateOfNextMonth =convert(date, convert(nvarchar(5),Year(@firstPayDateOfNextMonth)) + '-' + convert(nvarchar(2),Month(@firstPayDateOfNextMonth)) + '-' + convert(nvarchar(2),@payDay1)); if(datepart(weekday, @firstPayDateOfNextMonth) = 1) set @firstPayDateOfNextMonth = dateadd(d,-2,@firstPayDateOfNextMonth) else if(datepart(weekday, @firstPayDateOfNextMonth) = 7) set @firstPayDateOfNextMonth = dateadd(d,-1,@firstPayDateOfNextMonth) if(@startDate<=@firstPayDateOfNextMonth) set @outDate = @firstPayDateOfNextMonth; else begin declare @secondPayDateOfNextMonth date = dateadd(m,1,@startDate); set @secondPayDateOfNextMonth =convert(date, convert(nvarchar(5),Year(@secondPayDateOfNextMonth)) + '-' + convert(nvarchar(2),Month(@secondPayDateOfNextMonth)) + '-' + convert(nvarchar(2),@payDay2)); if(datepart(weekday, @secondPayDateOfNextMonth) = 1) set @secondPayDateOfNextMonth = dateadd(d,-2,@secondPayDateOfNextMonth) else if(datepart(weekday, @secondPayDateOfNextMonth) = 7) set @secondPayDateOfNextMonth = dateadd(d,-1,@secondPayDateOfNextMonth) if(@startDate<=@secondPayDateOfNextMonth) set @outDate = @secondPayDateOfNextMonth; end end end return @outDate; end GO create function [dbo].[SIP_MIP_GetBiWeekly26PayDate] ( @startDate date ,@referenceDate date ,@nextDateOnly bit = 1 ) returns date as begin declare @outDate date = null; if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day --determine how many weeks are between the start and reference dates declare @weeks int = datediff(ww,@referenceDate,@startDate); declare @weekly52StartDate date if(@weeks=0) begin --start date is less than a week away from the reference date if(@referenceDate<>@startDate) set @outDate = dateadd(ww,2,@referenceDate); else set @outDate = @startDate; end else begin --start date is one or more weeks away from the reference date declare @modulo int = @weeks % 2 if (@modulo<>0) set @weeks = @weeks+@modulo --make the offset number of weeks from the ref date is even if(@weeks<0) set @weeks = @weeks+2 --the ref date is later than the start date, adjust to the pay period on or after the start date set @outDate = dateadd(ww,@weeks,@referenceDate); if(@outDate<@startDate) set @outDate = dateadd(ww,2,@outDate); end return @outDate end GO create function [SIP_NPT_tfGetPayDays] ( @startDate date ,@endDate date ,@biWeekly26ReferencePayDate date ,@semiMonthly24PayDay1 int ,@semiMonthly24PayDay2 int ) returns @tab table ([Date] date, [Type] nvarchar(25),[Days] int, [WorkDays] int) as begin if(@startDate is null) return; declare @dt date declare @sm24Days int = 1; declare @bw26Days int = 1; declare @w52Days int = 1; declare @sm24WorkDays int = 1; declare @bw26WorkDays int = 1; declare @w52WorkDays int = 1; declare @sm24PayDate date declare @bw26PayDate date declare @w52PayDate date --Determine first pay dates for BiWeekly26, Weekly52, and SemiMonthly24 set @bw26PayDate = [dbo].[SIP_MIP_GetBiWeekly26PayDate](@startDate,@biWeekly26ReferencePayDate,0); set @w52PayDate = [dbo].[SIP_MIP_GetWeekly52PayDate](@startDate, datepart(weekday, @bw26PayDate),0); set @sm24PayDate = [dbo].[SIP_MIP_GetSemiMonthly24PayDate](@startDate,@semiMonthly24PayDay1,@semiMonthly24PayDay2,0); set @dt = @startDate while(1=1) begin --SemiMonthly24 if(@dt=@sm24PayDate) begin --record and calc next pay date insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'SemiMonthly24',@sm24Days,@sm24WorkDays) set @sm24Days = 1; set @sm24WorkDays = 1; set @sm24PayDate = [dbo].[SIP_MIP_GetSemiMonthly24PayDate](@sm24PayDate,@semiMonthly24PayDay1,@semiMonthly24PayDay2,1); end else begin --accumulate days and workdays set @sm24Days = @sm24Days+1; if(datepart(weekday, @dt) not in (1,7)) set @sm24WorkDays = @sm24WorkDays + 1 end --BiWeekly26 if(@dt=@bw26PayDate) begin --record and calc next pay date insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'BiWeekly26',@bw26Days,@bw26WorkDays) set @bw26Days = 1; set @bw26WorkDays = 1; set @bw26PayDate = [dbo].[SIP_MIP_GetBiWeekly26PayDate](@bw26PayDate,@biWeekly26ReferencePayDate,1); end else begin --accumulate days and workdays set @bw26Days = @bw26Days+1; if(datepart(weekday, @dt) not in (1,7)) set @bw26WorkDays = @bw26WorkDays + 1 end --Weekly52 if(@dt=@w52PayDate) begin --record and calc next pay date insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'Weekly52',@w52Days,@w52WorkDays) set @w52Days = 0; set @w52WorkDays = 0; set @w52PayDate = [dbo].[SIP_MIP_GetWeekly52PayDate](@w52PayDate, datepart(weekday, @w52PayDate),1); end else begin --accumulate days and workdays set @w52Days = @w52Days+1; if(datepart(weekday, @dt) not in (1,7)) set @w52WorkDays = @w52WorkDays + 1 end --move to the next day set @dt = dateadd(d,1,@dt); --stop when at the end date if(@dt>=@endDate) break; end return; end GO --TEST select * from [SIP_NPT_tfGetPayDays]('2013-1-1','2013-12-31','2009-12-18',1,15) order by [Type],[Date] GO |