DateDiff: Calculating Working Days

Aug 14, 2007

I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?

View 8 Replies


ADVERTISEMENT

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:

ce.enquiry_time represents when the enquiry was logged

(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))

It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.

View 1 Replies View Related

DateDiff That Only Counts Working Days

Jul 10, 2006

Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?

View 6 Replies View Related

TSQL : Calculating Working Days Between Two Dates

Jul 12, 2000

Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !

Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.

View 2 Replies View Related

TSQL : Calculating Working Days Between Two Dates

Jul 12, 2000

Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !

Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.

View 1 Replies View Related

Calculating Number Of Working Days In A Month

Sep 4, 2007

Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.

can anyone say the solution?

thanks.

View 2 Replies View Related

Reporting Services :: Calculating Average Amount Of Working Days In A Month - SSRS 2005 Matrix

Jun 22, 2015

I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?

View 7 Replies View Related

DateDiff Not Calculating As Expected

Feb 19, 2008

I have a stored procedure that is Averaging a Difference in dates in seconds. All of the sudden it started throwing an Arithmetic overflow error. After running the query below on the same data, I can see that it is because the DateDiff in my procedure, which is calculating the difference in seconds, is returning a value greater than 68 years. Looking at the dates in the result table, I don't see how it is coming up with the values in the Years Difference column.




Code SnippetSELECT createdate, completeddate, DATEDIFF(y, createdate, completeddate) as 'years difference'
FROM tasks
WHERE (TaskStatusID = 3) and (createdate < completeddate) and (DATEDIFF(y, createdate, completeddate)>=68)
ORDER BY completeddate




Results:




CreateDate
CompletedDate
Years Difference

2007-07-28 16:26:17
2007-10-08 20:45:19
72

2007-07-28 15:56:13
2007-10-12 19:40:28
76

2007-07-28 16:06:20
2007-10-18 21:00:05
82

2007-07-30 01:15:54
2007-10-21 20:18:43
83

2007-07-29 23:12:08
2007-10-22 23:22:47
85

2007-08-16 11:17:29
2007-10-23 15:47:32
68

2007-07-25 21:20:44
2007-10-23 19:11:32
90

2007-07-29 23:04:53
2007-10-23 21:09:47
86

2007-08-22 18:29:50
2007-11-02 14:29:12
72

2007-08-23 18:33:38
2007-11-02 14:38:34
71

2007-07-29 21:46:10
2007-11-02 23:24:39
96

2007-08-14 05:18:17
2007-11-03 21:08:30
81

View 21 Replies View Related

DATEDIFF For Business Days Only

Aug 16, 2007

I'm looking for a DATEDIFF function which will give me the
BUSINESS days difference between 2 dates.
e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4.
is there such a function?
Thx

View 4 Replies View Related

Datediff Needs To Deliver Month AND Days

Aug 26, 2005

with datediff all I can get it to return is months or a total of thedays...so if the difference in dates is 12 months 4 days how do I adjust theSQL to accommodate both?

View 3 Replies View Related

DATEDIFF Case Statement - Calculate Number Of Days

Feb 14, 2014

Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]

This is my query so far

SELECT [CreatedDate]
,[StageName]
,[CloseDate]
,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

Which produces the following

CreatedDate2012-12-17
StageNameLive Transactions
CloseDate2012-12-31
Age424

When in fact the age should be 14days

View 7 Replies View Related

Calculating Overdue Days

Aug 21, 2005

I have table with three columns:     1) TodaysDate,
           
           
           
                2)
DueBackDate,    
           
           
           
            3) DaysOverdue

The problem I am having is creating a formula in MS SQL, so that when
the 'DueBackDate' column is more than todays date, I want the
'DaysOverdue' column to be incremented accordingly.

I have tried creating an SQL Query something like this:

UPDATE rental

SET  DaysOverdue = DaysOverdue + 1

WHERE  ({ fn NOW() } > DueBackDate)

But the problem with this that it only increments the 'DaysOverdue'
field by 1. When I want it to basically subtract todays date by
'DueBackDate'.

