Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version from rdb$database;


VERSION


3.0.5




-- https://stackoverflow.com/questions/60030543/rebuild-sql-query-to-sum-date-from-two-tables

create table KPS1 (
  ID integer not null,
  DATE_FROM date not null,
  DATE_TO date not null
)


create table KPS2 (
  ID integer not null,
  DATE_FROM date not null,
  DATE_TO date not null
)


create index KPS1_workers on KPS1(id)


create index KPS2_workers on KPS2(id)


insert into KPS1 values (1, '2018-02-08', '2019-12-01')
1 rows affected

insert into KPS2 values (1, '2017-02-20', '2018-01-01')
1 rows affected

-- this data sample taked from
-- https://stackoverflow.com/questions/51551257/how-to-get-correct-year-month-and-day-in-firebird-function-datediff
insert into KPS1 values (2, '1988-09-15', '2000-03-16')
1 rows affected

insert into KPS1 values (2, '2000-03-16', '2005-02-28')
1 rows affected

select * from KPS1 union all select * from KPS2


ID
DATE_FROM
DATE_TO


1
2018-02-08
2019-12-01

2
1988-09-15
2000-03-16

2
2000-03-16
2005-02-28

1
2017-02-20
2018-01-01




-- sadly, the topic starter did not say what he wants to do with his many sources of data
-- so multiple interpretations are possible!

-- here we are counting days from the first date to the last date, one row per worker

-- finding the minimum and maximum dates from ALL the sources
--  (multitude of rows in multitude of tables)
-- very simple to write and read, it however would be bad on long tables
-- because ID indexes hidden by UNION and not available for further, outer queries
--   FULL-SCAN in natural order and post-merge external sorting would occur

Select ID as id_contact, Min(DATE_FROM) as DATE_FROM, Max(DATE_TO) as DATE_TO
From (select * from KPS1 union all select * from KPS2)
Group by ID


ID_CONTACT
DATE_FROM
DATE_TO


1
2017-02-20
2019-12-01

2
1988-09-15
2005-02-28




-- finding the minimum and maximum dates from ALL the sources
-- this one is harder to write and read
-- but should be better for execution: it allows use of indexes by ID be propagated

-- This optimized query works fine with the data presented by topic starter
-- where each source tables has exactly one row for one and the same worker.

-- It will not work so fine when some workers are missed from some tables.
-- Fixing it will make the query even more complex to write and read

Select KPS1.ID as id_contact, 
  IIF(KPS1.DATE_FROM < KPS2.DATE_FROM, KPS1.DATE_FROM, KPS2.DATE_FROM) as DATE_FROM,
  IIF(KPS2.DATE_TO < KPS2.DATE_TO, KPS2.DATE_TO, KPS1.DATE_TO) as DATE_TO
From KPS1, KPS2
Where KPS1.ID = KPS2.ID


ID_CONTACT
DATE_FROM
DATE_TO


1
2017-02-20
2019-12-01




Select ID_CONTACT, DateDiff(day, DATE_FROM, DATE_TO) as DAYS_COUNT From
(
  Select ID as id_contact, Min(DATE_FROM) as DATE_FROM, Max(DATE_TO) as DATE_TO
  From (select * from KPS1 union all select * from KPS2)
  Group by ID
)


ID_CONTACT
DAYS_COUNT


1
1014

2
6010




-- alternatively, here counting days per-job, many rows may happen for the same worker

select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KPS1 a
  union all
select b.*, datediff(day, b.DATE_FROM, b.DATE_TO) as DAYS_COUNT from KPS2 b


ID
DATE_FROM
DATE_TO
DAYS_COUNT


1
2018-02-08
2019-12-01
661

2
1988-09-15
2000-03-16
4200

2
2000-03-16
2005-02-28
1810

1
2017-02-20
2018-01-01
315




SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
(
  select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KPS1 a 
) 
GROUP BY ID_CONTACT

    union all
    
SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
(
  select b.*, datediff(day, b.DATE_FROM, b.DATE_TO) as DAYS_COUNT from KPS2 b
)  
GROUP BY ID_CONTACT


ID_CONTACT
DAYS_COUNT


1
661

2
6010

1
315




