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
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:
- when my t_stamp is between shift start and end
- 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.