I have tried to also create a formula within MS SQL but I come up with
errors. I have done something like this in Access which worked. The
Formula looked like this:
                         IIF(Now()>[DueBackDate],int(Now()-[DueBackDate]),0)

Is there a way to convert this formula so that is would work in MS SQL?

Thanks.

View 5 Replies View Related

Calculating The No. Of Days In A Month

Jul 14, 2004

How can I calculate the no. of days in a month.I know it is a simple question but I am not able to find any code to calculate that.
Can someone help me?

View 7 Replies View Related

SQL Server 2012 :: Using DateDiff To Work As Excel Network Days

Jan 2, 2015

So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)

When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.

Luckily there is another table in the database called - ih_non_work_days.

The format of the date is "2014-12-25 00:00:00.000" for example in that table.

How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?

So for now my basic script looks like -

SELECT com.comm_reference AS 'Referance'
,com.current_task_target_date AS 'TargetDate'
, DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'
FROM [dbo].[em_communication] as com

View 2 Replies View Related

Calculating Avg Of Days Broken Down By Period

Nov 28, 2013

Aim – Calculate the number of days between CreatedDate and [Date_Docs_In_Complete__c],count how many Ids, Fall within a specific month by year, and then work out the avg number of days it took for each month

My query so far

select
ID,
left(CreatedDate,10) as CreatedDate,
left([Date_Docs_In_Complete__c],10) as [Date_Docs_In_Complete__c],
DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days
from #build

Produces following results

IDCreatedDateDate_Docs_In_Complete__cDays
0063000000ausKGAAY2010-03-262013-07-161208
0063000000mC359AAC2011-06-302013-07-03734
0063000000oyaSPAAY2011-11-292013-07-18597

Desired outcome results would be

Year Month Date_Docs_In_Complete__c Total Days Avg_Days
2013 07 3 2539846.3

View 2 Replies View Related

Fetch Data Of User Who Have Created Profile Within 7 Days - DateDiff Function

May 2, 2015

I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .

I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.

select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC

System.DateTime.Now is a function for getting current date time in C#

The query is neither giving error nor giving desired result.

View 4 Replies View Related

Calculating Date Based On Business Days

Jul 21, 2001

Does anyone know of a way to calculate the date 'x' number of business days after another date?

View 2 Replies View Related

Transact SQL :: Calculating Date Difference In Days

Apr 16, 2015

I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:

SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code

I keep getting the following message:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Warning: Null value is eliminated by an aggregate or other SET operation.

No matter what filter I use to process non-blank dates, it never works.

View 12 Replies View Related

Transact SQL :: Calculating Duration In Days For Generic From And To

Oct 7, 2015

I need the SQL to calculate the duration between a day and time, no specific date. 

For example:
Sun 00:00 > Mon 08:00 = 5.67 days

So there is no date, it's just the general duration  (in days), from a specific day and time to a specific day and time.

View 3 Replies View Related

SQL Server 2012 :: Calculating Working Hours Between 2 Dates

May 22, 2014

This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.

