Working days grouped by month

Question

Working days grouped by month

DSC123

SSChasing Mays

Points: 628
More actions
December 1, 2016 at 7:25 am

#332480

Hi - please can someone point me in the right direction for working out the following problem in SQL.
I have 2 date fields - start and end dates and another field with number of working days. The data relates to how many working days someone was absent and I need to group this into month.
For example 20 working days total, 11 in August, 9 in September. I don't have access to low level data which would make this a lot easier.
Is this even possible using these 3 columns?
Many thanks in advance,
DSC

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63494 More actions · Answer 1

Okay... rereading this... A Calendar table won't necessarily work. I think this will only work if you know what month all of the absents occurred in. If you can't determine that, then there's not really any way to get the right answer.

Say I have a person who is absent 12 workdays in October and November. How do I know what month the person was absent?

Do you have some sample data that you could post?

Thom A SSC Guru Points: 99129 More actions · Answer 2

Have a look at this article[/url].

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

DSC123 SSChasing Mays Points: 628 More actions · Answer 3

Thanks for the reply - I'll have a read through and see if I can implement it via temp tables. Looks promising! 🙂

DSC123 SSChasing Mays Points: 628 More actions · Answer 4

Hi,

Sure, the FROMDATE field is the 1st day of the absence and the UNTILDATE field is the last day.

Example data:

FROMDATE UNTILDATEWORKDAYS

09/08/201606/09/201620

From what I've read on the below article I think it is possible, I just need to build the prerequisite tables, join on them and group on the work days column if my understanding is correct.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Some DDL and easily consumable data would help circumvent any confusion but here's some sample code to get you started. Thom referred you to a calendar table article - that's the way to go. I'm going to ignore holidays for now and assume that a "working day" is Mon-Fri. Here's some sample data and an example of how to count the "work days".

-- test data

DECLARE @yourTable TABLE (EMPID int identity, FROMDATE date, UNTILDATE date, WORKDAYS int);

INSERT @yourTable (FROMDATE, UNTILDATE)

VALUES ('20160809', '20160906'), ('20160301', '20160405');

-- How to get working days: For now we're talking about Mon-Fri

WITH

E AS (SELECT c FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(c)),

iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 FROM E a, E b, E c)

SELECT EMPID, FROMDATE, UNTILDATE, WORKDAYS = COUNT(*)

FROM @yourTable yt

CROSS APPLY

(

SELECT TOP (DATEDIFF(DAY, yt.FROMDATE, yt.UNTILDATE)+1)

DATEADD(DAY, N, FROMDATE),

DATEPART(WEEKDAY, (DATEADD(DAY, N, FROMDATE)))

FROM iTally

) dd(dt,d)

WHERE d NOT IN (1,7)

GROUP BY EMPID, FROMDATE, UNTILDATE;

Now let's say you needed to update a table to include working days....

DECLARE @yourTable TABLE (EMPID int identity, FROMDATE date, UNTILDATE date, WORKDAYS int);

INSERT @yourTable (FROMDATE, UNTILDATE)

VALUES ('20160809', '20160906'), ('20160301', '20160405');

-- Add the previous result set to a CTE and update like so

WITH

E AS (SELECT c FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(c)),

iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 FROM E a, E b, E c),

CalculateWorkDays AS

(

SELECT EMPID, FROMDATE, UNTILDATE, WORKDAYS = COUNT(*)

FROM @yourTable yt

CROSS APPLY

(

SELECT TOP (DATEDIFF(DAY, yt.FROMDATE, yt.UNTILDATE)+1)

DATEADD(DAY, N, FROMDATE),

DATEPART(WEEKDAY, (DATEADD(DAY, N, FROMDATE)))

FROM iTally

) dd(dt,d)

WHERE d NOT IN (1,7)

GROUP BY EMPID, FROMDATE, UNTILDATE

)

UPDATE @yourTable

SET WORKDAYS = cw.WORKDAYS

FROM CalculateWorkDays cw;

SELECT * FROM @yourTable;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

DSC123 SSChasing Mays Points: 628 More actions · Answer 6

Thank you so much for the example code above - I think I'm close to cracking it using the article Thom provided.

I wasn't sure if the problem was solvable so just wanted to know if the theory was possible.

Thank you all for replying and pointing me in the right direction, it's much appreciated.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

Referring to the article on the calendar table, you could add a [month] column that would makes this easy for you.

You would create the field and update it by adapting the following:

select [calendardate]

, dateadd(d,-day([calendardate])+1,[calendardate])

from calendar

From here the calculation becomes a simple matter.

select[month], count(*) numWorkDays

fromCalendar

where[calendardate]>= @fromDate and [calendardate]<=@untilDate and

workday = 1

group

by [month]

----------------------------------------------------