Calculating Total Time in Seconds

Let me break this part down for you:

	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp < :begin_ts
		ORDER BY t_stamp DESC), '1900-01-01')
	  AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp > :end_ts
		ORDER BY t_stamp), '2100-01-01')

For lead() to work as we desire, it always needs two rows, the one we are “on”, and the next one. On the last row, lead is null because there’s no row after it. Which means, to compute durations at the boundaries of our desired time span, we have to include at least one row outside the time span on either end.
We can find the last row before our timespan like so:

SELECT TOP 1 t_stamp FROM myTable WHERE t_stamp < :begin_ts ORDER BY t_stamp DESC

We can find the first row after our timespan like so:

SELECT TOP 1 t_stamp FROM myTable WHERE t_stamp > :end_ts ORDER BY t_stamp

We are allowed to use ORDER BY in these subqueries because we are using TOP 1 to get just the first result. However, these two queries can return no row at all, which would be a null. So these are wrapped in the coalesce() function, supplying a far past date or a far future date when necessary.

Using these timestamps in the inner query gives that query all the rows in the desired timespan, plus one row just outside the timespan on either end. That gives the lead() function something to work with at the boundaries.

1 Like

You might want to consider using a table with two dates, let’s call them date_beg and date_end, in addition to all the other data you want with the event. When an event starts insert a record into the table, filling in date_beg but leaving date_end null. When the event ends, update the record, putting the end time into date_end. The query to determine the duration becomes trivial at this point.

These events don't end. Your approach would require recording each timestamp in two places. Not good. Competent databases can do intervals with lead() or lag() with essentially zero cost, guaranteeing consistent results.

When you said easily generated, did you mean there is an easier way to get the time between timestamps and sum them up?

Or did you mean a query similar to discussed here used with
lead(t_stamp) over (partition by machine order by t_stamp)
to omit other machines if the table has many machines

I meant using lead(). If you are using a query to get multiple machine’s results at once, you would use partition by machine in the OVER clause so that lead() looks for the next timestamp for the matching machine. If you use a WHERE clause in the query to only return results for a single machine, you can omit that part of the over clause.

I didn’t mean “easy” from your point of view, but from the database point of view.

2 Likes
select myCode as Fault_Code,count(myCode) as Stop_Occurences, 
       sum(datediff(second,
	(CASE WHEN  t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead or lead is null  THEN :endLead else lead END)))
             as Total_Seconds
FROM ( SELECT  myCode, t_stamp,
	coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :endLead THEN current_timestamp
                 ELSE :endLead END) AS lead
	from  myTable 
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp < :beginTS
		ORDER BY t_stamp DESC), '1900-01-01')
	AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp > :endLead
		ORDER BY t_stamp), '2100-01-01')	) subq
	where lead >= :beginTS and ( t_stamp  < :endLead ) and myCode is not null 
group by myCode
order by sum(datediff(second,t_stamp,lead)) desc

sample results
image

How do I append the table to show the reason from a dictionary.
I have a dictionary with all the code matched to text for the reason.
For example in project library I have a script dictionary:

machineCode_Map ={
    42 : "We lost the towel"
    66: "The drones betrayed us"
}
def get_machineCode(v):
    return machineCode_Map[v]

How can I append to the table to show the reason text next to the codes?

I know one way is for me to make a table of my dictionary, then join.
I have many machines though, and I was going to make this work for multiple machines.
I have made a transaction group already with Machine_ID columns.

Therefore, I am hoping on the perspective table component I can somehow attached the dictionary values based on the code in that row. Maybe this is unlikely.

That's what I would do.

2 Likes

What if you had 60 machines with unique dictionaries?


where lead >= :beginTS and ( t_stamp  < :endLead ) and Code is not null and ( :machine=-1 )

I got this working to show me all the machines codes.

I was thinking like

where lead >= :beginTS and ( t_stamp  < :endLead ) and Code is not null 
and ( :machine=-1 or Line=Case when :machine=1 then 'blueMachine' else null end)

Three columns. Machine, Fault Code, Fault Description

2 Likes

Yah, I see.
I forgot about solution and location as well.

I think I need to make a table for all the machines with columns:
machine, fault code, fault description, fault solution, fault location

Then I join this table. In this case I use only description. In other spots, I will need to show the solutions and locations.


Lol I was like why did nobody comment on my where clause?
I guess when I used the right parenthesis it works


select Line, Code as Fault_Code,count(Code) as Stop_Occurences, 
	sum(datediff(second,
	(CASE WHEN  t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead or lead is null  THEN :endLead else lead END)))
	as Total_Seconds

FROM (
	SELECT  Code, t_stamp, Line,
	coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :endLead 
		THEN current_timestamp ELSE :endLead END) AS lead
	
	from    myTable 
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp < :beginTS
		ORDER BY t_stamp DESC), '1900-01-01')
	AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp > :endLead
		ORDER BY t_stamp), '2100-01-01')
	) subq
	where lead >= :beginTS and ( t_stamp  < :endLead ) and 
		Code is not null and ( :machine=-1 or 
		Line=Case 	when :machine=1 then 'blueMachine' 
				When :machine=2 then 'redMachine'
                When :machine=60 then 'sixtiethMachine'
		else null end )

group by Line, Code
order by sum(datediff(second,t_stamp,lead)) desc
2 Likes

Now I have made my static table that describes the fault code.

I believe I will need to add a new select column, then add a join clause just before the group clause.

Getting an error by “Left” it says

I had expected maybe:

select Line, Code as Fault_Code,count(Code) as Stop_Occurences, 
	sum(datediff(second,
	(CASE WHEN  t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead or lead is null  THEN :endLead else lead END)))
	as Total_Seconds, newTable.Description as Description

FROM (
	SELECT  Code, t_stamp, Line,
	coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :endLead 
		THEN current_timestamp ELSE :endLead END) AS lead
	
	from    myTable 
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp < :beginTS
		ORDER BY t_stamp DESC), '1900-01-01')
	AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp > :endLead
		ORDER BY t_stamp), '2100-01-01')
	) subq
	where lead >= :beginTS and ( t_stamp  < :endLead ) and 
		Code is not null and ( :machine=-1 or 
		Line=Case 	when :machine=1 then 'blueMachine' 
				When :machine=2 then 'redMachine'
                When :machine=60 then 'sixtiethMachine'
		else null end )

Left Join myTable on myTable.Line=newTable.Line and myTable.Code = newTable.Code
group by Line, Code
order by sum(datediff(second,t_stamp,lead)) desc

also attempting variations of :

Left Join newTable on subq.Line= newTable.Line and subq.Code=newTable.Code

Joins need to go between the FROM clause and the WHERE clause

I think you’re trying to put the fault code in the final select, so you’d simply need to move your left join between the ) subq line and the where lead... line

you’d need to replace your references after the ON statement though. one would need to use subq.Code (i think) and the other the table name (or alias) for your joined table

select Line, Code as Fault_Code,count(Code) as Stop_Occurences, 
	sum(datediff(second,
	(CASE WHEN  t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead or lead is null  THEN :endLead else lead END)))
	as Total_Seconds, newTable.Description as Description

FROM (
	SELECT  Code, t_stamp, Line,
	coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :endLead 
		THEN current_timestamp ELSE :endLead END) AS lead
	
	from    myTable 
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp < :beginTS
		ORDER BY t_stamp DESC), '1900-01-01')
	AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp > :endLead
		ORDER BY t_stamp), '2100-01-01')
	) subq
Left Join newTable on subq.Line= newTable.Line and subq.Code=newTable.Code
	where lead >= :beginTS and ( t_stamp  < :endLead ) and 
		Code is not null and ( :machine=-1 or 
		Line=Case 	when :machine=1 then 'blueMachine' 
				When :machine=2 then 'redMachine'
                When :machine=60 then 'sixtiethMachine'
		else null end )


group by Line, Code
order by sum(datediff(second,t_stamp,lead)) desc

says ambiguous column name Code
It seems to be upset with Code in the last Where clause.
If I write myTable.Code it isn’t happy with that either.
Says multipart identifier could not be bound.

am I using the correct join?
I just want to add the description from the second table.

Ambiguous means it doesn’t know which table/alias to use. Add the proper alias.

Says multipart identifier could not be bound.
or
Says ambiguous column name code

I don’t seem able to escape these, not sure what the proper alias is.
I tried the table name and the subq.

myTable isn’t “visible” at the outermost level. It is part of subq. Pick subq.Code or newTable.Code. Everywhere in the outer section where you use Code by itself. I see three places.

had trouble finding all of them to alias them

select subq.Line, subq.Code as Code,count(subq.Code) as Occurences, sum(datediff(second,
	(CASE WHEN  t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead or lead is null  THEN :endLead else lead END)))as Seconds, newTable.Description as Description

FROM (
	SELECT  myTable.Code,
		t_stamp, myTable.Line,
	coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :endLead THEN current_timestamp ELSE :endLead END) AS lead
	
	from    myTable 
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp < :beginTS
		ORDER BY t_stamp DESC), '1900-01-01')
	AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable 
		WHERE t_stamp > :endLead
		ORDER BY t_stamp), '2200-01-01')
	) subq
	
	Inner Join newTable on subq.Line=newTable.Line and subq.Code=newTable.Code
	where lead >= :beginTS and ( t_stamp  < :endLead ) and subq.Code is not null and (subq.Code<1100) and ( :machine=-1 or 
		subq.Line=Case 	when :machine=1 then 'b1' 
				When :machine=2 then 'b2'
				When :machine=3 then 'b3'
				When :machine=4 then 'b4'
				When :machine=5 then 'b5'
				When :machine=6 then 'b6'
				When :machine=11 then 'c1'
				When :machine=12 then 'c2'
				When :machine=13 then 'c3'
				When :machine=14 then 'c7'
				When :machine=15 then 'c5'
				When :machine=16 then 'c6'
		else null end )

group by subq.Line, subq.Code, newTable.Description
order by sum(datediff(second,t_stamp,lead)) desc

image
I need a way to only get the lead when the code is under 1500

update:
I got it.

pturmel’s model works great if it is one machine like in the initial ask.
I broke it when I added more machines.
The codes from multiple machines time stamps were messing up the lead t_stamp.
So I added a bunch of filtering.

Works great now.

Basically if it says “where”, I also added filtering for a code < 1500 and a line= case when :machine…

If I group this query by

format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') 

how do I join it to to another query that will be grouped by

format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') 

both queries have subqueries

still join on mytable from that inner most subquery?
and if I join, I think my t_stamps would be messed up?


it turned out that I could join a subquery on a subquery

left join ( the entire subquery) sub1
on on format(dateadd(hh,-8, t.t_stamp), 'yyyy-MMM-dd') = sub1.day

I apparently need someone to post or I can’t post on this thread because 3 self replies is the limit.
I think I will have more things to do with this duration query though.


sept 2022

I am using a union per machine to get more than one machine in the query because my table has all machines in one table.
I haven’t figured out a way to get the t_stamps of one machine, then the next, and then the next.
I think it must be some kind of partition by over combination.


I think thought these were the same, with isnull being faster.
Seems to be throwing index errors.

,isnull(lead(t_stamp) OVER (ORDER BY t_stamp),current_timestamp) as test2
,coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),	CASE WHEN current_timestamp < :endLead THEN current_timestamp ELSE :endLead END) AS lead,t_stamp

I'm a little rusty.
Been trying to get fault durations across multiple lines in one query.

Select top 1 will not work when I have more lines because t_stamp could be any of 12 production lines, and I still need the other 11 production line codes prior to the shift.


So I attempted to have a where clause to obtain:

  1. when my t_stamp is between shift start and end
  2. when concat(line,t_stamp) IN (subquery for concatenated Line and max(t_stamp) grouped by line)

However, this seems to be so slow that it times out.

Use a union instead of a complex where clause.
I am sure it is a common problem though, so I would appreciate input, thanks.


Maybe I solved it using the union. Getting them in under 2/10ths a second.