Calculate Date Difference Excluding Bank Holidays

Aug 28, 2013

I have a Bank holiday table (ID column, and Date column), how to exclude bank holidays in the datediff returned.

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN
declare @i int
Declare @count int

[Code] .....

Calculate Date Difference Excluding Dates

Aug 27, 2012

I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. Any extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.

Create Date Table With UK & Easter Bank Holidays

May 12, 2005

Also includes ISOWeek & Weekends

Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.

So while looking at Bretts blog and another post on here, i thought i'd post this on here
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698

Special thanks to rockmoose & BOL (as always)

Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend

SET DATEFIRST 1
SET NOCOUNT ON
GO

--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
GO
--END ISOweek

--CREATE Easter algorithm function
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,
@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT

SET @A = @YEAR%19
SET @B = @YEAR / 100
SET @C = @YEAR%100
SET @D = @B / 4
SET @E = @B%4
SET @F = (@B + 8) / 25
SET @G = (@B - @F + 1) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15)%30
SET @I = @C / 4
SET @K = @C%4
SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7
SET @M = (@A + 11 * @H + 22 * @L) / 451
SET @O = 22 + @H + @L - 7 * @M

IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END

RETURN @R
END
GO
--END fnDLA_GetEasterdate

--Create the table
CREATE TABLE MyDateTable
(
FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED,
Period int,
ISOWeek int,
WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create

--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
INSERT MyDateTable(FullDate, Period, ISOWeek)
SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom)
END
GO
--End population

/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday
OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday
GO

UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7)
GROUP BY DATEPART(yy,FullDate))
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO

---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
/* End of WorkingDays UPDATE */

--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable

SET NOCOUNT OFF

Andy

Beauty is in the eyes of the beerholder

Date Difference Measurement And Weekends / Holidays

Jun 4, 2008

The below code works fine to measure the difference in days between two dates.
However, there is an additional business requirement to subtract week-ends, and holidays, from the equation.
Any ideas on how to accomplish this task, and leverage the below, existing code?  Thanks in advance!
(SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "

SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.

[Code] ......

Best Way To Handle Excluding Holidays From Report

May 18, 2015

I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

Here I have 2 Dates. CreatedDttm & ModifiedDttm.

I want  - DATEDIFF(Day,CreatedDttm,ModifiedDttm)  and I have to exclude the Weekend days from that query result.

How To Calculate A Date Difference In Days

Apr 2, 2007

Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM

How can i get the DateDifference of these two dates in days.

SQL 2012 :: How To Calculate Date Difference In Months

Jan 23, 2015

I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?

CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)

Function To Calculate Holidays

May 31, 2006

Hello,

First things first - I have a table which holds employee information (tbl_EmployeeDetails), and another table which holds information about the holidays they have booked (tbl_Holidays).

If an employee books 5 days off, 5 rows will appear in the tbl_Holidays table, each line showing 1 under the day field and 7 under the hours field.

I'm trying to produce a function which will do the following :

1) Pass in the employee number
2) Establish whether the employee works full time or part time by looking up to tbl_employeedetails, and checking the fulltime flag
3) If full time, look up to tbl_Holidays and count number of days
4) If part time, look up to tbl_Holidays and count number of hours
5) After this, return the number of holidays booked

My code is as follows :
=============
CREATE FUNCTION [dbo].[fn_Get_Booked_Holidays_Current_Year] ( @EmpNo int )

RETURNS Float
AS
BEGIN

-- Declare fields
DECLARE @FullTime int

-- Determine if Part Time or Full Time
SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails

IF @FullTime = 1
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)

IF @FullTime = 0
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)

END
==========

Can someone please let me know where I'm going wrong, or what I need to do to get this function done?

Thanks,
J

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;

SQL Server 2012 :: Difference Between Two Dates (Excluding Weekends)

May 19, 2014

I have a table with a list of jobs along with their start and end datetime values.

I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.

Example

Start Date=2014-05-15 12:00:00.000
End Date=2014-05-19 13:00:00.000

Total Time should be: 2 Days, 1 Hour and 0 Minutes

Calculate Difference?

Dec 30, 2003

I am having trouble creating a sp for the following situation:

The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:

1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763

How can I calculate the actually miles traveled during the month in a query?

TIA,

Rob

Calculate Difference Between Two Dates

Oct 18, 2006

Hi, i'm trying to calculate the number of days between two dates, but within an UPDATE statement, so far I can't wrap my head around how I can update a field with the number of days.

I was thinking something like

Code:

Update #ClaimMaster
Set covered_days = (then insert select statement that subtracts the two dates)

Does that make any sense?

Calculate Difference Between Two Rows

May 15, 2006

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why???!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Calculate Difference Between Two Records?

Aug 28, 2015

I have table that contains below data

CreatedDate               ID             Message
2015-05-29 7:00:00      AOOze            abc
2015-05-29 7:05:00      AOOze            start

2015-05-29 7:10:00      AOOze            pqy
2015-05-29 7:15:00      AOOze            lab
2015-05-29 7:20:00      AOOze            lmn

2015-05-29 7:30:00      AOOze            start
2015-05-29 7:35:00      AOOze            stop

2015-05-29 7:40:00      AOOze            pqy
2015-05-29 7:45:00      AOOze            stop
2015-05-29 7:50:00      AOOze            lmn

I need to Find Maximum interval time for between message like Start and Stop as per order of createdDate.

For example:

OccuranceCount       MinDate                    MaxDate          DurationInSeconds
1                 2015-05-29 7:05:00      2015-05-29 7:35:00   30
2                 2015-05-29 7:30:00      2015-05-29 7:45:00   15

