I Want Combine My Time Slots As Per Interval (15,30,,45,60) And Do The Additon Of Columns In Sql Stored Procedure

I am getting the 'interval' wise (30 min, 15 min, 45 min) data in my stored procedure now I want to combine the 'time slot' of (30 min, 15 min, 45 min, etc.) and do the 'addition'

Solution 1:

1/. Find the range you are working on.

• Round Up the maximum date from your data to your define interval.
• Round Down the minimum from your data to your define interval.

 

publicstatic DateTime RoundUp(DateTime dt, TimeSpan d)=> newDateTime((dt.Ticks + d.Ticks - 1) / d.Ticks * d.Ticks, dt.Kind);

publicstatic DateTime RoundDown(DateTime dt, TimeSpan d)=> newDateTime(dt.Ticks - (dt.Ticks % d.Ticks), dt.Kind);

Edit: With this RoundDown if your minimum is on a split time, no previous interval will be created. E.g for 8:00, the minimum interval is {8:00-8:30} not {7:30-8:00}, {8:00-8:30}

Reference:

• RoundUp/RoundDown

2/. Split the range into sub-range of your interval.

Example: from 8 to 9, in interval of 30min, is {{8-8:30}, {8:30-9}}

publicstatic IEnumerable<Range<DateTime>> SplitDateRange(DateTime start, DateTime end, TimeSpan ChunkSize)
{
    DateTime chunkEnd;
    while ((chunkEnd = start.Add(ChunkSize)) < end)
    {
        yieldreturnnewRange<DateTime>(start, chunkEnd);
        start = chunkEnd;
    }
    yieldreturnnewRange<DateTime>(start, end);
}

Using Range: Range is a pretty basic thing, normally you have two variables (start/end) and write the same comparaison other and other. Here we abstract it away and apply it on class that implement IComparable, so we can easly sort our range. And provide 2 methods to know: If a object is in the range. Or if 2 ranges overlaps.

publicstruct Range<T> where T : IComparable<T>
{
    publicRange(T start, T end)
    {
        Start = start;
        End = end;
    }

    public T Start { get; }
    public T End { get; }
    publicboolIncludes(T value) 
        => Start.CompareTo(value) <= 0 && End.CompareTo(value) >= 0;
    publicboolIncludes(Range<T> range) 
        => Start.CompareTo(range.Start) <= 0 && End.CompareTo(range.End) >= 0;
}

Reference:

• SplitDateRange.
• Martin Flower's Range.

3/. "Multiply the value".

As you need split Value to be in two range: 8:30 is in range {8:00-8:30} and {8:30-9:00}

We will make a cartesian product of your date and the range and pair them based on of the date beeing in the range.

var temp = from i in input            // For all i in inputfrom r in ranges           // For all rangewhere  r.Includes(i.Date)  // If the input date is in a rangeselectnew// We create a new entry 
                      {
                          Range = r,
                          Field1 = i.Field1,
                          Field2 = i.Field2,
                          Field3 = i.Field3
                      };

4/. GroupBy and Sum.

Finaly a simple GroupBy and Sum

var result = 
    temp .GroupBy(x => x.Range))
          .Select(g =>new
              {
                  Range = g.Key,
                  SumField1 = g.Sum(x => x.Field1),
                  SumField2 = g.Sum(x => x.Field2),
                  SumField3 = g.Sum(x => x.Field3)
              })
           .ToList();

Reference:

• GroupBy and Sum.

---

live demo in this demo no extention class have been create in order to put everything in the main.