Calculating Total Time in Seconds

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.