CODEWITHSUNDEEP
t-sqlsql-server-2012
BishNaboB
BishNaboB

Reputation: 1070

Generate time slots based on duration and quantity in SQL

I have a table that for some reason stores rotas like this:

Rota      | Date_Start              | Position | Duration | Quantity | Rota_Slot_Type
----------+-------------------------+----------+----------+----------+---------------
372387412 | 2020-04-12 08:00:00.000 | 1        | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2        | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 3        | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 4        | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 5        | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 6        | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 7        | 15       | 12       | Not available
372387412 | 2020-04-12 08:00:00.000 | 8        | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 9        | 15       | 1        | Not available

Changing the table is not an option.

I have generated this:

ID_Rota   | Date_Start              | RowNumber | Position | Number | Duration | Quantity | Rota_Slot_Type
----------+-------------------------+-----------+----------+--------+----------+----------+---------------
372387412 | 2020-04-12 08:00:00.000 | 1         | 1        | 1      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2         | 1        | 2      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 3         | 1        | 3      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 4         | 1        | 4      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 5         | 1        | 5      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 6         | 1        | 6      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 7         | 1        | 7      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 8         | 1        | 8      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 9         | 1        | 9      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 10        | 1        | 10     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 11        | 1        | 11     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 12        | 1        | 12     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 13        | 1        | 13     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 14        | 1        | 14     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 15        | 1        | 15     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 16        | 2        | 1      | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 17        | 3        | 1      | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 18        | 4        | 1      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 19        | 4        | 2      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 20        | 4        | 3      | 15       | 13       | Not available

(Top 20 rows, there are 46 in total) This was generated using the following SQL:

select
    rs.ID_Rota,
    rs.Date_Start,
    row_number() over (partition by rs.Date_Start, rs.ID_Rota order by rs.Position, n.Number) as [RowNumber],
    rs.Position,
    n.Number,
    rs.Duration,
    rs.Quantity,
    rs.Rota_Slot_Type
from dbo.RotaSlots as [rs]
    cross apply
        (
            select top (rs.Quantity)
                n.Number + 1 as [Number]
            from dbo.Numbers as [n]
        ) as [n]

The part I'm struggling with is generating a Slot_Start column for each row. My anticipated output is:

ID_Rota   | Date_Start              | Slot_Start              | RowNumber | Position | Number | Duration | Quantity | Rota_Slot_Type
----------+-------------------------+-------------------------+-----------+----------+--------+----------+----------+---------------
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:00:00.000 | 1         | 1        | 1      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:15:00.000 | 2         | 1        | 2      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:30:00.000 | 3         | 1        | 3      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:45:00.000 | 4         | 1        | 4      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:00:00.000 | 5         | 1        | 5      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:15:00.000 | 6         | 1        | 6      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:30:00.000 | 7         | 1        | 7      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:45:00.000 | 8         | 1        | 8      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:00:00.000 | 9         | 1        | 9      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:15:00.000 | 10        | 1        | 10     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:30:00.000 | 11        | 1        | 11     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:45:00.000 | 12        | 1        | 12     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:00:00.000 | 13        | 1        | 13     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:15:00.000 | 14        | 1        | 14     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:30:00.000 | 15        | 1        | 15     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:45:00.000 | 16        | 2        | 1      | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 12:15:00.000 | 17        | 3        | 1      | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 12:30:00.000 | 18        | 4        | 1      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 12:45:00.000 | 19        | 4        | 2      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 13:00:00.000 | 20        | 4        | 3      | 15       | 13       | Not available

The first block is relatively straight forward - (RowNumber - 1) * Duration gives me the Slot_Start for all rows under Position 1. It falls down when you switch block to Position 2, Position 3, and so on.

Any help is gratefully received.

Upvotes: 1

Views: 120

Answers (1)

seanb
seanb

Reputation: 6685

The simplest way to get the Slot_Start time is to

  • Order the rows (as you have there with RowNumber)
  • Do a cumulative sum (running total) of the minutes duration of all preceding rows
  • Add this cumulative sum to the Start_Datetime

In other words - instead of calculating (rownumber - 1) * Duration, you get the sum of Durations of all the relevant preceding rows.

You can use an expression like the following to calculate Slot_Start

DATEADD(minute, SUM(Duration) OVER (PARTITION BY ID_Rota, Date_Start ORDER BY RowNumber) - Duration, Date_Start) AS Slot_Start

Note you may need to put your code into a sub-query or CTE, so that RowNumber is calculated, or you could incorporate the ROW_NUMBER expression etc into your same set of calculations.

You could also use the ROWS explicitly within the window function, except then you need to account for the first row e.g.,

ISNULL(DATEADD(minute, SUM(Duration) OVER (PARTITION BY ID_Rota, Date_Start ORDER BY RowNumber ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), Date_Start), Date_Start) AS Slot_Start2

Here is a db<>fiddle with data similar to what you have above (I started down a path before realising you were doing something different; however, the cumulative sum of Durations should still work).

Upvotes: 1

Related Questions