Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Summing 2 Dates but only Workdays Expand / Collapse
Author
Message
Posted Sunday, August 5, 2012 2:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 7:06 PM
Points: 15, Visits: 42
Thanks for the help yesterday. Today I am looking to sum 2 dates but only workdays. So if I have 8/1/2012 to 8/9/2012. I am looking for 7 days. Workweek is Monday to Friday! Thanks in advance!
Post #1340293
Posted Sunday, August 5, 2012 4:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,894, Visits: 7,138
What have you tried so far? Please post your code...

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1340308
Posted Sunday, August 5, 2012 6:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 7:06 PM
Points: 15, Visits: 42
Here is what I have so far.

Select person.id as ID_Number,lastname,firstname, count(datediff(day,jobstartdate,jobenddate)) as Absent_Count
from Person
inner join job
on person.id =job.employeeid
where purgeid = 0
group by Id_Number, lastname, firstname
order by ID_Number asc

Post #1340318
Posted Monday, August 6, 2012 4:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 4:37 AM
Points: 8, Visits: 45
The issue of working days is much larger than just weekends.
There are national holidays as well, plus unexpected "holidays" like extreme weather conditions and such.

In our app we have separate tables for 3 types of "non-working days" and we use our own SQL functions to make working day calculations.
This is a bit tricky with regards to what these functions return when one of the arguments is a non-working day. For example Monday 17th minus Friday 14th is one working day. But what should Monday 17th minus Sunday 16th return? Zero or One?

There is some calculus problem here. If Sat-Fri = 0 and Sun-Sat = 0 and Mon-Sun = 0 then (Sat - Fri) + (Sun-Sat) + (Mon-Sun) = (Mon-Fri) = 0. Oupsss...
The same issue appears if you define (Sat-Fri=1).

It is a tough call, but if you ignore theory and stick to practice, here is how we decided to do it:

PRINT Calendar.fn_AddWorkingDays('2012/08/05', 1) ==> Aug  6 2012 12:00AM
PRINT Calendar.fn_AddWorkingDays('2012/08/04', 1) ==> Aug 6 2012 12:00AM
PRINT Calendar.fn_AddWorkingDays('2012/08/03', 1) ==> Aug 6 2012 12:00AM
PRINT Calendar.fn_DiffWorkingDays('2012/08/06', '2012/08/05') ==> 0
PRINT Calendar.fn_DiffWorkingDays('2012/08/05', '2012/08/04') ==> 0
PRINT Calendar.fn_DiffWorkingDays('2012/08/04', '2012/08/03') ==> 0

Also remember another dirty detail. Your app must NOT go crazy if the company decides to work on a Saturday Make sure you don't produce too many unexpected results. For example what should (Sat + 0) return?
Post #1340495
Posted Monday, August 6, 2012 7:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:04 AM
Points: 3,670, Visits: 72,433
Add a calendar table with a working_day field type int =1 on working days, and 0 on non-working days.

i.e.
CREATE TABLE dbo.calendar(
calendar_date date NOT NULL PRIMARY KEY,
working_day INT NOT NULL DEFAULT 1)

Holidays, and weekends, and emergency closed days get a 0 for the working_day

If you need to count partial working days, replace the INT with the appropriate numeric data type to handle the most granular fraction of a day you need to deal with.

then get the business to determine if you count the days between, include the starting day, and/or include the ending day.

then it comes down to the following for days between

SELECT
working_days = sum(working_day)
FROM
calendar
where
calendar_date > @start_date and calendar_date < @end_date

OR for days between + end day

SELECT
working_days = sum(working_day)
FROM
calendar
where
calendar_date > @start_date and calendar_date <= @end_date

OR for days between + start day
SELECT
working_days = sum(working_day)
FROM
calendar
where
calendar_date >= @start_date and calendar_date < @end_date

OR for days between + both start and end
SELECT
working_days = sum(working_day)
FROM
calendar
where
calendar_date >= @start_date and calendar_date <= @end_date

Obviously you need to join to the calendar table, but this will make things easy for you.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1340614
Posted Monday, August 6, 2012 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 4:37 AM
Points: 8, Visits: 45
I never liked solutions with precomputed data, but it seems that performance should take priority over my taste

