This question already has answers here:
Generate Dates between date ranges (14 answers)
Closed yesterday.
This post was edited and submitted for review yesterday and failed to reopen the post:
Original close reason(s) were not resolved
This is different than other 'need date difference posts' I've found bc I need to parse out the difference between dates by the minute. I tried solutions in this post and others with no luck.
I have this 'UnavailableTime' table
| UnavailableID | UnavailableFrom | UnavailableTo |
| ------------ | ------------ | ------------ |
| 1 | 2/1/2023 8:00:00 AM | 2/1/2023 8:03:00 AM |
| 2 | 2/2/2023 10:30:00 AM | 2/2/2023 10:32:00 AM |
I need a minute breakdown for each timeframe the person was unavailable. Like this:
UnavailableID | MinuteBreakdown |
---|---|
1 | 2/1/2023 8:00:00 AM |
1 | 2/1/2023 8:01:00 AM |
1 | 2/1/2023 8:02:00 AM |
1 | 2/1/2023 8:03:00 AM |
2 | 2/2/2023 10:30:00 AM |
2 | 2/2/2023 10:31:00 AM |
2 | 2/2/2023 10:32:00 AM |
Could someone please help me!
I've tried joining my 'UnavailableTime' table to a calender table that has every minute for the month, then doing a count of minutes inbetween the From & To times with no luck.
3条答案
按热度按时间ukdjmx9f1#
You don't need a calendar table for minutes because there's nothing really special about any minute between two points in time (unless you need to compensate for the night of a change to or from DST, but let's pretend everyone stores UTC because... that makes much more sense).
We can generate a dynamic numbers table using recursion (or sub in a numbers table you already have, or generate one using a variety of other methods that are easy to search for). We only need the largest gap, so we can determine that up front. Then we just join to all of the "minutes to add" that are less than or equal to the minute gap between any row's From / To values.
This works until you get to about 22 days of outage, then you need to change 32767 to 0.
gudnpqoy2#
This can do the trick (as long as you don't have many, many rows, First I calculate the number of minutes between UnavailableFrom and UnavailableTo. then for each UnavailableID I create a numerator till the number of minutes
6fe3ivhb3#
You can use Common Table Expressions :
SQL Fiddle