November 28, 2018 at 1:40 pm
I have an employee activity table that is difficult to determine how much time is actualy spent on an activity when you want to group them in 15 minute or 1 hour blocks. Basically during a shift an employee will login at the start and logoff at the end with varying activity codes in between. By using a combination of a Calendar table and a Tally table I can basically expand out the shift in 1 minute increments which would allow reporting on specific blocks of times. The goal is stage the data and then insert into a seperate table from the original table.
I almost have it but there are a few issues I'm contending with.
1. The first pass through this data could take awhile considering I'm creating a 1 minutes timeslot for every employee everyday. (FYI - before I get disclaimers this is against a data warehouse not production but if there is a better way I'd love to hear about it) Because of this I want to run this code in chunks (maybe a month at a time). When I'm finally caught up I'd like to run a job to either do this daily or weekly to just keep the table up to date. The problem is how to capture a previous code that started during a previous load? Just to simulate it you can comment/uncomment the part of my code that expands the data over two days near the VALUES clause.
2. The next problem is with a LOGOFF event. The way I do it I get a repeated LOGOFF until a different event occurs. I could choose to ignore these events in the report but it would be nice to keep things clean.
3. Because I want to do these in blocks where different people might or might not have shifts during that span I run into a problem where I will get a completely empty schedule for certain days and people. It would be nice to find a way to not generate datasets for unless they actually worked on a particular day without having to run this one day at time. Using my code as an example you can see personID 1 does work over the two days but not on the 25th. Therefore, we basically create an empty data set for that day.
Any help with this would be greatly appreciated, thanks.
DECLARE @activity TABLE (act_date DATE, act_time TIME, personID INT, activity VARCHAR(10))
INSERT INTO @activity(act_date, act_time, personID, activity)
VALUES
('2018-11-14', '06:10', 1, 'LOGOFF'),
('2018-11-26', '06:01', 1, 'LOGIN'),
('2018-11-26', '06:07', 1, 'ADMIN'),
('2018-11-26', '12:10', 1, 'LUNCH'),
('2018-11-26', '13:08', 1, 'FL'),
('2018-11-26', '18:09', 1, 'LOGOFF'),
('2018-11-25', '18:02', 2, 'LOGIN'),
('2018-11-25', '18:04', 2, 'TRA'),
('2018-11-26', '00:05', 2, 'LUNCH'),
('2018-11-26', '01:08', 2, 'FL'),
('2018-11-26', '06:03', 2, 'LOGOFF'),
('2018-11-26', '18:01', 2, 'LOGIN'),
('2018-11-26', '18:09', 2, 'ADMIN'),
('2018-11-26', '22:50', 2, 'FL')
DECLARE @Calendar TABLE (Dates DATE)
INSERT INTO @Calendar(Dates)
VALUES ('2018-11-25'), ('2018-11-26')
--VALUES ('2018-11-26')
;WITH Tally (n) AS
(
SELECT TOP (1440)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n)
)
,People (personID) AS
(
SELECT DISTINCT personID FROM @activity
)
SELECT
i.personID,
i.Dates,
i.TimeSlot,
MAX(i.activity) OVER (PARTITION BY i.GroupCount) AS Activity
FROM (
SELECT
y.personID,
c.Dates,
x.TimeSlot,
a.activity,
COUNT(a.activity) OVER (PARTITION BY y.personID ORDER BY y.personID, c.Dates, x.TimeSlot) AS GroupCount
FROM @Calendar c
CROSS APPLY (
SELECT TimeSlot=DATEADD(minute, n, CAST('00:00' AS TIME)) FROM Tally
) x
CROSS APPLY (SELECT People.personID FROM People) y
LEFT JOIN @activity a ON a.act_date = c.Dates AND a.act_time = x.TimeSlot AND a.personID = y.personID
) i
ORDER BY
i.personID,
i.Dates,
i.TimeSlot
November 28, 2018 at 2:13 pm
Some quick thoughts:
1. Why not a 5-minute interval? I don't know of anything other than a payment system that would need a lower interval than that.
2. Omit "LOGOFF" events completely from the table.
3. Change the LEFT JOIN @activity to an INNER JOIN. Any gaps are automatically nonworked / nonactive time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 29, 2018 at 6:45 am
ScottPletcher - Wednesday, November 28, 2018 2:13 PMSome quick thoughts:1. Why not a 5-minute interval? I don't know of anything other than a payment system that would need a lower interval than that.
2. Omit "LOGOFF" events completely from the table.
3. Change the LEFT JOIN @activity to an INNER JOIN. Any gaps are automatically nonworked / nonactive time.
1. I have considered this but my example isn't representative of the frequency of events. These events get generated by multple sources some are manual while others are automated. It's not out of the ordinary to see events happen within a couple of seconds from each other. Rounding the values to fit them into 5 minute timeslots will skew statistical results especially over long periods of time.
2. That's actually not a bad idea. I could omit those events for the time slots but then re-introduce them in the final result set.
3. Ummm...that basically defeats the whole purpose of expanding out the events so they can be measured in smaller time increments. Go ahead and try it out yourself using my code and you'll see what I mean.
Also my biggest issue is how to do these blocks so that I can maintain the table going forward. I need a clever way to capture relavent previous activity codes so that if events occur hours after midnight when the last job interval was complete, it won't show a bunch of NULL's.
November 29, 2018 at 9:07 am
Ok, so I came up with a different method that only expands out the data set rather than create a timeslot for every second. I think it's much cleaner and seems to be much faster. Unfortunately, I have 1 new issue. When I get to the last code it has no time interval to check against. Of course with it I still have to figure out a clean way to run the job from any point to make sure I don't miss anything.
What do you folks think of this method?
EDIT: I added an end of day marker for every person that will later get dropped to give the last code a time to compare with. LOL, when I look at this post in seems like a sneek peak into my thought process.
DECLARE @activity TABLE (act_date DATETIME, personID INT, activity VARCHAR(10))
DECLARE @activity2 TABLE (act_date DATETIME, personID INT, activity VARCHAR(10), TimeMinutes INT)
--add sample data
INSERT INTO @activity(act_date, personID, activity)
VALUES
('2018-11-14 06:10', 1, 'LOGOFF'),
('2018-11-26 06:01', 1, 'LOGIN'),
('2018-11-26 06:07', 1, 'ADMIN'),
('2018-11-26 12:10', 1, 'LUNCH'),
('2018-11-26 13:08', 1, 'FL'),
('2018-11-26 18:09', 1, 'LOGOFF'),
('2018-11-25 18:02', 2, 'LOGIN'),
('2018-11-25 18:04', 2, 'TRA'),
('2018-11-26 00:05', 2, 'LUNCH'),
('2018-11-26 01:08', 2, 'FL'),
('2018-11-26 06:03', 2, 'LOGOFF'),
('2018-11-26 18:01', 2, 'LOGIN'),
('2018-11-26 18:09', 2, 'ADMIN'),
('2018-11-26 22:50', 2, 'FL')
--add an "End of Day" marker to give the last entry something to compare to
INSERT INTO @activity(act_date, personID, activity)
SELECT
CAST(DATEADD(DAY, 1, MAX(a.act_date)) AS DATE) AS act_date,
x.personID,
'EOD' AS activity
FROM @activity a
CROSS APPLY (SELECT DISTINCT personID FROM @activity) x
GROUP BY
x.personID
--determine the time difference in minutes between events
INSERT INTO @activity2(act_date, personID, activity, TimeMinutes)
SELECT
a.act_date,
a.personID,
a.activity,
CASE WHEN a.activity = 'LOGOFF' THEN 1 ELSE
DATEDIFF(MINUTE, a.act_date, LEAD(a.act_date) OVER (PARTITION BY personID ORDER BY act_date)) END AS TimeMinutes
FROM
@activity a
;WITH Tally (n) AS
(
SELECT TOP (100000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n)
)
--expand out the rows by the number of minutes the activities took
SELECT
DATEADD(MINUTE, t.n, a.act_date) AS act_date,
a.personID,
a.activity
FROM
@activity2 a
JOIN Tally t ON t.n < a.TimeMinutes
ORDER BY
a.personID,
DATEADD(MINUTE, t.n, a.act_date)
November 29, 2018 at 12:32 pm
From your post, it seems that you want to get records of how much time users spend on various activities. You have a log of date/time events, and need to match up the "current" one with the "prior" one or the "next" one to determine the time difference. The LAG and LEAD functions will provide that data. I know this is a slightly different approach to dividing the duration into blocks, but it may be what you need, or it may provide a base data set to get what you need
Your data appears to be a "log first" set, meaning the record indicates what the person is going to do starting at the logged point in time, not a "log later" set, which says what the person did finishing with the logged time. Based on that, I'm counting the logged date and time as the starting point, and using the LEAD function to get the date/time from the next record to use as the ending point. Then the starting and ending points are fed to the DATEDIFF function to find the difference.SELECT act_datetime,
personID,
activity,
LEAD(act_datetime, 1) OVER (PARTITION BY personID ORDER BY act_datetime) AS prior_datetime,
CASE WHEN activity = 'LOGOFF' THEN NULL
ELSE (DATEDIFF(MILLISECOND, act_datetime, LEAD(act_datetime, 1) OVER (PARTITION BY personID ORDER BY act_datetime)) * 1.0) / 60000 END
AS act_minutes
FROM (
SELECT CAST(act_date AS DATETIME) + CAST(act_time AS DATETIME) AS act_datetime,
personID,
activity
FROM @Activity
) q
ORDER BY personID, act_datetime
I used a MILLISECOND difference for DATEDIFF, then divided by 60000 so that partial minutes could be returned, depending on the data. In this case, the data has only whole minutes recorded, so only whole minutes are being returned. The "prior_datetime" column is used for clarity and verification, but it is not strictly needed in the results.
November 29, 2018 at 1:27 pm
Thanks for the input Jonathan. Although it does look like you are working with my original idea since you are using act_time. Though time is seperate from date in my actual tables I combined them together as I will be doing that when I stage the data to facilitate some of the work I'm doing.
I think I figured out all the issues I had. I added my final code below. I always appreciate any comments or feedback. Maybe this will help someone else in the future with a similare situation.
DECLARE @activity TABLE (act_date DATETIME, personID INT, activity VARCHAR(10))
DECLARE @activity2 TABLE (act_date DATETIME, personID INT, activity VARCHAR(10), TimeMinutes INT)
DECLARE @final TABLE (act_date DATETIME, personID INT, activity VARCHAR(10))
DECLARE @From DATE, @To DATE
SET @From = '2018-11-25'
SET @To = '2018-11-26'
--add sample data
INSERT INTO @activity(act_date, personID, activity)
VALUES
('2018-11-14 06:10', 1, 'LOGOFF'),
('2018-11-26 06:01', 1, 'LOGIN'),
('2018-11-26 06:07', 1, 'ADMIN'),
('2018-11-26 12:10', 1, 'LUNCH'),
('2018-11-26 13:08', 1, 'FL'),
('2018-11-26 18:09', 1, 'LOGOFF'),
('2018-11-25 18:02', 2, 'LOGIN'),
('2018-11-25 18:04', 2, 'TRA'),
('2018-11-26 00:05', 2, 'LUNCH'),
('2018-11-26 01:08', 2, 'FL'),
('2018-11-26 06:03', 2, 'LOGOFF'),
('2018-11-26 18:01', 2, 'LOGIN'),
('2018-11-26 18:09', 2, 'ADMIN'),
('2018-11-26 22:50', 2, 'FL')
--add an "End of Day" marker to give the last entry something to compare to
INSERT INTO @activity(act_date, personID, activity)
SELECT
CAST(DATEADD(DAY, 1, @To) AS DATE) AS act_date,
x.personID,
'EOD' AS activity
FROM (SELECT DISTINCT personID FROM @activity) x
GROUP BY
x.personID
--determine the time difference in minutes between events
INSERT INTO @activity2(act_date, personID, activity, TimeMinutes)
SELECT
a.act_date,
a.personID,
a.activity,
CASE WHEN a.activity = 'LOGOFF' THEN 1 ELSE
DATEDIFF(MINUTE, a.act_date, LEAD(a.act_date) OVER (PARTITION BY personID ORDER BY act_date)) END AS TimeMinutes
FROM
@activity a
;WITH Tally (n) AS
(
SELECT TOP (100000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n)
)
--expand out the rows by the number of minutes the activities took
INSERT INTO @final(act_date, personID, activity)
SELECT
DATEADD(MINUTE, t.n, a.act_date) AS act_date,
a.personID,
a.activity
FROM
@activity2 a
JOIN Tally t ON t.n < a.TimeMinutes
ORDER BY
a.personID,
DATEADD(MINUTE, t.n, a.act_date)
SELECT * FROM @final
-----------------------------------------------------------
--Pretend we are loading the next batch
-----------------------------------------------------------
DELETE @activity
DELETE @activity2
DECLARE @DayBefore DATETIME
SELECT @DayBefore = DATEADD(MINUTE, -1, '2018-11-27') --this will come from input parameter in final code
--add sample data
INSERT INTO @activity(act_date, personID, activity)
VALUES
('2018-11-27 06:01', 3, 'LOGIN'),
('2018-11-27 10:10', 3, 'LOGOFF'),
('2018-11-27 00:47', 2, 'LUNCH'),
('2018-11-27 01:49', 2, 'ADMIN'),
('2018-11-27 06:03', 2, 'LOGOFF')
--Add a start marker for anything that happened in the last minute of the last load
INSERT INTO @activity(act_date, personID, activity)
SELECT
act_date,
personID,
activity
FROM
@final
WHERE
act_date = @DayBefore
--add an "End of Day" marker to give the last entry something to compare to
INSERT INTO @activity(act_date, personID, activity)
SELECT
CAST(DATEADD(DAY, 1, '2018-11-27') AS DATE) AS act_date, --in the final code I'll likely use the input parameter to determine the highest date
x.personID,
'EOD' AS activity
FROM (SELECT DISTINCT personID FROM @activity) x
GROUP BY
x.personID
--determine the time difference in minutes between events
INSERT INTO @activity2(act_date, personID, activity, TimeMinutes)
SELECT
a.act_date,
a.personID,
a.activity,
CASE WHEN a.activity = 'LOGOFF' THEN 1 ELSE
DATEDIFF(MINUTE, a.act_date, LEAD(a.act_date) OVER (PARTITION BY personID ORDER BY act_date)) END AS TimeMinutes
FROM
@activity a
;WITH Tally (n) AS
(
SELECT TOP (100000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n)
)
--expand out the rows by the number of minutes the activities took
INSERT INTO @final(act_date, personID, activity)
SELECT
DATEADD(MINUTE, t.n, a.act_date) AS act_date,
a.personID,
a.activity
FROM
@activity2 a
JOIN Tally t ON t.n < a.TimeMinutes
ORDER BY
a.personID,
DATEADD(MINUTE, t.n, a.act_date)
SELECT * FROM @final
November 30, 2018 at 6:49 pm
I might simply be missing it but do you have an example of what you actually want to save to a table using the given data?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2018 at 7:10 pm
Jeff Moden - Friday, November 30, 2018 6:49 PMI might simply be missing it but do you have an example of what you actually want to save to a table using the given data?
I don't understand what the OP is trying to do either.
December 1, 2018 at 9:41 am
I did end up with a working solution but I should probably elaborate since I seem to have missed the mark explaining my problem. I have a very busy event table that is imported every night to a data warehouse for reporting. The way it works is whenever a new event happens it gets recorded with the date and time, this continues until a user is logged off. The users are only ever assigned to one thing at a time. Therefore, in order to determine how much time is actually spent on a particular task on any given day has been traditionally to measure the time difference between one event and the very next one. So for example, if one event was logged that user A started "Admin" work at 14:00 and the next logged event for let say "Training" was at 17:00. In this scenario, we assume user A was assigned to Admin work for 3 hours. This method works fine when looking at overall work time for an entire shift. The problem is when folks want to look for busy times and such at specific time intervals. This can be in 15 minutes, 1 hour, 3 hour and 6 hour increments. If someone were to query what happened between 15:00 - 16:00 no records would be returned and thus would appear that User A did nothing in that time period which isn't true.
So with that said I was attempting to devise a method to "fill in the blanks" if you will be expanding the data out. At first, I thought to create a schedule with timeslot I could assign those events to and fill them out would help. But the problem is I ended up with too much wasted space. In my last post, I changed gears a bit and only expanded out the events in 1 minute increments for the time difference between events rather than slot them in time slots. The main issue I had with either technique was to determine a way to keep the table up to date after the initial data transformation by making sure I didn't leave any gaps in the data. For example, let's say I ran the job to expand the data out up to November 12. Next time I might run the job for all events between the 13th to 20th. Since some folks might be on a shift that crossed midnight I needed to make sure that certain events from the previous load were considered in the next load.
Although not exactly how I'm implementing it, the last code I posted attempts to simulate what I'm talking about. I hope it's more clear...
Cheers,
December 1, 2018 at 2:15 pm
Y.B. - Saturday, December 1, 2018 9:41 AMI did end up with a working solution but I should probably elaborate since I seem to have missed the mark explaining my problem. I have a very busy event table that is imported every night to a data warehouse for reporting. The way it works is whenever a new event happens it gets recorded with the date and time, this continues until a user is logged off. The users are only ever assigned to one thing at a time. Therefore, in order to determine how much time is actually spent on a particular task on any given day has been traditionally to measure the time difference between one event and the very next one. So for example, if one event was logged that user A started "Admin" work at 14:00 and the next logged event for let say "Training" was at 17:00. In this scenario, we assume user A was assigned to Admin work for 3 hours. This method works fine when looking at overall work time for an entire shift. The problem is when folks want to look for busy times and such at specific time intervals. This can be in 15 minutes, 1 hour, 3 hour and 6 hour increments. If someone were to query what happened between 15:00 - 16:00 no records would be returned and thus would appear that User A did nothing in that time period which isn't true.So with that said I was attempting to devise a method to "fill in the blanks" if you will be expanding the data out. At first, I thought to create a schedule with timeslot I could assign those events to and fill them out would help. But the problem is I ended up with too much wasted space. In my last post, I changed gears a bit and only expanded out the events in 1 minute increments for the time difference between events rather than slot them in time slots. The main issue I had with either technique was to determine a way to keep the table up to date after the initial data transformation by making sure I didn't leave any gaps in the data. For example, let's say I ran the job to expand the data out up to November 12. Next time I might run the job for all events between the 13th to 20th. Since some folks might be on a shift that crossed midnight I needed to make sure that certain events from the previous load were considered in the next load.
Although not exactly how I'm implementing it, the last code I posted attempts to simulate what I'm talking about. I hope it's more clear...
Cheers,
If you have a table that you calculate the start time and end time of an activity (1 row per activity) and you have a time range you want to see what the person was doing you can just query:SELECT *
FROM myTable t
WHERE t.Act_StartDatetime <= @TimeRangeEnd
AND t.Act_EndDatetime >= @TimeRangeStart
AND t.personID = @PersonID
You will get a row for each activity the person was doing in the time range.
So I would just create a table with the StartDatetime and EndDatetime and work out how to populate that from your events.
December 1, 2018 at 5:27 pm
If you looked at my sample data you’d see I don’t have a start and end time for event. It’s assumed the end time is the start time of the next event. Besides that I need an accurate measurement of time spent for any time range. So if an event took 1 hour between 2:30am to 3:30am but you wanted to know what happened between 2am and 3am you would want to see 30 min spent on that particular event not 1 hour.
EDIT: Please don’t confuse my original data with my actual solution which might be what your are looking. I needed to transform it to get to the point where I can easily query it. My fault for not marking it as an answer.
December 1, 2018 at 7:14 pm
Y.B. - Saturday, December 1, 2018 5:27 PMIf you looked at my sample data you’d see I don’t have a start and end time for event. It’s assumed the end time is the start time of the next event. Besides that I need an accurate measurement of time spent for any time range. So if an event took 1 hour between 2:30am to 3:30am but you wanted to know what happened between 2am and 3am you would want to see 30 min spent on that particular event not 1 hour.EDIT: Please don’t confuse my original data with my actual solution which might be what your are looking. I needed to transform it to get to the point where I can easily query it. My fault for not marking it as an answer.
"If you looked at my sample data you’d see I don’t have a start and end time for event. It’s assumed the end time is the start time of the next event."
That's what I mean when I said: "create a table with the StartDatetime and EndDatetime and work out how to populate that from your events." You would have to populate the EndDateStamp as the start time of the next event and that's what you need to work out.
"So if an event took 1 hour between 2:30am to 3:30am but you wanted to know what happened between 2am and 3am you would want to see 30 min spent on that particular event not 1 hour."
That's just a very minor change to the query I gave, so you would have something like this in your select:SELECT IIF(t.Act_StartDatetime < @TimeRangeStart, @TimeRangeStart, t.Act_StartDatetime) AS StartDateTimeWithinRange,
IIF(t.Act_EndDatetime > @TimeRangeEnd, @TimeRangeEnd, t.Act_EndDatetime ) AS EndDateTimeWithinRange,
FROM myTable t
WHERE t.Act_StartDatetime <= @TimeRangeEnd
AND t.Act_EndDatetime >= @TimeRangeStart
AND t.personID = @PersonID
Then you would see 30 minutes, not 1 hour.
December 1, 2018 at 8:02 pm
Ok, I didn’t see it before but that’s an interesting take. I’ll give it a try when I get into the office on Monday. Surprisingly, my code ran pretty fast at about 2 seconds for an entire week. However, your method wouldn’t require nearly as much storage. Thanks for the input, I’ll let you know how I make out.
December 10, 2018 at 8:01 am
Jonathan AC Roberts - Saturday, December 1, 2018 7:14 PMY.B. - Saturday, December 1, 2018 5:27 PMIf you looked at my sample data you’d see I don’t have a start and end time for event. It’s assumed the end time is the start time of the next event. Besides that I need an accurate measurement of time spent for any time range. So if an event took 1 hour between 2:30am to 3:30am but you wanted to know what happened between 2am and 3am you would want to see 30 min spent on that particular event not 1 hour.EDIT: Please don’t confuse my original data with my actual solution which might be what your are looking. I needed to transform it to get to the point where I can easily query it. My fault for not marking it as an answer.
"If you looked at my sample data you’d see I don’t have a start and end time for event. It’s assumed the end time is the start time of the next event."
That's what I mean when I said: "create a table with the StartDatetime and EndDatetime and work out how to populate that from your events." You would have to populate the EndDateStamp as the start time of the next event and that's what you need to work out."So if an event took 1 hour between 2:30am to 3:30am but you wanted to know what happened between 2am and 3am you would want to see 30 min spent on that particular event not 1 hour."
That's just a very minor change to the query I gave, so you would have something like this in your select:SELECT IIF(t.Act_StartDatetime < @TimeRangeStart, @TimeRangeStart, t.Act_StartDatetime) AS StartDateTimeWithinRange,
IIF(t.Act_EndDatetime > @TimeRangeEnd, @TimeRangeEnd, t.Act_EndDatetime ) AS EndDateTimeWithinRange,
FROM myTable t
WHERE t.Act_StartDatetime <= @TimeRangeEnd
AND t.Act_EndDatetime >= @TimeRangeStart
AND t.personID = @PersonID
Then you would see 30 minutes, not 1 hour.
So I just started testing out your method (busy week) and it's pretty cool. I'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?
DECLARE @myTable TABLE (StartDateTime DATETIME, StartTime TIME, EndDateTime DATETIME, EndTime TIME)
DECLARE @FromTime TIME, @ToTime TIME
SET @FromTime = '22:00:00'
SET @ToTime = '23:59:00'
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 22:10: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')
--SELECT * FROM @myTable
SELECT
IIF(m.StartTime < @FromTime, @FromTime, m.StartTime) AS StartTime,
IIF(m.EndTime > @ToTime, @ToTime, m.EndTime) AS EndTime,
m.StartDateTime,
m.EndDateTime
FROM @myTable m
WHERE
m.StartTime <= @ToTime
AND m.EndTime >= @FromTime
December 10, 2018 at 8:12 am
Y.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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply