0

I have two tables:

[Date Master] is table with date and an additional column [Workday], with that column we can decide that is that a real working day or not.

+-------------------------------------+--+---+----------+
|             Master Date             |  |   |  Workday |
+-------------------------------------+--+---+----------+
|                                     |  |   |          |
| 2020-03-16 00:00:00.000             |  |   |        1 |
| 2020-03-17 00:00:00.000             |  |   |        1 |
| 2020-03-18 00:00:00.000             |  |   |        1 |
| 2020-03-19 00:00:00.000             |  |   |        1 |
| 2020-03-20 00:00:00.000             |  |   |        1 |
| 2020-03-21 00:00:00.000             |  |   |        0 |
| 2020-03-22 00:00:00.000             |  |   |        0 |
| 2020-03-23 00:00:00.000             |  |   |        1 |
| 2020-03-24 00:00:00.000             |  |   |        1 |
| 2020-03-25 00:00:00.000             |  |   |        1 | 
| 2020-03-26 00:00:00.000             |  |   |        1 |
| 2020-03-27 00:00:00.000             |  |   |        1 |
| 2020-03-28 00:00:00.000             |  |   |        0 |
| 2020-03-29 00:00:00.000             |  |   |        0 |
| 2020-03-30 00:00:00.000             |  |   |        1 |
| 2020-03-31 00:00:00.000             |  |   |        1 |
+-------------------------------------+--+---+----------+

The second table [MAIN] is a kind of performance table where we store various colleagues' attendances in the office.

+-----------------------------------------+--+--+--+------------------------+----------------------+
|                      ID                 |  |  |  |     Start Date         |       End Date       |
+-----------------------------------------+--+--+--+------------------------+----------------------+
| ---------------------- ---------- ------|  |  |  |                        |                      |
|                    528950               |  |  |  |     2020-03-19         |      2020-03-23      |
+-----------------------------------------+--+--+--+------------------------+----------------------+

I wrote a select, which should show the difference between the [Start Date] and the [End Date] with using the previously mentioned [Workday] column value.

SELECT 
    [ID],
    DATEDIFF(dd, ([Start Date]), [End Date]) AS 'Start Date - End Date (Business Days)'
FROM 
    Main 
LEFT JOIN 
    Date_Master ON [Master Date] BETWEEN [Start Date] AND [End Date]
WHERE 
    Workday = 1 
    AND [ID] = '528950'

And here comes the funny part: this select returns 4 days:


+-----------------------------------------------+-----------------------------------------+
|                      ID                       |   Start Date - End Date (Business Days) |
+-----------------------------------------------+-----------------------------------------+
| ------- ------------------------------------- |                                         |
|                    528950                     |                   4                     |
+-----------------------------------------------+-----------------------------------------+

but if I start to calculate it manually I get 3 days:

+-------------------------+---------+
|       Master Date       | Workday |
+-------------------------+---------+
| 2020-03-19 00:00:00.000 |       1 |
| 2020-03-20 00:00:00.000 |       1 |
| 2020-03-21 00:00:00.000 |       0 |
| 2020-03-22 00:00:00.000 |       0 |
| 2020-03-23 00:00:00.000 |       1 |
+-------------------------+---------+

What am I doing wrong? Maybe it is so easy, but I am stuck in my line of thinking.

Thank you in advance.

Dale K
  • 25,246
  • 15
  • 42
  • 71
8indie8
  • 69
  • 2
  • 8
  • `4` is correct, there *are* 4 days between `2020-03-19` and `2020-03-23`, there's nothing "funny" about it; `23 - 19 = 4` ergo 4 days. If you want to calculate the nothing of working days between 2 different dates, you can't use `DATEDIFF` on it's own. – Thom A Sep 09 '20 at 08:24
  • 2
    Does this answer your question? [Count work days between two dates](https://stackoverflow.com/q/252519/2029983). – Thom A Sep 09 '20 at 08:25
  • Ahhhh got it... because the select will always get the 19th as start and 23th as an end and the datediff doesn't care about with the Workday columns value. Thank you @Larnu you helped me and you recommendation worked with some modifications. – 8indie8 Sep 09 '20 at 08:43

0 Answers0