Is there a way to count days INSIDE a range of dates?

Question

I'm quite a beginner on VB/SQL, I just began my learning few months ago, but I can understand the logic of algorithms as I used to do some Excel VBA .

I'm actually designing a database where I can (wish to) follow up every colleague's activity during the year.

The objective is to have a (Monthly) ratio of => Billable days / (Billable + Non Billable - Absent)

The context : A single person can be : Working internally (Non billable), OR Working Externally (Billable) , OR on Holidays (Absent).

I have a [Planning] Table where it stores the following data : [Consultant_ID] (linked to another table [Consultant], [Activity] (A list with the three choices described above), [Beginning_Date], [End_Date].

Example : Consultant 1 : Working externally from 01/01/2019 to 01/06/2019, Working internally from 02/06/2019 to 31/12/2019, Holidays from 02/03/2019 to 15/03/2019

Is there a way to have the Billable ratio of March for example ?

I created 4 queries (Maybe too much ?) 3 queries : [Consultant_ID] [Activity] [Beginning_Date] [End_Date] [Ratio : Datediff("d";[Beginning_Date];[End_Date]).

For each query : The [Activity criteria] : one Working Internally, one Working Externally, one Absent.

And for the [Beginning_Date] and [End_Date] criterias : <=[Enter beginning date], >=[Enter End date]

And the 4th query [Consultant ID] [Billable] [Non billable] [Absent] (and planning to add the [RATIO]).

Problem is : the Datediff counts the dates of the whole activity of what it finds, and not only the dates between 01/03/2019 and 31/03/2019 as I wish to.

I Expect the output of the ratio to be : Billable days / (Billable + Non Billable - Absent) of the desired period.

The actual output shows the billable, non billable, and absent days of the whole period between the dates which are inputted

So instead of 31 Billable, 0 Non billable, 15 Absent It shows 180 Billable, 0 Non Billable, 32 Absent

Sorry for the long post, it is actually my first, and thank you very much ! I've been struggling with this for a whole week

Very long and very broad question. The answer is "Probably". The issue is the date criteria are not recognized? — June7, Mar 26 '19 at 18:51
Please share with us the actual sql of the query 1 for WorkingInternally and query 4. Otherwise we are just guessing and shooting in the dark as what to recommend. — donPablo, Mar 26 '19 at 21:27
@June7 It is recognized, but the datediff takes the whole duration, while i only want to calculate the ratio for a specific month, or week for example — Mao Guenn, Mar 27 '19 at 09:24

donPablo · Accepted Answer · 2019-03-26T22:31:44.250

We first need to figure out the maxBegin and the minEnd dates for each row

 SELECT
       *,
        (IIF (Beginning_Date > #3/1/2019#, Beginning_Date, #3/1/2019#)   ) as maxBegin,
        (IIF (End_Date < #3/31/2019#, End_Date, #3/31/2019#)   ) as minEnd,
        Datediff("d", maxBegin, minEnd) + 1 as theDiff
     FROM Planning
     Where Beginning_Date <= #3/31/2019#  AND End_Date >= #3/1/2019#

Then use that to compute the durations. Note: DateDiff does not count both ends, so we need to add +1.

SELECT
   Consultant_ID,
   SUM(IIF (Activity = "Working Internally", Datediff("d", maxBegin, minEnd) +1, 0) ) as NonBillable,
   SUM(IIF (Activity = "Working Externally", Datediff("d", maxBegin, minEnd) +1, 0) ) as Billable,
   SUM(IIF (Activity = "Holidays", Datediff("d", maxBegin, minEnd) +1, 0) ) as Absent
FROM
   (
     SELECT
       *,
        (IIF (Beginning_Date > #3/1/2019#, Beginning_Date, #3/1/2019#)   ) as maxBegin,
        (IIF (End_Date < #3/31/2019#, End_Date, #3/31/2019#)   ) as minEnd
     FROM Planning
     Where Beginning_Date <= #3/31/2019#  AND End_Date >= #3/1/2019#
   ) as z

GROUP BY Planning.Consultant_ID;

Finally, you need to substitute the actual Begin/End dates via params into the sql to run your query. Also note that the Holidays are only 14, not 15.

Also, you can add the Ratio calculation right into this sql, and have only one query.

I just tried your code, it works perfectly fine with few adjustments (on some elements names related to my database) Thank you very much for your answer, it is very clear, even for a beginner like me ! — Mao Guenn, Mar 27 '19 at 11:04

Is there a way to count days INSIDE a range of dates?

1 Answers1