Datediff excluding weekends

• ralph moncrieff-ross

  SSC Enthusiast

  Points: 195

  More actions

  March 12, 2010 at 7:42 am

  #212520

  Hi everone

  using Reporting services 2005

  with MSCRM Dynamics 4

  Can you please help a newbie to reporting services

  I have the following dataset and need to eliminate weekends from the following

  select

  FI.incidentid,

  FI.title,

  FI.subjectidname,

  FI.statuscodename,

  FI.modifiedbyname,

  FI.new_lastupdatetime,

  FI.createdon,convert(varchar,FI.createdon,5) as Date,

  FI.new_solution,

  FI.new_timesupportcallreceived,

  FI.incidentstagecodename,

  DATEDIFF("d",FI.new_timesupportcallreceived, FI.createdon)as difference

  from FilteredIncident as FI

  Thank you for all your time

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 12, 2010 at 8:14 am

  #1132726

  you can add a WHERE statment to eliminate the days, but which datefield? your sample has three fields that might be datetimes:

  new_lastupdatetime, createdon or new_timesupportcallreceived?

  select datename(dw,getdate())--Friday

  and do something like :

  WHERE datename(dw,getdate()) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')

  --or maybe

  WHERE datename(dw,getdate()) NOT IN ('Saturday','Sunday')

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• wim.buyens

  Ten Centuries

  Points: 1159

  More actions

  March 12, 2010 at 8:27 am

  #1132746

  I use this function:

  CREATE FUNCTION [System].[fn_NbrOfWorkingDays] (@StartDate as datetime,@EndDate as datetime)

  RETURNS int

  WITH SCHEMABINDING,RETURNS NULL ON NULL INPUT,ENCRYPTION

  AS

  BEGIN

  RETURN(

  SELECT days/7*5 + days%7--calc workingdays

  - CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END

  - CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END

  FROM (SELECT

  DATEDIFF(day, @StartDate, @EndDate) + 1 AS days,--nbrof days with the 2 dates included

  DATEPART(weekday, @StartDate + @@DATEFIRST - 1) AS wd--weekday of startdate(monday:1)

  ) AS D)

  END

  StartDate: Thursday EndDate: Monday

  Mostly we need 5/7 of the days(for each full week of difference + days difference

  And you need to substract 1 if saturday between the 2 dates (same of sunday)