How to get the closing time of day in timing schedule that spans multiple days

user_336271 0
Viewed 119

Assume I have the following table called Timing:

enter image description here

Obviously each row represents a shift in a specific day.

A day can have non-overlapping multiple shifts.

If a shift spans the next day it will be splitted at midnight, and the second half would have a parent id of the first half (as you can see in row 24 and 31)

I want to query how many minutes until my day ends (the next closing time).

For instance if I'm at day 1, my day ends at day 2 - 2:00 AM (because the shift starts at day 1 - 9:00, and ends at day 2 - 2:00).

I have to be careful if there are gaps (like weekends or so). Notice there is no day 3, so the next closing time would be day 4 - 23:15 (provided that you are at day 3).

I'm mainly looking for a Linq query (Timing.Where(x=> x.close_time< .... etc).

But I'm thinking that it might be super complicated, so I'm ok with having a raw SQL query.

EDIT: This is what I got so far:

    var localTime = DateTime.Now;
    var tomorrowDay = ((int)localTime.DayOfWeek + 7 + 1) % 7;

    Timing lastShift = Timings.Where(x =>
              ((int)x.DayOfWeek) == tomorrowDay && x.ParentId != null)
              .SingleOrDefault(); // Either it is tomorrow but starts today.

    if (lastShift != null)
    {
        return Convert.ToInt32((lastShift.CloseTime - localTime.TimeOfDay).TotalMinutes);
    }

    lastShift = Timings
              .Where(x => x.DayOfWeek == localTime.DayOfWeek && x.CloseTime >= localTime.TimeOfDay)
              .OrderByDescending(x => x.CloseTime)
              .Take(1).SingleOrDefault();

    return Convert.ToInt32((lastShift.CloseTime - localTime.TimeOfDay).TotalMinutes);

EDIT:

Thanks to @Han, here is a list of the same table above:

    var Timings = new []
    {
        new Timing(22, (DayOfWeek)0, new TimeSpan(9,45,0), new TimeSpan(11, 15,  0),null),
        new Timing(23, (DayOfWeek)0, new TimeSpan(13,  0,  0), new TimeSpan( 15,  0,  0), null),
        new Timing(24, (DayOfWeek)1, new TimeSpan( 9,  0,  0), new TimeSpan(23, 59, 59), null),
        new Timing(31, (DayOfWeek)2, new TimeSpan( 0,  0,  0), new TimeSpan( 2,  0,  0), 24),
        new Timing(25, (DayOfWeek)2, new TimeSpan(10,  0,  0), new TimeSpan(12,  0,  0), null),
        new Timing(26, (DayOfWeek)2, new TimeSpan(15,  0,  0), new TimeSpan(17,  0,  0), null),
        new Timing(28, (DayOfWeek)4, new TimeSpan( 9, 45,  0), new TimeSpan(23, 15,  0), null),
        new Timing(29, (DayOfWeek)5, new TimeSpan( 9, 45,  0), new TimeSpan(23, 15,  0), null),
        new Timing(30, (DayOfWeek)6, new TimeSpan( 9, 45,  0), new TimeSpan(23, 15,  0), null),
    };

class Timing
{
    public int Id {get; set;}
    public DayOfWeek DayOfWeek {get; set;}
    public TimeSpan OpenTime {get; set;}
    public TimeSpan CloseTime {get; set;}
    public int? ParentId {get; set;}
    
    public Timing(int id, DayOfWeek dow, TimeSpan openTime, TimeSpan closeTime, int? parentId)
    {
        this.Id = id;
        this.DayOfWeek = dow;
        this.OpenTime = openTime;
        this.CloseTime = closeTime;
        this.ParentId = parentId;
    }
}
ef-core-5.0 sql c#
1 Answers

I suggest to left self-join your table to get the close time in the next day. I assume each row has zero or one child row. I don't use table but array, but the query should be the same. I code in LINQPad.

void Main()
{
    var Timings = new []
    {
        new Timing(22, 0, new DateTime(2021,  9, 12,  9, 45,  0), new DateTime(2021,  9, 12, 11, 15,  0), null),
        new Timing(23, 0, new DateTime(2021,  9, 12, 13,  0,  0), new DateTime(2021,  9, 12, 15,  0,  0), null),
        new Timing(24, 1, new DateTime(2021,  9, 13,  9,  0,  0), new DateTime(2021,  9, 13, 23, 59, 59), null),
        new Timing(31, 2, new DateTime(2021,  9, 14,  0,  0,  0), new DateTime(2021,  9, 14,  2,  0,  0), 24),
        new Timing(25, 2, new DateTime(2021,  9, 14, 10,  0,  0), new DateTime(2021,  9, 14, 12,  0,  0), null),
        new Timing(26, 2, new DateTime(2021,  9, 14, 15,  0,  0), new DateTime(2021,  9, 14, 17,  0,  0), null),
        new Timing(28, 4, new DateTime(2021,  9, 16,  9, 45,  0), new DateTime(2021,  9, 16, 23, 15,  0), null),
        new Timing(29, 5, new DateTime(2021,  9, 17,  9, 45,  0), new DateTime(2021,  9, 17, 23, 15,  0), null),
        new Timing(30, 6, new DateTime(2021,  9, 18,  9, 45,  0), new DateTime(2021,  9, 18, 23, 15,  0), null),
    };
    
    var timingGroupedWithChildren = (
        from t1 in Timings.Where(x => x.ParentId == null) // parent rows only
        join t2 in Timings.Where(x => x.ParentId != null) // childr rows only
            on t1.Id equals t2.ParentId // left join parent's Id with child's ParentId
            into nextDay
        select new {t1, nextDay})
        .Dump() //unremark this line to get show the result in LINQPad
        ;
}

class Timing
{
    public int Id {get; set;}
    public int DayOfWeek {get; set;}
    public DateTime OpenTime {get; set;}
    public DateTime CloseTime {get; set;}
    public int? ParentId {get; set;}
    
    public Timing(int id, int dow, DateTime openTime, DateTime closeTime, int? parentId)
    {
        this.Id = id;
        this.DayOfWeek = dow;
        this.OpenTime = openTime;
        this.CloseTime = closeTime;
        this.ParentId = parentId;
    }
}

The timingGroupedWithChildren looks like this: enter image description here

Notice that only id = 24 has nextDay, the other rows don't have nextDay. There are 8 items (shown at top left corner), but only Id 23 and 24 are shown in detail (other rows are collapsed to save space because my screen is not large enough).

Now it's easy to get the closing time in next day. First approach is like this.

void Main()
{
    var Timings = new []
    {
        new Timing(22, 0, new DateTime(2021,  9, 12,  9, 45,  0), new DateTime(2021,  9, 12, 11, 15,  0), null),
        new Timing(23, 0, new DateTime(2021,  9, 12, 13,  0,  0), new DateTime(2021,  9, 12, 15,  0,  0), null),
        new Timing(24, 1, new DateTime(2021,  9, 13,  9,  0,  0), new DateTime(2021,  9, 13, 23, 59, 59), null),
        new Timing(31, 2, new DateTime(2021,  9, 14,  0,  0,  0), new DateTime(2021,  9, 14,  2,  0,  0), 24),
        new Timing(25, 2, new DateTime(2021,  9, 14, 10,  0,  0), new DateTime(2021,  9, 14, 12,  0,  0), null),
        new Timing(26, 2, new DateTime(2021,  9, 14, 15,  0,  0), new DateTime(2021,  9, 14, 17,  0,  0), null),
        new Timing(28, 4, new DateTime(2021,  9, 16,  9, 45,  0), new DateTime(2021,  9, 16, 23, 15,  0), null),
        new Timing(29, 5, new DateTime(2021,  9, 17,  9, 45,  0), new DateTime(2021,  9, 17, 23, 15,  0), null),
        new Timing(30, 6, new DateTime(2021,  9, 18,  9, 45,  0), new DateTime(2021,  9, 18, 23, 15,  0), null),
    };
    
    var timingGroupedWithChildren = (
        from t1 in Timings.Where(x => x.ParentId == null) // parent rows only
        join t2 in Timings.Where(x => x.ParentId != null) // childr rows only
            on t1.Id equals t2.ParentId // left join parent's Id with child's ParentId
            into nextDay
        select new {
            t1.Id,
            t1.DayOfWeek,
            t1.OpenTime,
            // if current row's next day is null, then use current row's CloseTime
            // otherwise use next day's CloseTime
            CloseTime = nextDay.Where(x => x.ParentId == t1.Id).Count() == 0 ? t1.CloseTime : nextDay.Where(x => x.ParentId == t1.Id).Single().CloseTime
        })
        //.Dump() //unremark this line to get show the result in LINQPad
        ;
    
    var myShift = timingGroupedWithChildren.Where(x => x.Id == 24).Single();
    var myWorkingHours = (myShift.CloseTime - myShift.OpenTime).TotalHours;
    Console.WriteLine($"Working hours = {myWorkingHours}");
}

