July 10, 2014 at 8:27 am
Looking for assistance in returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.
Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
July 10, 2014 at 9:05 am
DROP TABLE #Sample
CREATE TABLE #Sample (Startdate DATE, [start-time] INT, [end-time] INT, duration INT)
INSERT INTO #Sample (Startdate, [start-time], [end-time], duration) VALUES ('20140710', 820, 1000, 20)
-- How it's done
SELECT *
FROM #Sample s
CROSS APPLY (
SELECT
RangeStart = DATEADD(MINUTE,([start-time]/100)*60 + [start-time]%100,CAST(Startdate AS DATETIME)),
RangeEnd = DATEADD(MINUTE,([end-time]/100)*60 + [end-time]%100,CAST(Startdate AS DATETIME)),
DurationMinutes = (duration/100)*60 + duration%100
) x
CROSS APPLY (SELECT RangeMinutes = DATEDIFF(MINUTE,RangeStart,RangeEnd)) y
CROSS APPLY (
SELECT TOP((RangeMinutes/DurationMinutes)+1) n FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) d (n)
) z
-- with the noise removed
SELECT Result = DATEADD(MINUTE,z.n*x.DurationMinutes,x.RangeStart)
FROM #Sample s
CROSS APPLY (
SELECT
RangeStart = DATEADD(MINUTE,([start-time]/100)*60 + [start-time]%100,CAST(Startdate AS DATETIME)),
RangeEnd = DATEADD(MINUTE,([end-time]/100)*60 + [end-time]%100,CAST(Startdate AS DATETIME)),
DurationMinutes = (duration/100)*60 + duration%100
) x
CROSS APPLY (SELECT RangeMinutes = DATEDIFF(MINUTE,RangeStart,RangeEnd)) y
CROSS APPLY (
SELECT TOP((RangeMinutes/DurationMinutes)+1) n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) d (n)
) z
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 10, 2014 at 10:23 am
There's a much simpler query:
DECLARE @DATE AS datetime = '07/10/2014';
DECLARE @TIME_START_INT AS int = 820;
DECLARE @TSV AS varchar(4) = RIGHT('00' + CAST(@TIME_START_INT AS varchar(4)), 4);
DECLARE @TIME_END_INT AS int = 1000;
DECLARE @TEV AS varchar(4) = RIGHT('00' + CAST(@TIME_END_INT AS varchar(4)), 4);
DECLARE @START AS datetime = @DATE + CAST(LEFT(@TSV, 2) + ':' + RIGHT(@TSV, 2) + ':00' AS time);
DECLARE @END AS datetime = @DATE + CAST(LEFT(@TEV, 2) + ':' + RIGHT(@TEV, 2) + ':00' AS time);
DECLARE @INTERVAL AS int = 20;
WITH Tally AS (
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Tally
WHERE N + 1 < 10001
)
SELECT DATEADD(mi, @INTERVAL * N, @START) AS THE_TIME
FROM Tally
WHERE DATEADD(mi, @INTERVAL * N, @START) <= @END
OPTION (MAXRECURSION 10000)
Janet Barnett (7/10/2014)
Looking for assistance in returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 10:23 am
Found this one and it's just exactly what I need. Many thanks to Jingyang Li for sharing.
declare @date datetime = '2014-07-10 00:00:00.000'
, @start int = 820
, @end int = 1000
, @duration int = 20 ; --minute
declare @start2 varchar(5)=Left(Right('00'+Cast(@start as varchar(5)),4),2)+':'+Right(Cast(@start as varchar(5)),2)
declare @end2 varchar(5)=Left(Right('00'+Cast(@end as varchar(5)),4),2)+':'+Right(Cast(@end as varchar(5)),2)
;with mycte as (
select *, dateadd(minute, timeslot*n,cast(convert(varchar(10),dt,101)+' '+ stime as datetime)) dt2
,cast(convert(varchar(10),dt,101)+' '+ etime as datetime) dt_end
from (Select @date as dt, @start2 stime, @end2 etime, @duration timeslot) t
cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)
)
select dt2 from mycte where dt2<dt_end
July 10, 2014 at 10:37 am
sgmunson (7/10/2014)
There's a much simpler query:
DECLARE @DATE AS datetime = '07/10/2014';
DECLARE @TIME_START_INT AS int = 820;
DECLARE @TSV AS varchar(4) = RIGHT('00' + CAST(@TIME_START_INT AS varchar(4)), 4);
DECLARE @TIME_END_INT AS int = 1000;
DECLARE @TEV AS varchar(4) = RIGHT('00' + CAST(@TIME_END_INT AS varchar(4)), 4);
DECLARE @START AS datetime = @DATE + CAST(LEFT(@TSV, 2) + ':' + RIGHT(@TSV, 2) + ':00' AS time);
DECLARE @END AS datetime = @DATE + CAST(LEFT(@TEV, 2) + ':' + RIGHT(@TEV, 2) + ':00' AS time);
DECLARE @INTERVAL AS int = 20;
WITH Tally AS (
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Tally
WHERE N + 1 < 10001
)
SELECT DATEADD(mi, @INTERVAL * N, @START) AS THE_TIME
FROM Tally
WHERE DATEADD(mi, @INTERVAL * N, @START) <= @END
OPTION (MAXRECURSION 10000)
Janet Barnett (7/10/2014)
Looking for assistance in returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
Oh... be careful now... Simpler isn't always better. That uses an rCTE that "Counts" and it's a known performance and resource usage issue. There are many other much better methods some of which are even "simpler". Please see the following article for what I'm talking about on the rCTE that "Counts" thing...
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2014 at 11:55 am
Jeff Moden (7/10/2014)
Oh... be careful now... Simpler isn't always better. That uses an rCTE that "Counts" and it's a known performance and resource usage issue. There are many other much better methods some of which are even "simpler". Please see the following article for what I'm talking about on the rCTE that "Counts" thing...
Looks like I needed an update. It's been quite a while since I last reviewed the Tally table stuff, and it's now clear as a bell that while for a very small count, the time involved isn't a killer, but as code like this often ends up much later in the hands of someone with no idea about how it was constructed or why, and then they apply it to a much larger sample, it pays to do it right the first time.
Here's my re-factored query:
DECLARE @DATE AS datetime = '07/10/2014';
DECLARE @TIME_START_INT AS int = 820;
DECLARE @TSV AS varchar(4) = RIGHT('00' + CAST(@TIME_START_INT AS varchar(4)), 4);
DECLARE @TIME_END_INT AS int = 1000;
DECLARE @TEV AS varchar(4) = RIGHT('00' + CAST(@TIME_END_INT AS varchar(4)), 4);
DECLARE @START AS datetime = @DATE + CAST(LEFT(@TSV, 2) + ':' + RIGHT(@TSV, 2) + ':00' AS time);
DECLARE @END AS datetime = @DATE + CAST(LEFT(@TEV, 2) + ':' + RIGHT(@TEV, 2) + ':00' AS time);
DECLARE @INTERVAL AS int = 20;
DECLARE @DIVISOR AS int = (DATEDIFF(mi, @START, @END) / ISNULL(NULLIF(@INTERVAL, 0), 1)) + 1;
--===== Itzik-Style CROSS JOIN counts from 1 to the number of intervals needed
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1-- 1*10^1 or 10 rows
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b-- 1*10^2 or 100 rows
),
E4(N) AS (SELECT 1 FROM E2 a, E2 b-- 1*10^4 or 10,000 rows
),
E8(N) AS (SELECT 1 FROM E4 a, E4 b-- 1*10^8 or 100,000,000 rows
),
Tally(N) AS (SELECT TOP (@DIVISOR) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8
)
SELECT DATEADD(mi, @INTERVAL * N, @START) AS THE_TIME
FROM Tally
WHERE DATEADD(mi, @INTERVAL * N, @START) <= @END
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 12:01 pm
sgmunson (7/10/2014)
...but as code like this often ends up much later in the hands of someone with no idea about how it was constructed or why, and then they apply it to a much larger sample, it pays to do it right the first time.
I couldn't have said it better. Well done!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2014 at 12:16 pm
sgmunson (7/10/2014)
There's a much simpler query:
DECLARE @DATE AS datetime = '07/10/2014';
DECLARE @TIME_START_INT AS int = 820;
DECLARE @TSV AS varchar(4) = RIGHT('00' + CAST(@TIME_START_INT AS varchar(4)), 4);
DECLARE @TIME_END_INT AS int = 1000;
DECLARE @TEV AS varchar(4) = RIGHT('00' + CAST(@TIME_END_INT AS varchar(4)), 4);
DECLARE @START AS datetime = @DATE + CAST(LEFT(@TSV, 2) + ':' + RIGHT(@TSV, 2) + ':00' AS time);
DECLARE @END AS datetime = @DATE + CAST(LEFT(@TEV, 2) + ':' + RIGHT(@TEV, 2) + ':00' AS time);
DECLARE @INTERVAL AS int = 20;
WITH Tally AS (
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Tally
WHERE N + 1 < 10001
)
SELECT DATEADD(mi, @INTERVAL * N, @START) AS THE_TIME
FROM Tally
WHERE DATEADD(mi, @INTERVAL * N, @START) <= @END
OPTION (MAXRECURSION 10000)
Janet Barnett (7/10/2014)
Looking for assistance in returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
Yup...but convert it into a useful iTVF and it looks uncannily similar 🙂
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 10, 2014 at 11:06 pm
Just a quick thought on this problem; there is no need for any string manipulation.
😎
DROP TABLE #Sample
CREATE TABLE #Sample (Startdate DATE, [start-time] INT, [end-time] INT, duration INT)
INSERT INTO #Sample (Startdate, [start-time], [end-time], duration) VALUES ('20140710', 820, 1000, 20)
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,BASE_ENTRY AS
(
SELECT
DATEADD(MINUTE,(S.[start-time] % 100),DATEADD(HOUR,FLOOR(S.[start-time] / 100) ,CONVERT(DATETIME,S.Startdate,0))) AS START_DT
,DATEADD(MINUTE,(S.[end-time] % 100),DATEADD(HOUR,FLOOR(S.[end-time] / 100) ,CONVERT(DATETIME,S.Startdate,0))) AS END_DT
,((S.duration / 100) * 60) + (S.duration % 100) AS DURATION_MINUTES
FROM #Sample S
)
SELECT
DATEADD(MINUTE,NM.N,BE.START_DT) AS Results
FROM BASE_ENTRY BE
OUTER APPLY
(SELECT TOP (((DATEDIFF(MINUTE,BE.START_DT,BE.END_DT) / BE.DURATION_MINUTES)))
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1) * BE.DURATION_MINUTES
FROM T T1, T T2, T T3, T T4) AS NM(N)
Results
-----------------------
2014-07-10 08:20:00.000
2014-07-10 08:40:00.000
2014-07-10 09:00:00.000
2014-07-10 09:20:00.000
2014-07-10 09:40:00.000
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply