# Calculate several timedifference give different output

I have the following table :

entity_id time_c next_time_c
1 ‘2023-01-02 10:34:36’ ‘2023-01-10 15:12:24’
2 ‘2023-03-01 16:10:12’ ‘2023-03-20 22:47:59’

My goal is to calculate the interval between `time_c` and `next_time_c` without Saturday and Sunday and the full interval.

I come up with the following query :

``````WITH parms (entity_id, start_date, end_date) AS
(
SELECT
entity_id,
time_c::timestamp,
next_time_c::timestamp
FROM
test_c
), weekend_days (wkend) AS
(
SELECT
SUM(CASE WHEN EXTRACT(isodow FROM d) IN (6, 7) THEN 1 ELSE 0 END)
FROM
parms
CROSS JOIN
generate_series(start_date, end_date, interval '1 day') dn(d)
)
SELECT
entity_id AS "ID",
CONCAT(
extract(day from diff), ' days ',
extract( hours from diff)   , ' hours ',
extract( minutes from diff) , ' minutes ',
extract( seconds from diff)::int , ' seconds '
) AS "Duration (excluding saturday and sunday)",
justify_interval(end_date::timestamp - start_date::timestamp) AS "Duration full"
FROM (
SELECT
start_date,
end_date,
entity_id,
(end_date-start_date) - (wkend * interval '1 day') AS diff
FROM parms
JOIN weekend_days ON true
) sq;
``````

It works well when I have only 1 row in my table. When there is more than 1 row, the result is wrong.

With 1 row :

ID Duration (excluding saturday and sunday) Duration full
1 6 days 4 hours 37 minutes 48 seconds {"days":8,"hours":4,"minutes":37,"seconds":48}
ID Duration (excluding saturday and sunday) Duration full
2 13 days 6 hours 37 minutes 47 seconds {"days":19,"hours":6,"minutes":37,"seconds":47}

With 2 rows :

ID Duration (excluding saturday and sunday) Duration full
1 0 days 4 hours 37 minutes 48 seconds {"days":8,"hours":4,"minutes":37,"seconds":48}
2 11 days 6 hours 37 minutes 47 seconds {"days":19,"hours":6,"minutes":37,"seconds":47}

Why the result is false when calculating several rows ?

### >Solution :

You should calculate weekend days for each `entity_id` separately:

``````...
SELECT
entity_id,
SUM(case when extract(isodow from d) in (6, 7) then 1 else 0 end)
FROM
parms
CROSS JOIN
generate_series(start_date, end_date, interval '1 day') dn(d)
GROUP BY entity_id
...
``````

Check the full query in Db-Fiddle.