# 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),
as Total_Seconds

FROM (
SELECT  Code, t_stamp, Line,

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
ORDER BY t_stamp), '2100-01-01')
) subq
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
``````
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

``````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),
as Total_Seconds, newTable.Description as Description

FROM (
SELECT  Code, t_stamp, Line,

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
ORDER BY t_stamp), '2100-01-01')
) subq
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
``````

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),
as Total_Seconds, newTable.Description as Description

FROM (
SELECT  Code, t_stamp, Line,

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
ORDER BY t_stamp), '2100-01-01')
) subq
Left Join newTable on subq.Line= newTable.Line and subq.Code=newTable.Code
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
``````

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),

FROM (
SELECT  myTable.Code,
t_stamp, myTable.Line,

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
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
`````` 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.