Need Sql To Calculate Percentage Of Difference

Oct 23, 2007

so I have some data that looks like this:
semester weekOfSemester counts
Fall 2006 4 1
Fall 2007 4 6

I want to eventually graphically represent this data over the 18 weeks of the semester in terms of Fall 2007. I need to show change weather positive or negative as a percentage against Fall 2006. Can someone help with the sql?

thanks

kam

Calculate The Difference Time Between Two Times?

Oct 9, 2013

i am using this expression to get the time difference between two times.

{%Z.elapsed.time(@AK.VD.depart.date,@AK.VD.depart.time,@AK.VD.depart.date,@DV.VD.arrival.time,"hh.hh")*60} as [LOS (min)]

When Arrival time and depart time both are on same day above expression working to get the diference .

But if arrival date 2013-09-20 00:00:00.000 and arrival time 0800 and depart date 2013-09-21 00:00:00.000 and depart time 0050 when i calculate the time difference(using above expression) between these two i am getting -429.60 which is wrong. i have to get around 990.

How To Calculate Difference Between Getdate And T1 How Many Days

Oct 10, 2013

i have a table

a1
-----
id t1

How can I calculate the difference between getdate() and t1 how many days.

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

Date Comparison Excluding Time

Jan 6, 2005

I have a stored procedure that is suppose to get all records that have an expiration date of today. My where clause is as follows:

Round2ExpDate <= getdate()

This is not working because of the time factore. What is the best way to truncate the time for each date? All recommendations are greatly appreciated.

SQL 2012 :: Calculate Time Difference For Consecutive Rows?

Jul 2, 2015

I have a table like this.

CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;
INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('AAA-111', NULL, 'USER1', '2015-06-15 00:21:06', 0),
('AAA-111', '2', 'USER1', '2015-06-15 00:21:07', 0),

[code]....

Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:

- S_ACTV_CODE_PREV means the previous active records.

- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts

- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE

- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL

- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL

- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL

So the result should be as below:

S_ID S_ACTV_CODE_PREV S_ACTV_CODE_CURR S_USER S_START_TIME
S_END_TIME TIME_SPENT (in Sec)
AAA-111 NULL NULL USER1 2015-06-15 00:21:06
2015-06-15 00:21:07 1
AAA-111 NULL 2 USER1 2015-06-15 00:21:07
2015-06-17 03:20:33 183566
AAA-111 2 4 USER2 2015-06-17 03:20:33

[code]....

SQL Server 2012 :: How To Calculate Dates Difference In A Same Table

Sep 4, 2015

I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.

Each customer is uniquely identified by a customer#

I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.

How can i do that in a query?

Selecting Based On A Date Excluding The Time

May 7, 2008

Hi,

I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.

I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.

Thanks for any help.

Nvarchar Fields With Time Data - Calculate Difference In Hours

Sep 19, 2014

I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.

SQL Server 2014 :: Calculate Running Difference In Values Between Days?

Oct 13, 2015

I am trying to write a query to calculate the running difference between data on different dates. Below is what my table of data looks like. Basically I want to calculate the difference between the total_completed for each state and date.

DateStatesTotal_Completed
08/27/15CA 19,952
09/11/15CA 26,336
10/02/15CA 35,444
10/08/15CA 38,278
08/27/15CO2797
09/11/15CO3264
10/02/15CO4270
10/08/15CO4297

below is what I am trying to achieve:

DateStatesTotal_CompletedCompleted_Difference
08/27/15CA 19,952 0
09/11/15CA 26,336 6,384
10/02/15CA 35,444 9,108
10/08/15CA 38,278 2,834
08/27/15CO27970
09/11/15CO3264467
10/02/15CO42701,006
10/08/15CO429727

below is my code (I almost have what I need) I just can't figure out how show 0 as the completed_difference for the first Date for each state since there is no prior date to calculate against.

MRR_TOTALS_WEEK_OVER_WEEK AS
(
SELECT
T1.[Date]
,T1.States
,T2.Total_Completed
,ROW_NUMBER() OVER(PARTITION BY T1.States ORDER BY T1.States,T1.[Date]) AS ORDERING
FROM TOTAL_CHARTS T1
LEFT JOIN TOTAL_COMPLETED T2 ON T1.[Date] = T2.[Date] AND T1.States = T2.States
)

[code].....

Analysis :: Calculate Percentage Difference Of Two Values From Selected Years

Aug 31, 2015

Developing a measure which displays the difference of two values from the selected years.

An example : Show the difference of the sales amount from 2013 and 2015.

Since i am not really into mdx or calculated members.

Reporting Services :: How To Calculate Percentage And Difference Of Two Values In Matrix Report In Ssrs 2008

Dec 2, 2014

I am creating matrix report with grouping on WEEK and Fiscalyearweek,I need to calculate of difference between FY14W01,FY15W01 ande  percentage of those..how to calculate in ssrs level.

SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

Mar 27, 2015

I'm trying to generate a report using matrix like this

Month
Product     PreviousYearSalesAmount    SelectedYearSalesAmount      %SalesDifference

I can populate year sales amount, but i cant calculate the percentage.

Note: Month and Year are passed as parameters.

Transact SQL :: Outputting Date Difference Between Two Date

Sep 11, 2015

I require outputting the date difference between two date's if it is greater than 7(DateDiff(day, DateAdd(day, t.[Started], Nxt.started), (t.[started])) > 7).I get incorrect syntax on my operator.What is the correct code?

Calculate Date

Nov 12, 1999

Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.