CREATE function [dbo].[fnc_myHinkley_ASSY_CalcWorkingMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS decimal(14,2)
/*
Programmer: Goran Borojevic
Date: 5/14/2014

This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM.
*/
AS
BEGIN

--check if one of the dates is null
if @StartDate is null or @EndDate is null
RETURN 0

[code]...

View 9 Replies View Related

Been Working On A Query For A Few Days Now ...

Jan 23, 2008

I have the following tables

EntertainmentType
typeId PK tinyint
type varchar(30)

Entertainment
id PK int
typeId FK tinyint
title varchar(35)
description varchar(300)
purchaseDate smalldatetime

CheckedOut
recordId PK int
id >> dunno if I should make this a foreign key - relates to Entertainment.id
checkOutDate smalldatetime
dueBackDate smalldatetime
userId
returned bit

It is actually has a relationship that is similar to a regular customers, orders set of tables.

I have a list of movies and every time a movie is checked out a record gets added to the checkedout table. So while there is 1 of each movie in the entertainment table ... the movie may be referred to in the checkedout table multiple times ...

The result set that I am trying to get, and that i've spent all day on, is - all the movies and an indication of whether they are currently available for checkout.

i have the following, which I also had help with ...

select * from entertainment
where entId not in
( select entId from checkedout
where
-- checks if dates conflict, assume 2 days checkout
( checkOutDate > dateadd(d,2,getdate())
or dueBackDate < getdate() )
or
-- checks if current booking returned and is now available
( checkOutDate < getdate()
and dueBackDate > getdate()
and returned = 'true')
)

though this returns a list of all the movies that are currently available for checkout. I need to be able to show all the movies that I have, so that someone knows that I have it even if its not available right now. The relationship is very similar to a customers - orders set of tables and I suppose the equivalent would be asking for a list of all the customers indicating the lastest product they bought ...

If I replace not in with exists I get the desired result but it won't work with a join so I don't know how to indicate if its available or not. Does anyone have any suggestions ... I appreciate any help you can provide ...

View 7 Replies View Related

We Are Working On This Sp For Last 4 Days Plese

May 21, 2008

executing this procedure taking time please solve it for less time any modifications


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.r_routeGetCache2 Script Date: 5/21/2008 3:07:05 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[r_routeGetCache2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[r_routeGetCache2]
GO


/*
---------------
Upgrade Version of r_routeGetCache1 to Implement Grade
By N Mohaed
2005 - 12 - 12
----------------
*/
CREATE procedure r_routeGetCache2
@pLongest varchar(32),
@pLongestCls int,
@pDate datetime,
@pSrcInt varchar(7),
@pSrcIntGroup varchar(64),
@pSrcIntDom varchar(64),
@routeclass int=0 ,
@pCLI varchar(32)='',
@pOperatorGroup int = 0 ,
@isgrade int=0,
@pRouteName varchar(32) = ''
as
begin
declare @pc varchar(2)
declare @dd int
declare @hh int
declare @mm int

set @pc=left(@pLongest,2)
set @dd=1+(@@datefirst+datepart(dw,@pDate)-2)%7
--select @hh=(datepart(hh, @pDate)-timezone) from r_info
set @hh=(datepart(hh, @pDate)-0)
set @mm=datepart(mi, @pDate)
set nocount on

--- Azam
select top 1 @routeclass = routecls from r_interface
where [group] = @pSrcIntGroup
and state='I'

if @routeclass is null set @routeclass=0
--- End



--insert
CREATE TABLE #operator_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[cls] [varchar] (1) NOT NULL ,
[pc] [varchar] (2) NOT NULL
)

--CREATE UNIQUE CLUSTERED INDEX [idx1] ON
--[dbo].[#operator_selected]([routecls], [oprId], [cls], [pc]) ON [PRIMARY]


insert into #operator_selected(routecls,oprid,cls,pc)
select x.routecls,x.oprid,x.cls,x.pc from
r20_route_timecls x (nolock),
r_TimeCls y with (nolock),
r_interface tit with (nolock),
r_timecode tco with (nolock),
r_daycode dco with (nolock)
where x.routecls=0
and tit.id = x.oprid and tit.state = 'I'
and y.tintid =x.oprid and y.cls=x.cls
and y.dc=dco.id and y.tc=tco.id and y.sc=0
and @dd between dco.d1 and dco.d2
and ((24+@hh+isnull(tit.prefixcls,0))%24) * 100 + @mm between tco.h1
and tco.h2-1
and x.pc=@pc


CREATE TABLE #timecls_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[pc] [varchar] (2) NOT NULL ,
[prefixcode] [varchar] (50) NOT NULL ,
cnt int,
clsA int,
clsP int,
clsO int,
clsW int,
[cls] [varchar] (1) NOT NULL
)

insert into
#timecls_selected(routecls,oprid,pc,prefixcode,cnt,clsA,clsP,clsO,clsW,cls)
select a.routecls, a.oprid, a.pc, max(a.prefixCode) prefixCode,
count(*) cnt,
sum(case when a.cls='A' then 1 else 0 end) clsA,
sum(case when a.cls='P' then 1 else 0 end) clsP,
sum(case when a.cls='O' then 1 else 0 end) clsO,
sum(case when a.cls='W' then 1 else 0 end) clsW, 'Z' cls
from r20_route12 a(nolock), #operator_selected b
where a.routeCls=@routeclass --b.routeCls
and a.oprId=b.oprId and a.cls=b.cls
and a.pc=b.pc
and left(@pLongest,prefixLen)=a.prefixcode
group by a.routecls, a.oprid, a.pc

update #timecls_selected
set cls = (case when (clsW>0) and (cnt = clsW) then 'W'
when (clsO>0) and (cnt = clsO) then 'O'
when (clsP>0) and (cnt = clsP) then 'P'
when (clsA>0) and (cnt = clsA) then 'A'
else 'Z' end)

update #timecls_selected
set cls = (select max(a.cls) from r20_route12 a (nolock)
where a.routecls=#timecls_selected.routecls
and a.oprid = #timecls_selected.oprId
and a.pc=#timecls_selected.pc
and a.prefixCode=#timecls_selected.prefixCode
and ((a.cls='A' and #timecls_selected.clsA<>0) or
(a.cls='P' and #timecls_selected.clsP<>0) or
(a.cls='O' and #timecls_selected.clsO<>0) or
(a.cls='W' and #timecls_selected.clsW<>0))
)
where cls = 'Z'

CREATE TABLE #route (
seqno int identity(10,10),
priority int,
[id] int,
isactive int,
reason int,
exception int,
exceptionCls int,
calcexception int,
ext int,
oprid int,
parentid int,
routecls int,
prefixcode varchar(50),
universe varchar(5),
domain varchar(5),

pdomain varchar(5),
[group] varchar(40),
interface varchar(40),
userinfo varchar(40),
hint varchar(100),
clsorg char(1),
cls char(1),
cost float,
flag int,
rating int,
access varchar(10),
redlist int ,quality float)


if @isgrade =0
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
x.state isactive, x.reason,
isnull(x.exception,0) exception,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else cast((x.exception-0.5)*10 as int)
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc and
x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.cost
end
else
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
x.state isactive, x.reason,
isnull(x.exception,0) exception,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else cast((x.exception-0.5)*10 as int)
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc
and x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.quality, x.cost

end





select
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, isnull(y.hint, '') hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade,
oprId, @pLongest routepfx, @pRouteName routename
--into #all Deleted By N Mohamed 051129
from #route x, r_interface y with (nolock) ---, r_interfaceGroup ig with (nolock)
where x.oprId=y.id
and x.quality < 10
--and ig.tintid=x.oprId
--and ig.intgroupid=@poperatorGroup
--and ig.flag=1 and x.isactive=1
and @pLongest like x.prefixcode+'%'
order by seqno+[calcException]
--order by x.quality, seqno+[calcException], x.cost




--drop table #route
--drop table #timecls_selected
--drop table #operator_selected
end





GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 2 Replies View Related

Working Days Only Date

Jan 29, 2015

I need to calculate a date.example it needs to be 20 working days ago compared to today so that means it needs to not include any Saturday or Sunday in between

declare @start_date datetime
declare @end_date datetime
declare @working_days int
set @working_days = 20

[code]...

So I need to calculate @start_date but it needs to exclude any weekend days.@working_days is the number of working day I'm interested in.

View 1 Replies View Related

How To Count Working Days Only Between Two Dates

Dec 16, 2013

I have the following simple SQL which counts the days difference between two date fields:

SELECT
DATEDIFF(dd,central_enquiry.enquiry_time, GETDATE()) as Days_Open

FROM
central_enquiry
---

How do I get it to exclude weekends?

I also have a table nonworkingdays which has a nonworking_date field where users can manually record national holidays and bank holidays etc.

Example date

nonworking_date
01/01/2014
18/04/2014
05/05/2014

How can I include this table in the calculation too?

View 16 Replies View Related

Analysis :: Count Working Days MDX

Nov 23, 2015

 I would like to count the working days of a spesific time range and then find the Average Daily Dispatches.Currently the time range is at WHERE statement. how to count the dates in a month range. I prefer not to add a new calc Member.

WITH MEMBER [Measures].[Working Days] AS
COUNT(Date.[Working Date].&[1])--Doesn't work
MEMBER [Measures].[Average Daily Dispatches] AS
[Measures].[Total Dispatches]/[Measures].[Working Days]
SELECT [Measures].[Average Daily Dispatches] ON 0
FROM [cube]
WHERE (
[Date].[Month].&[2015-01-01T00:00:00]:[Date].[Month].&[2015-08-01T00:00:00]
);

View 4 Replies View Related

Calculate Total Working Hours Between Days

Nov 15, 2013

I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:

Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.

View 3 Replies View Related

Retrieve Data For Working Days Using Date Function

Aug 22, 2007

Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?

Thanks...

View 2 Replies View Related

Transact SQL :: Calculate Working Days Excluding Weekends

Jun 7, 2015

Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??

select  building_number as SchoolID,building_name as Campus,   count( distinct( CASE  WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed   from   Sales sl join Buildings b on  sl.Building_Num =b.Building_number join students2 s on  s.Student_Number= sl.Student_Num   join Sale_Items SI on   si.UID = sl.UID   where  CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and   '2015-05-07'      and VoidReview <> 'v' and  SI.INum = '1'    group by  building_number,building_name order by building_number,Building_Name;

View 8 Replies View Related

Calculate Number Of Working Days Back From A Date

May 6, 2008

Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)

I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse

declare @WorkingDate as datetime

SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)

-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)

Hope someone can help

Thanks

View 3 Replies View Related

Analysis :: To Include Only Working Days In SSAS Calculations

Jul 28, 2015

I am trying to compute average of Sales amount for 10 days and I am having an issue when using LAG function. Since weekends fall in between working days my LAG (10) function is also including weekends and hence I am not able to get the correct average.

For ex: D1 - 10 , D2 - 20, D3 - 30,  D4 - 50, D5 - 10, D6 - W, D7 - W, D8-10, D9 - 20, D10 -30, D11 - 10, D12 - 40, D13 - W, D14 - W

I am using a sample script

SUM([Time].[Hierarchy].CurrentMEMBER : Time.[Hierarchy].Currentmember.Lag(9), [Measures].[Sales Amount])/10

When I use this and When my current member is D12 Then I am getting the value as 200/10 = 20 (from D12 to D3).

However ideally it should be 230/10  =23 (from D12 to D1) and lag should exclude D6 and D7 as it is a weekend.

I also have a working day flag in my time dimensions which says 0 for non working days and 1 for working days.

View 2 Replies View Related

T-SQL (SS2K8) :: Generate Working Schedule For Employees For X-days Ahead Based On Starting Date

May 6, 2014

I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.

I have got 3 relevant tables:

1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)

2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)

3. Table Z with resource data.

An example to clarify (for fake employee 100):

Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)

Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):

Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)

The weekend days show as 00:00 for the hours (same as day 8 in example)

I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.

I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...

I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):

2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6

With this done I can compare the actual daynumber with the daynumber in Table Y.

The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.

View 0 Replies View Related

Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date

Oct 28, 2015

i have written a sql function which returns only number of working days (excludes holidays and Weekends)  between given StartDate  and EndDate.
                 
USE [XXX]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateNumberOFWorkDays]    Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO

[code]...

I need  a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date.  Example scenario:  If i give date as  12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table  tblMasHolidayList where all the 2015 year holidays dates and info are stored.

View 18 Replies View Related

SQL Server - Select Records Added Today, Last 3 Days, 7 Days...

Oct 25, 2006

Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly).   -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out?  Thanks so much in advance.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved