SQL Server Minute Breakdown (as datetime values) Between 2 Datetime Columns [duplicate]

ffdz8vbo  于 2023-03-11  发布在  其他
关注(0)|答案(3)|浏览(104)

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:

UnavailableIDMinuteBreakdown
12/1/2023 8:00:00 AM
12/1/2023 8:01:00 AM
12/1/2023 8:02:00 AM
12/1/2023 8:03:00 AM
22/2/2023 10:30:00 AM
22/2/2023 10:31:00 AM
22/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.

ukdjmx9f

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.

DECLARE @maxDelta int;

SELECT @maxDelta = MAX(DATEDIFF(MINUTE, UnavailableFrom, UnavailableTo))
FROM dbo.UnavailableTime;

WITH m(m) AS
(
  SELECT 0 UNION ALL SELECT m+1
  FROM m WHERE m < @maxDelta
)
SELECT u.UnavailableID, 
  MinuteBreakdown = DATEADD(MINUTE, m.m, u.UnavailableFrom)
FROM dbo.UnavailableTime AS u
INNER JOIN m ON m.m <= DATEDIFF(MINUTE, u.UnavailableFrom, u.UnavailableTo)
ORDER BY UnavailableID, MinuteBreakdown
OPTION (MAXRECURSION 32767);

This works until you get to about 22 days of outage, then you need to change 32767 to 0.

gudnpqoy

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

SELECT UnavailableID, MinuteBreakdown 
FROM MyTable
CROSS APPLY (SELECT DATEDIFF(MINUTE, UnavailableFrom,UnavailableTo) MinuteCount ) ca
CROSS APPLY (SELECT TOP (MinuteCount) DATEADD(MINUTE, ROW_NUMBER() OVER( ORDER BY (SELECT NULL))-1, UnavailableFrom ) MinuteBreakdown FROM sys.all_objects) ca2
6fe3ivhb

6fe3ivhb3#

You can use Common Table Expressions :

;WITH CTE AS
(
  -- initial part
  SELECT UnavailableID, UnavailableFrom AS MinuteBreakdown, UnavailableFrom, UnavailableTo
  FROM TimeMachine
  WHERE UnavailableFrom < UnavailableTo
  UNION ALL
  -- recursive part
  SELECT UnavailableID, DATEADD(MI, 1, MinuteBreakdown), UnavailableFrom, UnavailableTo
  FROM CTE
  WHERE DATEADD(MI, 1, MinuteBreakdown) <= UnavailableTo
)
SELECT *
FROM CTE
ORDER BY UnavailableID, MinuteBreakdown;

SQL Fiddle

相关问题