SQL working days query

Question

I am trying to write a query to calculate the number of working days between 2 dates. I first tried this in VBA, which worked, but this is not very efficient.

I have 2 queries, the first works out the date difference between a valueDate and cutOffDate; the second counts the number of dates from a table of holidays/weekends that fall between the valueDate and cutOffDay.

The trouble I'm having is how to combine these 2 parts to give the item age (number of working days between the dates).

My query examples are:

SELECT allOS.SortCode, allOS.NPA, allOS.valueDate, allOS.cutOffDate, 
DateDiff("d",[allOS.valueDate],[allOS.cutOffDate]) AS Age
FROM allOS;

and

SELECT Count(Holidays.Holiday) AS NonWorkingDays 
FROM Holidays 
HAVING (([Holiday]>[#01/01/2013#] And [Holiday]<[#11/06/2013#]));

I need to subtract the result of the second query from the Age of the first query.

Sample input and output data

allOS:

sortCode|npa|valueDate|cutOffDate
111111|99999999|01-11-2013|15-11-2013
222222|77777777|04-11-2013|15-11-2013
333333|88888888|05-11-2013|15-11-2013
444444|66666666|06-11-2013|15-11-2013
555555|44444444|07-11-2013|15-11-2013
666666|33333333|12-11-2013|15-11-2013
777777|55555555|13-11-2013|15-11-2013
888888|11111111|14-11-2013|15-11-2013
999999|22222222|15-11-2013|15-11-2013

Holidays:

holiday|reason
02-11-2013|Saturday
03-11-2013|Sunday
08-11-2013|Long Weekend
09-11-2013|Saturday
10-11-2013|Sunday
11-11-2013|Long Weekend
16-11-2013|Saturday
17-11-2013|Sunday`

Result:

sortCode|npa|valueDate|cutOffDate|Age
111111|99999999|01-11-2013|15-11-2013|8
222222|77777777|04-11-2013|15-11-2013|7
333333|88888888|05-11-2013|15-11-2013|6
444444|66666666|06-11-2013|15-11-2013|5
555555|44444444|07-11-2013|15-11-2013|4
666666|33333333|12-11-2013|15-11-2013|3
777777|55555555|13-11-2013|15-11-2013|2
888888|11111111|14-11-2013|15-11-2013|1
999999|22222222|15-11-2013|15-11-2013|0

The results for age is the difference between the valueDate and cutOffDate less any of the days from the holiday table.

HansUp · Accepted Answer · 2013-11-22T19:43:39.790

You can use a correlated subquery to calculate the number of non-work days included in each valueDate and cutOffDate date range.

Here is a preliminary query I tested with your sample data, and I included the first and last rows output from that query.

SELECT
    a.sortCode,
    a.npa,
    a.valueDate,
    a.cutOffDate,
    DateDiff('d', a.valueDate, a.cutOffDate) AS raw_days,
    (
        SELECT Count(*)
        FROM Holidays
        WHERE holiday BETWEEN a.valueDate AND a.cutOffDate
    ) AS NonWorkDays
FROM allOS AS a;

sortCode npa      valueDate  cutOffDate raw_days NonWorkDays
-------- -------- ---------- ---------- -------- -----------
  111111 99999999  11/1/2013 11/15/2013       14           6
  999999 22222222 11/15/2013 11/15/2013        0           0

Notice the last row. The raw_days value is zero because both valueDate and cutOffDate are the same. If you want that to be one day, add one to the value returned by the DateDiff expression.

After you adjust that preliminary query as needed, you can use it as the data source for another query where you can calculate Age as raw_days - NonWorkDays. But I'll leave that final piece for you in case I've botched the preliminary query.

If subqueries are unfamiliar to you, I recommend two of Allen Browne's pages for useful background information: Subquery basics and Surviving Subqueries.

Also note that correlated subqueries demand extra work from the db engine. That SELECT Count(*) subquery must be run separately for each row of the table. You should have Holidays.holiday indexed to ease the db engine's burden.

brilliant. Thanks, firstly, for all your help and, secondly, for opening my eyes to the power of subqueries! I have already identified a few other places where they could help me. I have adapted the above to fit my production database so I couldn't be happier. Next time I'm stuck and post a question I'll be sure to consider what I'm asking more carefully, ensuring I provide as much info as possible. Oh, thanks for the links too. This has given me plenty more to read and experiment with! — sticks, Nov 22 '13 at 08:09

score -1 · Answer 2 · answered Nov 20 '13 at 08:56

-1

It is easy, just read about with clause. With it, you can run the first query and the second query then take the result and process it in the third query inside with clause

http://www.oracle-base.com/articles/misc/with-clause.php

answered Nov 20 '13 at 08:56

Mina Tadros

514
6
18

I have had a read at the link, however, I have been unable to get it to work. This sort of thing is not my normal day job, just covering for sickness. Is the WITH clause compatable with Access 2007? Please forgive me as I am not an experienced SQL user... trying to learn though. – sticks Nov 20 '13 at 11:34

SQL working days query

2 Answers2