class Timing
{
    public int Id {get; set;}
    public int DayOfWeek {get; set;}
    public DateTime OpenTime {get; set;}
    public DateTime CloseTime {get; set;}
    public int? ParentId {get; set;}
    
    public Timing(int id, int dow, DateTime openTime, DateTime closeTime, int? parentId)
    {
        this.Id = id;
        this.DayOfWeek = dow;
        this.OpenTime = openTime;
        this.CloseTime = closeTime;
        this.ParentId = parentId;
    }
}

You can see in pic below that I substitute the closing day if current row has children. But I don't test this query with an actual database (I'm using an array) and I don't like calling nextDay.Where(x => ...).Count() twice because some methods in LINQ, eg. Count(), iterates all rows. It's filtered with Where(x => ...) but I can't' say anything unless I see the actual SQL statement executed when calling this query. You can see the actual statement if you turn on SQL Profiler in SQL Management Studio or use LINQPad SQL translation. The button is at the top in the pic (Result lambda symbol SQL IL Tree).

enter image description here

Another approach is just take the child row and do the Count() after you fetch from SQL.

void Main()
{
    var Timings = new []
    {
        new Timing(22, 0, new DateTime(2021,  9, 12,  9, 45,  0), new DateTime(2021,  9, 12, 11, 15,  0), null),
        new Timing(23, 0, new DateTime(2021,  9, 12, 13,  0,  0), new DateTime(2021,  9, 12, 15,  0,  0), null),
        new Timing(24, 1, new DateTime(2021,  9, 13,  9,  0,  0), new DateTime(2021,  9, 13, 23, 59, 59), null),
        new Timing(31, 2, new DateTime(2021,  9, 14,  0,  0,  0), new DateTime(2021,  9, 14,  2,  0,  0), 24),
        new Timing(25, 2, new DateTime(2021,  9, 14, 10,  0,  0), new DateTime(2021,  9, 14, 12,  0,  0), null),
        new Timing(26, 2, new DateTime(2021,  9, 14, 15,  0,  0), new DateTime(2021,  9, 14, 17,  0,  0), null),
        new Timing(28, 4, new DateTime(2021,  9, 16,  9, 45,  0), new DateTime(2021,  9, 16, 23, 15,  0), null),
        new Timing(29, 5, new DateTime(2021,  9, 17,  9, 45,  0), new DateTime(2021,  9, 17, 23, 15,  0), null),
        new Timing(30, 6, new DateTime(2021,  9, 18,  9, 45,  0), new DateTime(2021,  9, 18, 23, 15,  0), null),
    };
    
    var timingGroupedWithChildren = (
        from t1 in Timings.Where(x => x.ParentId == null) // parent rows only
        join t2 in Timings.Where(x => x.ParentId != null) // childr rows only
            on t1.Id equals t2.ParentId // left join parent's Id with child's ParentId
            into nextDay
        select new {
            t1.Id,
            t1.DayOfWeek,
            t1.OpenTime,
            t1.CloseTime,
            NextDay = nextDay
        })
        //.Dump() //unremark this line to get show the result in LINQPad
        ;
        
    var myShift = timingGroupedWithChildren.Where(x => x.Id == 24).Single();
    var myWorkingHours = ((myShift.NextDay.Count() == 0 ? myShift.CloseTime : myShift.NextDay.Single().CloseTime) - myShift.OpenTime).TotalHours;
    Console.WriteLine($"Working hours = {myWorkingHours}");
}

class Timing
{
    public int Id {get; set;}
    public int DayOfWeek {get; set;}
    public DateTime OpenTime {get; set;}
    public DateTime CloseTime {get; set;}
    public int? ParentId {get; set;}
    
    public Timing(int id, int dow, DateTime openTime, DateTime closeTime, int? parentId)
    {
        this.Id = id;
        this.DayOfWeek = dow;
        this.OpenTime = openTime;
        this.CloseTime = closeTime;
        this.ParentId = parentId;
    }
}

You can see that only row with Id = 24 has NextDay (like pic #1). enter image description here

user_27944090
Related