I want combine my time slots as per interval (15,30,,45,60) and do the additon of columns in sql stored procedure

Question

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' of above column.

e.g Existing procedure output:

 Time   Parameter 1     Parameter 2     Parameter 3
  ----------------------------------------------------
    8:00    7.6                2                  0
    8:30    7.52               2                  0
    9:00    20.6               1                  0
    9:30    5.57               1                  0
    10:00   5.43               1                  0
    10:30   5.78               1                  0
    11:00   8.09               1                  0
    11:30   10.48              1                  0
    12:00   10.11              1                  0
    12:30   9.95               0                  0
    13:00   6                  1                  0
    13:30   5.08               0                  0
    14:00   5.31               0                  0
    14:30   20.38              0                  0
    15:00   6.17               0                  0

And I want to be as :

Time-slot            Parameter 1     Parameter 2  Parameter 3
-------------------------------------------------------------
8:00:00 - 8:30:00       15.12           4                0
8:30:00 - 9:00:00       28.12           3                0
9:00:00 - 9:30:00       26.17           2                0
9:30:00 - 10:00:00        11            2                0
10:00:00 - 10:30:00      11.21          2                0
10:30:00 - 11:00:00      13.87          2                0
11:00:00 - 11:30:00      18.57          2                0
11:30:00 - 12:00:00      20.59          2                0
12:00:00 - 12:30:00      20.06          1                0
12:30:00 - 13:00:00      15.95          1                0
13:00:00 - 13:30:00      11.08          1                0
13:30:00 - 14:00:00      10.39          0                0
14:00:00 - 14:30:00      25.69          0                0
14:30:00 - 15:00:00      26.55          0                0

Answer 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.

 

public static DateTime RoundUp(DateTime dt, TimeSpan d)
   => new DateTime((dt.Ticks + d.Ticks - 1) / d.Ticks * d.Ticks, dt.Kind);

public static DateTime RoundDown(DateTime dt, TimeSpan d)
    => new DateTime(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}}

public static IEnumerable<Range<DateTime>> SplitDateRange(DateTime start, DateTime end, TimeSpan ChunkSize)
{
    DateTime chunkEnd;
    while ((chunkEnd = start.Add(ChunkSize)) < end)
    {
        yield return new Range<DateTime>(start, chunkEnd);
        start = chunkEnd;
    }
    yield return new Range<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.

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

    public T Start { get; }
    public T End { get; }
    public bool Includes(T value) 
        => Start.CompareTo(value) <= 0 && End.CompareTo(value) >= 0;
    public bool Includes(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 input
           from r in ranges           // For all range
           where  r.Includes(i.Date)  // If the input date is in a range
           select new                 // 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.