-1

I'm creating a system to calculate user working days.

Suppose user 1 has been at work from 2020-02-01 to 2020-02-20.

And user 2 has been at work from 2020-02-10 to 2020-02-15

In Sql Server

I have something similar in the table below

enter image description here

Now i want to calculte count of user working days between 2 date

For Example

Select Sum(DateDiff(Day,StartDate,EndDate)) From Table1 Where StartDate >= '2020-02-08' And EndDate <= '2020-02-12'

Above query returns 0 . But in this date user 1 has 5 working days and user 2 has 3 working days.

How can i calculate this?

I want a similar answer below:

enter image description here

topcool
  • 2,498
  • 7
  • 28
  • 52
  • Does this answer your question? [SQL SERVER: Get total days between two dates](https://stackoverflow.com/questions/6068017/sql-server-get-total-days-between-two-dates) – RiggsFolly Jun 26 '20 at 10:44
  • Or this https://stackoverflow.com/questions/14749877/t-sql-get-number-of-working-days-between-2-dates – RiggsFolly Jun 26 '20 at 10:47
  • Define "working days" please. Monday to Friday? something else? Anyway, this has definitely been asked and answered many times either way. – underscore_d Jun 26 '20 at 10:51
  • Does this answer your question? [T-SQL get number of working days between 2 dates](https://stackoverflow.com/questions/14749877/t-sql-get-number-of-working-days-between-2-dates) – underscore_d Jun 26 '20 at 10:52
  • @underscore_d No All days that user has been in `startdate` and `enddate`. Holidays are not important here – topcool Jun 26 '20 at 10:53
  • Explain your logic. Both users should have the same working day values since their start and end dates OVERLAP the entire date period (Feb 8 ~ Feb 12) of interest. – SMor Jun 26 '20 at 11:49

1 Answers1

1

You can use datediff() -- after testing for the limits of the period that you want:

select t.*,
       datediff(day,
                case when startdate < '2020-02-08' then '2020-02-08' else startdate end),
                case when enddate > '2020-02-12' then '2020-02-12' else startdate end)
               ) + 1
from t;

The + 1 is because you are including the last day in the calculation.

Note: This only works correctly when there is an overlap. You want a filter:

(enddate >= '2020-02-08' or startdate <= '2020-02-12')

Based on the question, I'm not sure if this should be in a WHERE clause or a CASE expression. That is, do you want to filter out non-overlaps or do you want them to appear as 0/NULL?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786