Add / Subtracting days from a date to get the next date without counting weekend

Recently had a situation where I had to calculate the next date based on the duration provided to the function. The catch was not to count the weekend (Saturday and Sunday) in the calculation.

I did a lot of google search and landed with  a way to get the next date if the duration was positive (that is the next date will be later than the provided date). I needed a way to go back as well.

After a lot of learning, I came up with this T SQL function for SQL Server that will allow you pass a positive or negative duration parameter and a date. Based on the parameter, it will add or subtract numbers.

Here is the T SQL function. Have fun and enjoy. Feel free to use this, I would really appreciate acknowledgement and link to my blog (http://dmknol.blogspot.com/)

create function [dbo].[GetEndDate_ByWeekdays]
(
 @pdtStartDate datetime,
 @piDuration int
)
returns datetime
as
begin
 declare @rdt datetime
 -- calculating forward
 if (@piDuration > 0)
 begin
   select @rdt = dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end, @pdtStartDate)
   + (datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
    @pdtStartDate)) - 2 + @piDuration) % 5
   + ((datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
    @pdtStartDate)) - 2 + @piDuration) / 5) * 7
   - (datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
   @pdtStartDate)) - 2)
 end

 -- calculating backward
 else
 begin
  -- local variable
  declare @liDuration int,
    @ldt_orgStart datetime,
    @li_orgDuration int
   
  -- save original value
  set @ldt_orgStart = @pdtStartDate
  set @li_orgDuration = @piDuration
  -- divide the duration into set of fives
  while abs(@piDuration)/5 > 0
   begin
    set @piDuration = @piDuration + 5
    --select [@piduration] = @piDuration
    set @liDuration = 7 - 2*(datepart (dw, @pdtStartDate) /7) + (datepart (dw, @pdtStartDate) -2)/7
    set @liduration = @liDuration *(-1)
    --select [@liduration] = @liDuration
    set @pdtStartDate = dateadd(d, @liDuration, @pdtStartDate)
    --select [@pdtStartDate_inside] = @pdtStartDate
   end
  -- add days that were not covered within the five sets
  if (abs(@li_orgDuration) % 5 > 0)
  begin
   --select @piDuration, @pdtStartDate, datepart (dw, @pdtStartDate)
   set @liDuration = case when datepart (dw, @pdtStartDate) <= abs(@piDuration) + 1 then abs(@piDuration) + 2
         when datepart (dw, @pdtStartDate) = 1 then abs(@piDuration) + 1
         else abs(@piDuration) end
   set @liDuration = @liDuration * (-1)
   --select [@liDuration_last] = @liDuration
   set @rdt = dateadd(d, @liDuration, @pdtStartDate)
  end
  else
   set @rdt = @pdtStartDate
 end

 -- return the date
 return @rdt
end
GO

TSQL Averages with Rollup

Here is tsql way of getting averages and also rolling up. Below example is using adventureworks database that is available with SQL server 2008. It is getting average pay rate by each department and also overall by the company.

use [AdventureWorks]

select[Department] = case when grouping(d.GroupName) = 1 then 'Company Average'

                              else d.GroupName end,

            [Average Pay Rate] = avg(p.Rate)

from (select [EmployeeID], [Rate] = max([Rate])

            from [HumanResources].[EmployeePayHistory]

            group by [EmployeeID]) p

      inner join [HumanResources].[EmployeeDepartmentHistory] h

                  on h.[EmployeeID] = p.[EmployeeID] and h.[EndDate] is null

      inner join [HumanResources].[Department] d on d.[DepartmentID] = h.[DepartmentID]

group by d.[GroupName]

with rollup

order by 1

SQL server split received string using XML

Several time, we have a need to split the string based on a delimiter, for instance to handle multiple input filter parameters etc.

 

Most of the time we end up using a user defined function that accepts a string and a delimiter and returns a table of split values. Function may be in-efficient at times.

 

Here is an XML way to split the input string and get a result within a table

 

declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)set @str='A,B,C,D,E'set @delimiter =','set @xml = cast(('<X>' + replace(@str, @delimiter, '</X><X>') + '</X>') as xml)select N.value('.', 'varchar(10)') as value from @xml.nodes('X') as T(N)

SQL Server - Insert in identity column for a table

Sometimes there is a need to insert data into a table that has identity column. Example when you would like to restore a table from a backup including the same IDs so integrity is maintained. Here is simple script

--set identity insert on
set identity_insert <table_name> on

--set identity insert off
set identity_insert <table_name> off