WITH PER_SOURCE_SUMMER AS 
(
  SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
  (
    select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KPS1 a 
  ) 
  GROUP BY ID_CONTACT

    union all
    
  SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
  (
    select b.*, datediff(day, b.DATE_FROM, b.DATE_TO) as DAYS_COUNT from KPS2 b
  )  
  GROUP BY ID_CONTACT
)

SELECT ID_CONTACT, SUM(Days_Count) as DAYS_COUNT 
FROM PER_SOURCE_SUMMER
GROUP BY 1


ID_CONTACT
DAYS_COUNT


1
976

2
6010




-- Now, having TWO interpretations of the task and TWO implementations of days counter
-- we finally can come up with conversion from precise but hard to feel DAYS 
-- to imprecise but easy to digest Y-M-D

WITH SOURCE_MIN_MAX AS
(
  Select ID_CONTACT, DateDiff(day, DATE_FROM, DATE_TO) as DAYS_COUNT From
  (
    Select ID as id_contact, Min(DATE_FROM) as DATE_FROM, Max(DATE_TO) as DATE_TO
    From (select * from KPS1 union all select * from KPS2)
    Group by ID
  )
),

PER_SOURCE_SUMMER AS 
(
  SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
  (
    select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KPS1 a 
  ) 
  GROUP BY ID_CONTACT

    union all
    
  SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
  (
    select b.*, datediff(day, b.DATE_FROM, b.DATE_TO) as DAYS_COUNT from KPS2 b
  )  
  GROUP BY ID_CONTACT
),

SOURCE_PER_JOB AS
(
  SELECT ID_CONTACT, SUM(Days_Count) as DAYS_COUNT 
  FROM PER_SOURCE_SUMMER
  GROUP BY 1
),

KP_DAYS AS
(
  SELECT 1 as METHOD, A.* FROM SOURCE_MIN_MAX A
     union all
  SELECT 2 as METHOD, B.* FROM SOURCE_PER_JOB B
)

SELECT * from KP_DAYS


METHOD
ID_CONTACT
DAYS_COUNT


1
1
1014

1
2
6010

2
1
976

2
2
6010




WITH SOURCE_MIN_MAX AS
(
  Select ID_CONTACT, DateDiff(day, DATE_FROM, DATE_TO) as DAYS_COUNT From
  (
    Select ID as id_contact, Min(DATE_FROM) as DATE_FROM, Max(DATE_TO) as DATE_TO
    From (select * from KPS1 union all select * from KPS2)
    Group by ID
  )
),

PER_SOURCE_SUMMER AS 
(
  SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
  (
    select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KPS1 a 
  ) 
  GROUP BY ID_CONTACT

    union all
    
  SELECT ID as ID_CONTACT, SUM(Days_Count) as DAYS_COUNT FROM
  (
    select b.*, datediff(day, b.DATE_FROM, b.DATE_TO) as DAYS_COUNT from KPS2 b
  )  
  GROUP BY ID_CONTACT
),

SOURCE_PER_JOB AS
(
  SELECT ID_CONTACT, SUM(Days_Count) as DAYS_COUNT 
  FROM PER_SOURCE_SUMMER
  GROUP BY 1
),

KP_DAYS AS
(
  SELECT 1 as METHOD, A.* FROM SOURCE_MIN_MAX A
     union all
  SELECT 2 as METHOD, B.* FROM SOURCE_PER_JOB B
)

SELECT
    KP_DAYS.method, KP_DAYS.id_contact, KP_DAYS.days_count,
    FLOOR(KP_DAYS.DAYS_COUNT / 365.25) AS Y
  , FLOOR( (KP_DAYS.DAYS_COUNT - (FLOOR(KP_DAYS.DAYS_COUNT / 365.25) * 365.25) ) / 30.5) AS M 
  , CAST(MOD((KP_DAYS.DAYS_COUNT) - (((KP_DAYS.DAYS_COUNT) / 365.25) * 365.25), 30.5) AS INTEGER) AS D
FROM KP_DAYS


METHOD
ID_CONTACT
DAYS_COUNT
Y
M
D


1
1
1014
2
9
2

1
2
6010
16
5
2

2
1
976
2
8
1

2
2
6010
16
5
2
ID	DATE_FROM	DATE_TO
1	2018-02-08	2019-12-01
2	1988-09-15	2000-03-16
2	2000-03-16	2005-02-28
1	2017-02-20	2018-01-01