CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Roberto Amorim
Roberto Amorim

Reputation: 15

Counting how many days an employee stays in a function

There are two tables: trip and function

TRIP

id_employee, date_start, date_end, original_function
001, '2016-04-01', '2016-04-30', 010

FUNCTION

id_employee, cd_Function, date_start_function, date_end_function
001, 023, '2016-04-04', '2016-04-10'
001, 015, '2016-04-13', '2016-04-25'

I need to generate the following report:

Employee: id_employee

Functions---Days
010-----------12
015-----------11
023-----------07

Upvotes: 1

Views: 34

Answers (2)

Serg
Serg

Reputation: 22811

Looks like days of fuction should be substracted from days of trip original function

select id_employee, cd_function, sum(days) as days
from
   (select id_employee, original_function as cd_function,
              datediff(day, date_start, date_end) + 1 as days
    from trip
   ) 
   union all
   (select f.id_employee, x.cd_function, x.days             
    from function f
    left join trip t2 on t2.id_employee_id = f.employee_id
    outer apply (
       select f.cd_function, datediff(day, f.date_start_function, f.date_end_function) + 1 as days
       union all
       select t2.original_function as cd_function,  -(datediff(day, f.date_start_function, f.date_end_function) + 1) as days
      ) x
   )
group by id_employee, cd_function;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think you basically want to union all the rows from the two tables and then do an aggregation:

select id_employee, cd_function, sum(days) as days
from ((select id_employee, cd_function,
              datediff(day, date_start_function, date_end_function) + 1 as days
       from function
      ) union all
      (select id_employee, original_function,
              datediff(day, date_start, date_end) + 1 as days
       from trip
      )
     ) ft
group by id_employee;

Upvotes: 2

Related Questions