Calculate the duration between two dates based on holiday from calendar table-CodePudding

I have two tables : Calendar and Request_Stages:

I want to calculate the duration between DATE)FROM and DATE_TO for each request stage.

The desired result:

What I have tried:

  SELECT Req.requestID, Req.STAGE_ID COUNT(DAT.holiday) as duration

  FROM REQUEST Req

  JOIN [dbo].[STG_ACCR_DATE] DAT
  ON DAT.DATE >= Req.DATE_FROM
  AND DAT.FDATE <= Req.DATE_TO
  WHERE DAT.OFF_DAY = 0 --TO CALCULATE ONLY WORKING DAYS
  GROUP BY Req.request_ID, Req.STAGE_ID
  ORDER BY Req.request_ID, Req.STAGE_ID

The problem with my current result:

it doesn't SHOW the stages with zero working day, for example if a stage start date and end date are equal, the desired result is one '1' working day, but my query is returning zero '0' and doesn't show it in the results with this issue, stages records are lost.

Any suggestion to fix my query or new solution idea are appreciate it, probably I am thinking wrong, so any solution is welcome.

CodePudding user response：

The posted query doesn't really match up with the problem as presented.

select *,
    datediff(day, DATE_FROM, DATE_TO)   1 - (
        select count(*) from CALENDAR as c
        where c.DATE between rs.DATE_FROM and rs.DATE_TO and c.IS_HOLIDAY = 1
    ) as DURATION
from REQUEST_STAGES as rs