Background

I've been working on some reporting views that get a multi-day work shift and are supposed to do some calculations based on data, but I'm a bit stuck here. A typical shift is either 3 calendar days usually 1 half-day and two full days, or a whole week consisting of 2 half-days (end and start) and 5 full days.

Specifications

I have the following specifications for what is a full day and half-day. These rules are based on regulation and can't be changed.

• 2 half-days != 1 full-day, the 2 halves is more "valuable"
• Given a started_at iso datetime and end_at iso datetime
• I want to get two numbers, full_days, and half_days
• A half day is
  • A day at the start of the range starting at or after 12.00
  • A day at the end of the range which ends before 19.00
• A full day is
  • A day within the range (traditional 24hours)
  • A day at the start of the range starting before 12.00
  • A day at the end of the range which ends at or after 19.00

I'm thinking either a row per full-day and half-day or an aggregated row with half_days and full_days as two separate columns would be ideal in the view to connect it with my other views.

Simplified model

I simplified the data model to leave out unnecessary columns.

create table if not exists [trip]
(
    trip_id    integer
        constraint trip_pk
            primary key,
    started_at text default (datetime('now')),
    end_at     text default (datetime('now'))
);

And I'm a bit stuck with how I should design this query. A simple time delta doesn't work.

SQLFiddle with sample data and answers: http://sqlfiddle.com/#!5/de7551/2

CodePudding user response：

You can solve this with a CTE which calculates the day span (number of days the shift spans). Since half days are always 1, 2 or 0 (only occur on end and start) we don't actually need to consider each day by itself.

You can use julianday to get the day as a number, however julian days start at noon so you'll need to subtract 0.5 to get the "actual" day for your calculation. Floor the ending day to avoid a to long span if the end time is later then the start time on each respective day, and round up the result to include partial days as a spanned day.

At this point we can calculate number of half days by checking the end and start. To get the number of full days we simply subtract the half days from the result.

with trip_spans as (
select
    ceil(julianday(end_at)-0.5 - floor(julianday(started_at)-0.5)) day_span
    , t.*
    , (
        iif(time(started_at) > time('12:00'), 1, 0)
         
        iif(time(end_at) <= time('19:00'), 1, 0)
     ) half_days
    from trip t
)
select
    trip_spans.*
    , day_span-half_days full_days
from trip_spans