How do you fill in the calendar table?
Fill it once in advance for the next 200 years and then update single entries as changes happen?
Coz something like that is about the only way it will work 100% right, that is the only practical way to guarantee to your app that it will not try to access a date that is "out of range".

Even that is no big deal of course, since 200 years is about 70K records. The performance will probably be mind-blasting compared to the "calculate every time" approach we use use right now. Plus it allows you to directly switch a non-working day to a working day in no time and that's cool
Post #1340646
Posted Monday, August 6, 2012 8:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:04 AM
Points: 3,670, Visits: 72,433
I fill in the calendar table with a dump from an 11,000 integer tally table. It reaches out about 30 years. I have a process that checks nightly if the last date in the calendar table is within 5 years. When that happens, it adds another 11,000 rows.

Why 11,000? Because Jeff Moden's Tally table has 11,000 integers in it... and I just use that for the basic population.

My calendar table started as a dimension in my datawarehouse. I have a lot more information in it than just the date and if it's a working day.

I have reasons why mine is structured as it is (for instance instead of putting a 1 or a 0 in the working days, because it's a dimension I put 'Working Day' or 'Non-Working Day'. Mostly we work with the number of working hours in a day, not counting individual days, so the Holiday/Non-Holiday approach is fine for us as our calculations are based off of the working hours in a day columns.

but it looks like this. Most of the work happens with the actual value columns, the computed columns are there so that when people build reports they can set them to automatically run for today, yesterday, last week, etc and the computed columns will get the right dates.

CREATE TABLE [dbo].[dim_calendar](
[Calendar_Key] [int] IDENTITY(1,1) NOT NULL,
[Calendar_Date] [date] NOT NULL,
[US_Holiday_Indicator] [varchar](15) NULL,
[CA_Holiday_Indicator] [varchar](15) NULL,
[US_Working_Days_In_Month] [int] NOT NULL,
[CA_Working_Days_In_Month] [int] NOT NULL,
[US_Working_Hours_In_Day] [int] NOT NULL,
[CA_Working_Hours_In_Day] [int] NOT NULL,
[Week_Ending_Date] AS (dateadd(day,(7)-datepart(weekday,[Calendar_Date]),[Calendar_date])),
[Year_Numeric] AS (datepart(year,[calendar_date])),
[Year_Text] AS (datename(year,[Calendar_Date])),
[Quarter_Numeric] AS (datepart(quarter,[Calendar_Date])),
[Quarter_Smart_key] AS (datepart(year,[Calendar_Date])*(100)+datepart(quarter,[Calendar_date])),
[Quarter_Text_QQYYYY] AS ((('Q'+datename(quarter,[Calendar_Date]))+' ')+datename(year,[Calendar_Date])),
[Quarter_Text_YYYYQQ] AS ((datename(year,[Calendar_Date])+' Q')+datename(quarter,[Calendar_Date])),
[Month_Numeric] AS (datepart(month,[Calendar_Date])),
[Month_Smart_Key] AS (datepart(year,[Calendar_Date])*(100)+datepart(month,[Calendar_Date])),
[Month_Text] AS ((datename(month,[Calendar_Date])+' ')+datename(year,[CAlendar_Date])),
[Day_Numeric] AS (datepart(day,[Calendar_Date])),
[Day_Smart_Key] AS (((datepart(year,[Calendar_Date])*(1000000)+datepart(quarter,[Calendar_Date])*(10000))+datepart(month,[Calendar_Date])*(100))+datepart(day,[Calendar_Date])),
[Day_In_Week] AS (datepart(weekday,[Calendar_Date])),
[Day_Text] AS ((((datename(month,[Calendar_Date])+' ')+datename(day,[Calendar_Date]))+', ')+datename(year,[Calendar_Date])),
[Day_Name] AS (datename(weekday,[Calendar_Date])),
[Day_Type] AS (case when datename(weekday,[Calendar_Date])='Sunday' OR datename(weekday,[Calendar_Date])='Saturday' then 'Weekend' else 'Weekday' end),
[Thirty_Day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(30) else datediff(day,[Calendar_Date],getdate())/(30) end),
[Thirty_Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(30)),(0))+' 30 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(30) when (0) then 'Current 30 Day Period' when (1) then 'Prior 30 Day Period' when (2) then 'Tertiary 30 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(30),(0))+' 30 Day Periods Ago' end end),
[Ninety_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(90) else datediff(day,[Calendar_Date],getdate())/(90) end),
[Ninety_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(90)),(0))+' 90 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(90) when (0) then 'Current 90 Day Period' when (1) then 'Prior 90 Day Period' when (2) then 'Tertiary 90 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(90),(0))+' 90 Day Periods Ago' end end),
[Threehundredsixtyfive_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(365) else datediff(day,[Calendar_Date],getdate())/(365) end),
[Threehundredsixtyfive_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(365)),(0))+' 365 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(365) when (0) then 'Current 365 Day Period' when (1) then 'Prior 365 Day Period' when (2) then 'Tertiary 365 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(365),(0))+' 365 Day Periods Ago' end end),
[Six_Month_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(month,[Calendar_Date],getdate())/(6) else datediff(month,[Calendar_Date],getdate())/(6) end),
[Six_Month_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(month,[Calendar_Date],getdate())/(6)),(0))+' 6 Month Period(s) in the Future' else case datediff(month,[Calendar_Date],getdate())/(6) when (0) then 'Current 6 Month Period' when (1) then 'Prior 6 Month Period' when (2) then 'Tertiary 6 Month Period' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate())/(6),(0))+' 6 Month Periods Ago' end end),
[Quarters_Periods] AS (datediff(quarter,[Calendar_Date],getdate())),
[Quarters_Periods_Text] AS (case when datediff(quarter,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(quarter,[Calendar_Date],getdate())),(0))+' Quarter(s) in the future' else case datediff(quarter,[Calendar_Date],getdate()) when (0) then 'Current Quarter' when (1) then 'Prior Quarter' when (2) then 'Tertiary Quarter' else CONVERT([varchar],datediff(quarter,[Calendar_Date],getdate()),(0))+' Quarters Ago' end end),
[Months_Periods] AS (datediff(month,[Calendar_Date],getdate())),
[Months_Periods_Text] AS (case when datediff(month,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(month,[Calendar_Date],getdate())),(0))+' Month(s) in the Future' else case datediff(month,[Calendar_Date],getdate()) when (0) then 'Current Month' when (1) then 'Prior Month' when (2) then 'Tertiary Month' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate()),(0))+' Months Ago' end end),
[Day_Periods] AS (datediff(day,[Calendar_Date],getdate())),
[Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then case datediff(day,[Calendar_Date],getdate()) when (-1) then 'Tomorrow' else CONVERT([varchar],abs(datediff(day,[Calendar_Date],getdate())),(0))+' Days in the future' end when datediff(day,[Calendar_Date],getdate())=(0) then 'Today' when datediff(day,[Calendar_Date],getdate())=(1) then 'Yesterday' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate()),(0))+' Days Ago' end),
[Week_Periods] AS (datediff(week,[Calendar_Date],getdate())),
[Week_Periods_Text] AS (case when datediff(week,[Calendar_Date],getdate())<(0) then case datediff(week,[Calendar_Date],getdate()) when (-1) then 'Next Week' else CONVERT([varchar],abs(datediff(week,[Calendar_Date],getdate())),(0))+' Weeks in the future' end when datediff(week,[Calendar_Date],getdate())=(0) then 'This Week' when datediff(week,[Calendar_Date],getdate())=(1) then 'Last Week' else CONVERT([varchar],datediff(week,[Calendar_Date],getdate()),(0))+' Weeks Ago' end),
[Days_In_Month] AS (datediff(day,dateadd(month,datediff(month,(0),[Calendar_Date]),(0)),dateadd(month,(1)+datediff(month,(0),[Calendar_Date]),(0)))),
[day_number_in_month] AS (datepart(day,[calendar_date])),
[Quarter_text] AS ('Q'+datename(quarter,[Calendar_Date])),
[Month_name] AS (datename(month,[Calendar_Date])),
[Day_number_text] AS (datename(day,[calendar_date])),
[Year_period_numeric] AS (datediff(year,[Calendar_Date],getdate())),
[Year_period_text] AS (case datediff(year,[Calendar_Date],getdate()) when (0) then 'Current Year' when (1) then 'Last Year' when (2) then 'Prior Year' when (3) then 'Tertiary Year' when (-1) then 'Next Year' else case when datediff(year,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(year,[Calendar_Date],getdate())),0)+' Years From Now' else CONVERT([varchar],abs(datediff(year,[Calendar_Date],getdate())),0)+' Years Ago' end end),
[month_end] AS (CONVERT([date],dateadd(day,(-1),dateadd(month,(1)+datediff(month,(0),[calendar_date]),(0))),0)),
[day_number_in_30_day_periods] AS (case when datediff(day,[Calendar_Date],getdate())>=(0) then (30)-datediff(day,[calendar_date],getdate())%(30) else case datediff(day,[calendar_date],getdate())%(30) when (0) then (30) else abs(datediff(day,[calendar_date],getdate())%(30)) end end),
[day_number_in_90_day_periods] AS (case when datediff(day,[calendar_date],getdate())>=(0) then (90)-datediff(day,[calendar_date],getdate()) else case datediff(day,[calendar_date],getdate())%(90) when (0) then (90) else abs(datediff(day,[calendar_date],getdate())%(90)) end end),
[day_number_in_365_day_periods] AS (case when datediff(day,[calendar_date],getdate())>=(0) then (365)-datediff(day,[calendar_date],getdate()) else case datediff(day,[calendar_date],getdate())%(365) when (0) then (365) else abs(datediff(day,[calendar_date],getdate())%(365)) end end),
[day_number_in_quarter] AS ((1)+datediff(day,CONVERT([date],((CONVERT([varchar],datepart(year,[calendar_date]),(0))+'-')+CONVERT([varchar],datepart(quarter,[calendar_date])*(3)-(2),(0)))+'-01',(0)),[calendar_date])),
CONSTRAINT [PK__dim_cale__3C52D19446486B8E] PRIMARY KEY CLUSTERED
(
[Calendar_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1340652
Posted Monday, August 6, 2012 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
Here's my version of a Calendar table;
I put a bit of time into it putting in various US holidays, which might be of some help as well.
my date range is from SQL beginning of time(1900-01-01) to getdate() + 100 years .

TallyCalendar_Complete_With_DST.txt

Mark's example has some nice calculated columns in it ; mine is not quite so thourough as his is:
the final table has a definition that looks like this:
CREATE TABLE [dbo].[TallyCalendar] ( 
[TheDate] datetime NOT NULL,
[DayOfWeek] varchar(50) NULL,
[IsHoliday] bit NULL DEFAULT ((0)),
[IsWorkHoliday] bit NULL DEFAULT ((0)),
[IsWeekDay] bit NULL DEFAULT ((0)),
[IsWeekEnd] bit NULL DEFAULT ((0)),
[IsDaylightSavings] bit NULL DEFAULT ((0)),
[HolidayName] varchar(100) NULL,
[LunarPhase] varchar(50) NULL,
[IsoWeek] int NULL,
[IsWorkDay] AS (case when [IsWorkHoliday]=(1) OR ([DayOfWeek]='Sunday' OR [DayOfWeek]='Saturday') then (0) else (1) end) PERSISTED,
[JulianDay] AS (datediff(day,dateadd(year,datediff(year,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,
[YearNumber] AS (datepart(year,[Thedate])) PERSISTED,
[MonthNumber] AS (datediff(month,dateadd(year,datediff(year,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,
[DayNumber] AS (datediff(day,dateadd(month,datediff(month,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,
CONSTRAINT [PK__TallyCal__5CB7C64E1A14E395] PRIMARY KEY CLUSTERED (TheDate))
GO



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1340758
Posted Monday, August 6, 2012 7:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 7:06 PM
Points: 15, Visits: 42
Thanks to everyone for their help! Just learned a heck of a lot. Thanks again!
Post #1340965
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse