Best approach to get number of days from list of dates?

Transact-SQL
1

Afternoon all,

I'm wondering if someone can point me in the right direction, I have a table similar to the one below, I'm trying to write a query to get the number of days worked for a specific employee_id from the startdate and enddate.

Any help would be apreciated.

Thanks

Dave

CREATE TABLE #tempworked
(
    id INT PRIMARY KEY,
    employee_id INT NOT NULL,
    branch_id INT NOT NULL,
    startdate DATE NOT NULL,
    enddate DATE NOT NULL,
 )
 
INSERT INTO #tempworked
VALUES 
(1, 1000, 5000, 2018-06-04, 2018-06-08), -- 5 days mon to fri.
(2, 1000, 5000, 2018-06-12, 2018-06-13), -- 2 days tue & wed. 
(3, 1000, 5000, 2018-06-21, 2018-06-26), -- 4 days thurs to tues - excluding weekend.
 
(4, 1001, 5000, 2018-06-12, 2018-06-12), -- 1 day tues.
(5, 1001, 5000, 2018-07-09, 2018-07-13), -- 2 days mon & tue, thurs fri sat in future
(6, 1001, 5000, 2018-07-24, 2018-07-24) -- 0 days, tue in future
;
2

This might get you started.

Create youself a workday table, only containing the workday (you could exclude Holidays if you want).

declare @startdate date=cast('2018-01-01' as date);
create table workdays(dt date primary key);

insert into workdays(dt)
select dt
  from (select dateadd(day,row_number() over(order by (select null))-1,@startdate) as dt
          from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as wd1(n) /*    10 days */
               cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as wd2(n) /*   100 days */
               cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as wd3(n) /*  1000 days */
--               cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as wd4(n) /* 10000 days */
       ) as a
 where datediff(day,0,dt)%7 not in (5,6)
;

Now is a simple:

select distinct
       a.id
      ,a.employee_id
      ,a.branch_id
      ,a.startdate
      ,a.enddate
      ,sum(case when b.dt is null then 0 else 1 end) over(partition by a.id) as workdays
  from #tempworked as a
       left outer join workdays as b
                    on b.dt>=a.startdate
                   and b.dt<=a.enddate
                   and b.dt<=current_timestamp
;