How to get calculated values from a derived(?) table?
-
Here is my filtered dataset:
I would like to calculate the time difference between each 0 and 1 that occur sequentially. In this case, the time difference between each time stamp, since the 0's and 1's alternate. So, from item 10 to 11 - that's just over 25 minutes, from 11 to 12 - almost two minutes. How would I go about this?
I mention derived in the title because I'm thinking I might need to use some sort of subquery/derived table? I don't have much experience on derived tables and how to pull out a reference to each row to find the time difference.
Thank you
- This topic was modified 5 months, 1 week ago by mjdemaris. Reason: missing info
-
use LAG()? I tried to use your picture to create a table, but SSMS didn't know what to do with the picture when I tried to paste it in.
In all seriousness, if you want help, post consumable data.
-
Please see the article at the first link in my signature line below. Most of us like to test our answers before we publish them. For that, we need "Readily Consumable" Test Data and that article explains one of a couple of ways to do it. Thanks for helping us help you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
oh, the 25 minutes. that's the giveaway. use LAG, like I said.
use tempdb;
go
CREATE TABLE ds(
ID INT NOT NULL,
Flag BIT,
EventTime DATETIME2
);
GO
INSERT INTO ds VALUES
(10,0,'2024-06-24 14:20:19.437'),
(11,1,'2024-06-24 14:45:45.027'),
(12,0,'2024-06-24 14:47:35.067'),
(13,1,'2024-06-24 15:14:45.653'),
(14,0,'2024-06-24 15:17:00.707'),
(15,1,'2024-06-24 15:27:35.937'),
(16,0,'2024-06-24 15:29:35.980');SELECT ppt.ID, ppt.Flag, ppt.EventTime,ppt.PrevTime,ppt.PrevPrevTime,
span=DATEDIFF(minute,ppt.PrevTime,ppt.EventTime)
FROM
(SELECT ID, Flag, EventTime
,PrevTime = LAG(EventTime,1) OVER (ORDER BY EventTime)
,PrevPrevTime = LAG(EventTime,2) OVER (ORDER BY EventTime)
FROM ds) ppt -
Thanks Jeff, I will be sure to have some ready to use code in the future.
-
So, this is a derived table and LAG() is a window function. Well, I just started learning about these and I appreciate the help. I don't know enough about window functions to really know what I can or should do with them. But now I have one more tool in my toolbox!
Thank you
-
You're not going to use this data as points in time, but as intervals, so model it that way
Create table events
(event_number integer not null primary key,
event_start_timestamp dateime2(0) not null unique,
event_finish_timestamp dateime2(0) unique,
check (event_start_timestamp <= event_finish_timestamp) );
It's trivial to compute the duration of each of the events now. After 30 something years of doing SQL, I found it's not a good idea to write with assembly language, BIT flags like you're doing.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
This "check" field, is this something that you create as a field in a table? Because I've never seen that done.
What is the reasoning behind not using BIT flags? How could they be detrimental?
- jcelko212 32090 wrote:
You're not going to use this data as points in time, but as intervals, so model it that way
Create table events (event_number integer not null primary key, event_start_timestamp dateime2(0) not null unique, event_finish_timestamp dateime2(0) unique, check (event_start_timestamp <= event_finish_timestamp) );
It's trivial to compute the duration of each of the events now. After 30 something years of doing SQL, I found it's not a good idea to write with assembly language, BIT flags like you're doing.
True enough but sometimes... most of the time... you have to deal with the data that you're given. With that being said, do you have some code that will convert the OP's original data to the condition that you're describing?
After all, you DID say it was a "trivial" thing to do and so it should be a breeze for someone with over 30 years of experience in SQL, like yourself.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - mjdemaris wrote:
Thanks Jeff, I will be sure to have some ready to use code in the future.
Heh.. "next time". You're saying that because someone already did it but... they did a datatype incorrectly (IMHO), which also complicates the solution a lot as well as negating some future functionality that you might be asked to do like determining what the percentage of time that the EventStatus was a 1 and how much total time was that... by day, by week, by, month, by quarter, by year?
If you look at the DATA for the "EventTime", we see that the milliseconds ALWAYS end in a 0, 3, or 7. There's only one datatype that does that and that's the DATETIME datatype. The person that posted code used DATETIME2. There's a HUGE difference in functionality between the 2 datatypes with DATETIME2 being the more complex of the 2 for the calculation of simple DURATIONs. That's why he resorted to calculating just MINUTES.
Here's an article on how to calculate duration for DATETIME datatypes.
Ok... so let's build some test data with what I believe is the correct datatype for the Event times stamp (DATETIME).
--===== Create the test table with the expected Clustered PK.
DROP TABLE IF EXISTS #Event
;
CREATE TABLE #Event
(
EventID INT NOT NULL
,EventState BIT NOT NULL
,EventTS DATETIME NOT NULL
,PRIMARY KEY CLUSTERED (EventTS,EventID)
)
;
--===== Populate the test table
INSERT INTO #Event
(EventID,EventState,EventTS)
VALUES (10,0,'2024-06-24 14:20:19.437')
,(11,1,'2024-06-24 14:45:45.027')
,(12,0,'2024-06-24 14:47:35.067')
,(13,1,'2024-06-24 15:14:45.653')
,(14,0,'2024-06-24 15:17:00.707')
,(15,1,'2024-06-24 15:27:35.937')
,(16,0,'2024-06-24 15:29:35.980')
,(17,1,'2024-06-26 17:14:13.653') -- > 48 hours later
;In the following, it's easy to calculate the duration just by subtracting one EventTS from another. We "DRY" that out in a CTE and then formatting becomes nearly trivial and the number of hours can go up to whatever DATEDIFF can handle. Again, this is made pretty easy because of the use of the DATETIME datatype.
The cool part about this code is that, not only is it simple, it makes things like "DecimalDaysDuration" lead to a whole lot of other possibilities including temporal aggregations really easy (read the comments in the code). You'll become an "analyst hero" in the eyes of others.
--===== Solution uses LEAD to provide the duration for the current EventState.
-- For example, EventID 10 recorded an EventState of "0" that started at
-- '2024-06-24 14:20:19.437'. That EventState lasted until the next EventID,
-- which changed the EventState to "1" and that started at '2024-06-24 14:45:45.027'.
-- That means that the EventState that started at EventID "10" lasted for
-- '0:25:25:590" (hh:mi:ss:mmm).
--
-- Note also how easy it is to convert the RawDur to DecimalDays so that you
-- could easily aggregate durations by EventState and EventTS or whatever you
-- want than then simply do a CONVERT(DATETIME,SUM(DecimalDays)) and format that
-- like we did for the EventDuration to get hhhhhhh:mi:ss:mmm displayed for that
-- duration aggregate
WITH cteDryDur AS
(
SELECT EventID,EventState,EventTS
,RawDur = LEAD(EventTS,1) OVER (ORDER BY EventTS,EventID)-EventTS
FROM #Event
)
SELECT EventID,EventState,EventTS
,EventDuration = STUFF(CONVERT(VARCHAR(20),RawDur,114),1,2,DATEDIFF(hh,0,RawDur))
,DecimalDaysDuration = CONVERT(FLOAT,RawDur)
FROM cteDryDur
ORDER BY EventTS,EventID
;That produces the following output...
... and that, my friend, is the easy power of DATETIME and the reason why you shouldn't wait even when it looks like someone has done the job for you. 😀
Since you say you're a bit new, lookup the documentation for each function used by searching for the function name followed by t-sql. Like this...
STUFF t-sql
For the arcane features of DATETIME, Microsoft unfortunately listened to some "experts" and they no longer carry the information in their documentation. A lot of the "experts" are also authors on the internet and so it's a bit difficult to find decent articles with the old knowledge that is still very true and useful. 🙁
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - mjdemaris wrote:
This "check" field, is this something that you create as a field in a table? Because I've never seen that done.
What is the reasoning behind not using BIT flags? How could they be detrimental?
It's not a "check" field, it's a check constraint.
JCelko is a SQL purist. Some of his comments are based on his ideal SQL rather than the T-SQL that this forum is dedicated to. He always denigrates the use of BIT flags, but never discusses the suggested alternative of using Boolean data types, because he knows full well that T-SQL DOES NOT SUPPORT BOOLEAN DATA TYPES. Everything that JCelko says needs to be taken with a huge grain of salt, because of his refusal to ground his recommendations in reality.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Great info and articles, Jeff.
On another note, working with the same data: I would like to filter the data based on date and time, by shift, for example. I read an article here: https://stackoverflow.com/questions/54557004/calculate-total-downtime-based-on-shift-times
where someone told the OP to join the tables on whether the event times are between the shift times. So I tried something like this:
SELECT *
FROM #Event AS e
WHERE e.EventTS BETWEEN (
SELECT st.ShiftStart, st.ShiftEnd
FROM dbo.ShiftTimes AS st
WHERE st.ShiftName = 'Day Shift'
)Well, this didn't work, lol. Using the data above, if I want to look at 2024-06-24, and the time from 0700 to 1900... As part of solving this, I created a new column just for the time "EventTime", like this:
set EventTime = CAST(t_stamp AS time)
That gives me just the time part. I can filter for the date easy (WHERE EventTS BETWEEN FirstDate AND SecondDate), but if the Event overlaps another shift, I think I will run into inaccurate data.
I am going to come back to this post tomorrow to finish my train of thought as I work through it--it's getting late.
Ok, I am at the point where I would like to SUM the EventDuration, like this:
WITH cteDryDur AS
(
SELECT EventID,EventState,EventTS
,RawDur = LEAD(EventTS,1) OVER (ORDER BY EventTS,EventID)-EventTS
FROM #Event
WHERE CAST(EventTS AS date) = '2024-07-19' AND CAST(EventTS AS time)
BETWEEN '07:00:00' AND '19:00:00'
)
SELECT
SUM(EventDuration = STUFF(CONVERT(VARCHAR(20),RawDur,114),1,2,DATEDIFF(hh,0,RawDur)))
FROM cteDryDur
WHERE EventState = 1
ORDER BY EventTS,EventID
;But, there is a syntax error at the last parenthesis of the SUM statement. I'm guessing that attempting to SUM this up in this way is not correct. I suppose part of the problem might be that it is a string, but that would be a different error. I tried to cast the EventDuration expression, but that does not work either.
So, it looks like I might need to CAST this field AS TIME, then deconstruct into it's separate units, SUM, account for rolling over the 24/60/60 (h/m/s) marks...
Last Edit, for sure. I left out a step, here.
DROP TABLE IF EXISTS #EventDur;
CREATE TABLE #EventDur
(
Duration nvarchar(20) NULL,
DurTime time(3) NULL,
DurHour int NULL,
DurMin int NULL,
DurSec int NULL
);
WITH cteDryDur AS
(
SELECT EventID,EventState,EventTS
,RawDur = LEAD(EventTS,1) OVER (ORDER BY EventTS,EventID)-EventTS
FROM #Event
WHERE CAST(EventTS AS date) = '2024-07-19' AND CAST(EventTS AS time)
BETWEEN '07:00:00' AND '19:00:00'
)
INSERT INTO #EventDur (Duration)
SELECT
EventDuration = STUFF(CONVERT(VARCHAR(20),RawDur,114),1,2,DATEDIFF(hh,0,RawDur))
FROM cteDryDur
WHERE EventState = 1
ORDER BY EventTS,EventID
;
UPDATE #EventDur
SET DurTime = CAST(Duration AS time)
UPDATE #EventDur
SET DurHour = DATEPART(HH, DurTime), DurMin = DATEPART(MINUTE, DurTime), DurSec = DATEPART(Second, DurTime)Last Edit I think. Here is my final form for total time, Days, Hours, Minutes, Seconds: (I don't know if this is the correct way to accomplish this, but it's mine, as a professional green-leaf!)
SELECT CONCAT(q1.Rem_Day,':',q1.Rem_Hr, ':', q1.Rem_Min, ':',q1.Rem_Sec)
FROM
(SELECT
-- Total amounts
SUM(DurSec) AS Tot_Sec, SUM(DurMin) AS Tot_Min, SUM(DurHour) AS Tot_Hr,
-- Extra units
SUM(DurSec)/60 AS Extra_Min, SUM(DurMin)/60 AS Extra_Hr, SUM(DurHour)/24 AS Extra_day,
-- What's Remaining? Mod
SUM(DurSec)%60 AS Rem_Sec,
(SUM(DurMin) + SUM(DurSec)/60)%60 AS Rem_Min,
(SUM(DurHour) + SUM(DurMin)/60)%24 AS Rem_Hr,
((SUM(DurHour) + SUM(DurMin)/60)%24)/24%365 AS Rem_Day
FROM #EventDur) AS q1I will drop a version of this into an SP, with some variables for date range (start, end) and time range (start, end). Should work!
-
Piet, Jeff, JCelko, Drew:
This is my almost final draft. I decided to return just the SUM of seconds of down time of the selected shift of the day, and the GUI can transform the seconds into hours and minutes. I noticed, however, that if there is a significant gap of down time, say over something like 16 to 24 hours, that this does not take that into account. This will form the base for other SPs that would allow a date range selection from the GUI, then lead to allowing the use of comments as to why we are down (user input), and aggregating those results into a report.
One strange thing I encountered with using the temp tables, even though I dropped and recreated them, they retained their original configuration. For example, I needed to change the data type of "DurTime1" to INT, but it kept the DATETIME type. So I just added a 1 to the table name...
ALTER PROCEDURE dbo.usp_DownTime
-- Add the parameters for the stored procedure here
@Date_StartDATETIME ='2024-07-20 01:00:00'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE@Shift_BeginDATETIME=NULL
DECLARE@Shift_EndDATETIME=NULL
DECLARE @Curr_HourINT= DATEPART(HOUR, @Date_Start)
DECLARE @Curr_DateDATE= CAST(@Date_start AS DATE)
DECLARE @Hour1TIME='07:00:00'
DECLARE @Hour2TIME='19:00:00'
-- Insert statements for procedure here
DROP TABLE IF EXISTS #Event;
DROP TABLE IF EXISTS #EventDur;
CREATE TABLE #Event
(
EventID INT NOT NULL
,EventState BIT NOT NULL
,EventTS DATETIME NOT NULL
,PRIMARY KEY CLUSTERED (EventTS,EventID)
);
CREATE TABLE #EventDur11
(
DurTime1 INT NULL
);
-- INSERT INTO #EVENT FROM dbo.DownTime
IF @Curr_Hour BETWEEN 7 AND 19
BEGIN
SET @Shift_Begin = CONVERT(DATETIME, (SELECT SUBSTRING(CONCAT(@Curr_Date, ' ', @Hour1), 1, 19)), 120)
SET @Shift_End = CONVERT(DATETIME, (SELECT SUBSTRING(CONCAT(@Curr_Date, ' ',@Hour2), 1, 19)), 120)
--SELECT @Shift_Begin AS 'BEGIN', @Shift_End AS 'END'
INSERT INTO #Event
SELECT
TimerID, CoaterDownFlag, t_stamp
FROM
dbo.DownTime
WHERE t_stamp BETWEEN @Shift_Begin AND @Shift_End
END
IF @Curr_Hour > 19 -- NIGHT SHIFT, 1900 TO (DATE + 1) 0700
BEGIN
SET @Shift_Begin = CONVERT(DATETIME, (SELECT SUBSTRING(CONCAT(@Curr_Date, ' ',@Hour2), 1, 19)), 120)
SET @Shift_End = DATEADD(DAY,1,CONVERT(DATETIME, (SELECT SUBSTRING(CONCAT(@Curr_Date, ' ',@Hour1), 1, 19)), 120))
INSERT INTO #Event
SELECT
TimerID, CoaterDownFlag, t_stamp
FROM
dbo.DownTime
WHERE CAST(t_stamp as date) BETWEEN @Shift_Begin AND @Shift_End
END
IF @Curr_Hour < 7
BEGIN
SET @Shift_Begin = DATEADD(DAY,-1,CONVERT(DATETIME, (SELECT SUBSTRING(CONCAT(@Curr_Date, ' ',@Hour2), 1, 19)), 120))
SET @Shift_End = CONVERT(DATETIME, (SELECT SUBSTRING(CONCAT(@Curr_Date, ' ',@Hour1), 1, 19)), 120)
INSERT INTO #Event
SELECT
TimerID, CoaterDownFlag, t_stamp
FROM
dbo.DownTime
WHERE CAST(t_stamp as date) BETWEEN @Shift_Begin AND @Shift_End
END
IF @Curr_Hour BETWEEN 7 AND 19
BEGIN
--THESE IF STMTS ATTEMPT TO RETREIVE DOWN TIME THAT STARTED BEFORE AND CONTINUED
--AFTER THE SHIFT START AND STOP TIMES
DELETE FROM #Event WHERE CAST(EventTS AS TIME) NOT BETWEEN @Hour1 AND @Hour2
IF (SELECT TOP 1 EventState
FROM #Event
ORDER BY EventID ASC) = 0
INSERT INTO #EventDur11 (DurTime1)
SELECT DATEDIFF(SECOND, @Hour1, CAST((SELECT TOP 1 EventTS FROM #Event ORDER BY EventID ASC) AS TIME(2)));
IF (SELECT TOP 1 EventState
FROM #Event
ORDER BY EventID DESC) = 1
INSERT INTO #EventDur11 (DurTime1)
SELECT DATEDIFF(SECOND, CAST((SELECT TOP 1 EventTS FROM #Event ORDER BY EventID DESC) AS TIME(2)), @Hour2);
WITH cteDryDur AS
(
SELECT EventID,EventState,EventTS
,RawDur = CAST(LEAD(EventTS,1) OVER (ORDER BY EventTS,EventID)-EventTS AS FLOAT)
FROM #Event
)
INSERT INTO #EventDur11 (DurTime1)
SELECT
EventDuration = RawDur * 3600 * 24
FROM cteDryDur
WHERE EventState = 1
ORDER BY EventTS,EventID;
END
ELSE -- HOUR > 1900
BEGIN
--DELETE FROM #Event WHERE EventTS < @Hour2
IF (SELECT TOP 1 EventState
FROM #Event
ORDER BY EventID ASC) = 0
INSERT INTO #EventDur11 (DurTime1)
SELECT DATEDIFF(SECOND, @Hour2, CAST((SELECT TOP 1 EventTS FROM #Event ORDER BY EventID ASC) AS TIME(2)));
IF (SELECT TOP 1 EventState
FROM #Event
ORDER BY EventID DESC) = 1
INSERT INTO #EventDur11 (DurTime1)
SELECT DATEDIFF(SECOND, CAST((SELECT TOP 1 EventTS FROM #Event ORDER BY EventID DESC) AS TIME(2)), @Hour1);
WITH cteDryDur AS
(
SELECT EventID,EventState,EventTS
,RawDur = CAST(LEAD(EventTS,1) OVER (ORDER BY EventTS,EventID)-EventTS AS FLOAT)
FROM #Event
)
INSERT INTO #EventDur11 (DurTime1)
SELECT
EventDuration = RawDur * 3600 * 24
FROM cteDryDur
WHERE EventState = 1
ORDER BY EventTS,EventID;
END
SELECT SUM(DurTime1) AS Tot_Sec FROM #EventDur11
END
GO
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply