Whenever there is sophisticated logic, I'd suggest to use union queries and split the logic into a select query (or even table) each. Then you'd be able to calculate this in two steps. The main difference seems to be whether the 0.5 between 00:00:00 and 00:30:00 should be counted to the previous workday or whether it should stand alone. The latter seems to be determined based on whether the end_date is also a workday itself. I see three cases:
- Next day is a workday:
- Report all hours on
start_date
- Next day is not a workday:
- Report hours from
start_date to midnight on start_date - Report hours from midnight to
end_date on end_date
I used the following example data based on your description:
create temporary table _test (user varchar(20), start_date timestamp, end_date timestamp); insert into _test values ('Alex', '7/25/2016 16:00', '7/26/2016 0:30'), ('Alex', '7/24/2016 16:00', '7/25/2016 0:30'), ('Alex', '7/21/2016 16:00', '7/22/2016 0:30'), ('Alex', '7/20/2016 16:00', '7/21/2016 0:30'), ('Alex', '7/19/2016 16:00', '7/20/2016 0:30'), ('Alex', '7/18/2016 16:00', '7/19/2016 0:30'), ('Alex', '7/17/2016 16:00', '7/18/2016 0:30'), ('Alex', '7/14/2016 16:00', '7/15/2016 0:30'), ('Alex', '7/13/2016 16:00', '7/14/2016 0:30'), ('Alex', '7/12/2016 16:00', '7/13/2016 0:30'), ('Alex', '7/11/2016 16:00', '7/12/2016 0:30'), ('Alex', '7/10/2016 16:00', '7/11/2016 0:30');
We will need to know whether the next day is a workday, so I suggest using the lead() window function (see documentation) which will give you the start_date from the next row.
create temporary table _differences as ( select user_name , start_date::date as start_date , end_date::date as end_date /** * Calculate difference in hours between start_date and end_date: */ , round(cast(datediff(seconds, start_date, end_date) as decimal)/3600,2) as hours_start_to_end /** * Calculate difference in hours between start_date and midnight: */ , round(cast(datediff(seconds, start_date, dateadd(day, 1, start_date::date)) as decimal)/3600,2) as hours_start_to_midnight /** * Calculate difference between midnight on end_date and end_date: */ , round(cast(datediff(seconds, end_date::date, end_date) as decimal)/3600,2) as hours_midnight_to_end /** * Calculate number of days from end_date until next start_date: */ , datediff(day, end_date::date, lead(start_date::date) over(partition by user_name order by start_date::date)) as days_until_next_workday from _test );
Then the following query:
select user_name as user_name , start_date as ref_date , hours_start_to_end as difference from _differences where days_until_next_workday = 0 -- report all work hours on start_date union select user_name as user_name , start_date as ref_date , hours_start_to_midnight as difference from _differences where days_until_next_workday > 0 -- report partial work hours on start_date union select user_name as user_name , end_date as ref_date , hours_midnight_to_end as difference from _differences where days_until_next_workday > 0 -- report partial work hours on end_date order by user_name , ref_date desc ;
Would yield the following result:
user_name | ref_date | difference -----------+------------+------------ Alex | 2016-07-24 | 8.50 Alex | 2016-07-22 | 0.50 Alex | 2016-07-21 | 8.00 Alex | 2016-07-20 | 8.50 Alex | 2016-07-19 | 8.50 Alex | 2016-07-18 | 8.50 Alex | 2016-07-17 | 8.50 Alex | 2016-07-15 | 0.50 Alex | 2016-07-14 | 8.00 Alex | 2016-07-13 | 8.50 Alex | 2016-07-12 | 8.50 Alex | 2016-07-11 | 8.50 Alex | 2016-07-10 | 8.50 (13 rows)
You can see that 7/25/2016 is missing because there is no start_date on or after 7/26/2016, so you'll need to figure out how to account for that special case.
8.0for the current and0.5for the following day? Is this always the case for Thursdays? Or rather when the following day does not have astart_dateof its own?