December 10, 2018 at 8:35 am
Jeff Moden - Monday, December 10, 2018 8:12 AMY.B. - Monday, December 10, 2018 8:01 AMI'm converting my datetimes to time since I want to get data in a time range over multple days. This causes an issue when you have times that cross over midnight. How would you deal with those in this situation?I don't have to deal with such things because I recognized the issue long ago and simply don't use the TIME datatype. Stick with DATETIME.
While I'd normally agree with you (that was my original design) I'm trying to get around the fact that I would need to do a workload analysis on a specific block of time over an entire year.
December 10, 2018 at 9:04 am
Jeff Moden - Monday, December 10, 2018 8:12 AMY.B. - Monday, December 10, 2018 8:01 AMI'm converting my datetimes to time since I want to get data in a time range over multple days. This causes an issue when you have times that cross over midnight. How would you deal with those in this situation?I don't have to deal with such things because I recognized the issue long ago and simply don't use the TIME datatype. Stick with DATETIME.
Yes, just use datetime so you have both the date and time in one column.
If you want to run it over multiple days for a time range, say you want five selected days of activity from 1st December to 5th December between the hours of 23:00 and 02:00 , you would create a temporary table with those datetimes in and join that to your Activity tableDECLARE @SearchTable TABLE (SearchStart datetime, SearchEnd datetime)
INSERT INTO @SearchTable
VALUES
('2018-12-01 23:00:00', '2018-12-02 02:00:00'),
('2018-12-02 23:00:00', '2018-12-03 02:00:00'),
('2018-12-03 23:00:00', '2018-12-04 02:00:00'),
('2018-12-04 23:00:00', '2018-12-05 02:00:00'),
('2018-12-05 23:00:00', '2018-12-06 02:00:00')
SELECT s.SearchStart,
s.SearchEnd,
m.StartDateTime,
m.EndDateTime
FROM @myTable m
INNER JOIN @SearchTable s
ON s.SearchStart <= m.EndDateTime
AND s.SearchEnd >= m.StartDateTime
December 10, 2018 at 10:03 am
Jonathan AC Roberts - Monday, December 10, 2018 9:04 AMJeff Moden - Monday, December 10, 2018 8:12 AMY.B. - Monday, December 10, 2018 8:01 AMI'm converting my datetimes to time since I want to get data in a time range over multple days. This causes an issue when you have times that cross over midnight. How would you deal with those in this situation?I don't have to deal with such things because I recognized the issue long ago and simply don't use the TIME datatype. Stick with DATETIME.
Yes, just use datetime so you have both the date and time in one column.
If you want to run it over multiple days for a time range, say you want five selected days of activity from 1st December to 5th December between the hours of 23:00 and 02:00 , you would create a temporary table with those datetimes in and join that to your Activity tableDECLARE @SearchTable TABLE (SearchStart datetime, SearchEnd datetime)
INSERT INTO @SearchTable
VALUES
('2018-12-01 23:00:00', '2018-12-02 02:00:00'),
('2018-12-02 23:00:00', '2018-12-03 02:00:00'),
('2018-12-03 23:00:00', '2018-12-04 02:00:00'),
('2018-12-04 23:00:00', '2018-12-05 02:00:00'),
('2018-12-05 23:00:00', '2018-12-06 02:00:00')SELECT s.SearchStart,
s.SearchEnd,
m.StartDateTime,
m.EndDateTime
FROM @myTable m
INNER JOIN @SearchTable s
ON s.SearchStart <= m.EndDateTime
AND s.SearchEnd >= m.StartDateTime
Thank you sir! That's exactly what I need. This was driving me nuts...
Here is the whole example with the cutoff times used.
DECLARE @myTable TABLE (StartDateTime DATETIME, StartTime TIME, EndDateTime DATETIME, EndTime TIME)
DECLARE @SearchTable TABLE (SearchStart datetime, SearchEnd datetime)
INSERT INTO @myTable
VALUES
('2018-12-01 23:50:00', '23:50:00', '2018-12-02 00:23:00', '00:23:00'),
('2018-12-01 21:47:00', '22:10:00', '2018-12-02 00:06:00', '00:06:00'),
('2018-12-01 22:01:00', '22:01:00', '2018-12-01 22:23:00', '22:23:00')
INSERT INTO @SearchTable
VALUES
('2018-12-01 22:00:00', '2018-12-01 23:59:00'),
('2018-12-02 22:00:00', '2018-12-02 23:59:00'),
('2018-12-03 22:00:00', '2018-12-03 23:59:00'),
('2018-12-04 22:00:00', '2018-12-04 23:59:00'),
('2018-12-05 22:00:00', '2018-12-05 23:59:00')
SELECT
s.SearchStart,
s.SearchEnd,
CASE WHEN m.StartDateTime < s.SearchStart THEN s.SearchStart ELSE m.StartDateTime END AS StartDateTime,
CASE WHEN m.EndDateTime > s.SearchEnd THEN s.SearchEnd ELSE m.EndDateTime END AS EndDateTime
FROM @myTable m
INNER JOIN @SearchTable s ON s.SearchStart <= m.EndDateTime AND s.SearchEnd >= m.StartDateTime
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply