# Calculating Total Time in Seconds

**URL:** <https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322>\
**Category:** Ignition\
**Created:** [July 28, 2021, 6:44pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322 "2021-07-28T18:44:54Z")\
**Posts on this page:** 19\
**Page:** 4

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [December 3, 2021, 7:14pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/61 "2021-12-03T19:14:28Z")

</div>

Three columns. Machine, Fault Code, Fault Description

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [December 3, 2021, 7:25pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/62 "2021-12-03T19:25:10Z")

</div>

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

* * *

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

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [December 17, 2021, 8:47pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/63 "2021-12-17T20:47:07Z")

</div>

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:

```auto
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 :

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

```

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [December 17, 2021, 9:40pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/64 "2021-12-17T21:40:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [December 20, 2021, 1:33pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/65 "2021-12-20T13:33:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [December 20, 2021, 1:48pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/66 "2021-12-20T13:48:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [December 20, 2021, 2:43pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/67 "2021-12-20T14:43:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 20, 2021, 2:45pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/68 "2021-12-20T14:45:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [December 20, 2021, 3:02pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/69 "2021-12-20T15:02:42Z")

</div>

had trouble finding all of them to alias them

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

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [April 27, 2022, 1:16pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/70 "2022-04-27T13:16:49Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/c/ec3c3182a8256279cbb26421ceeab6ca726e4b19.png)  
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…

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 10, 2022, 8:42pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/71 "2022-05-10T20:42:31Z")

</div>

If I group this query by

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

```

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

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

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

```auto
,isnull(lead(t_stamp) OVER (ORDER BY t_stamp),current_timestamp) as test2

```

```auto
,coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),	CASE WHEN current_timestamp < :endLead THEN current_timestamp ELSE :endLead END) AS lead,t_stamp

```

---

<div class="post-metadata">

**Author:** ![Zach\_Larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zach_larson/32/54388_2.png) [@Zach\_Larson](https://forum.inductiveautomation.com/u/Zach_Larson)\
**Post date:** [February 8, 2024, 4:26pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/72 "2024-02-08T16:26:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [May 14, 2024, 7:21pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/73 "2024-05-14T19:21:42Z")

</div>

Would you be able to provide some additional insight on your changes? I'm also trying to get duration of machine/asset states from a single table. I have something that works but the execution time seems a bit long.

I have the following query, which feels like a bastardization of the original query from pturmel but it seems to work as expected. (It grabs and trims events that cross the timestamp boundaries.) The execution time is around 2.5 seconds on a 4.5 million row table to fetch a result set of ~12,000 rows.

> **State Duration By Asset SQL**
>
> ```sql
> SELECT
> AssetId,
> StateId,
> StateTimestamp as EventStart,
> EventEnd,
> dateDiff(SECOND, StateTimestamp,EventEnd) As duration 
> FROM (
> Select
> AssetId,
> StateId,
> CASE WHEN StateTimestamp < DATEADD(HOUR, -168, DATEADD(HOUR, DATEDIFF(hour, 0, CURRENT_TIMESTAMP), 0)) 
> THEN DATEADD(HOUR, -168, DATEADD(HOUR, DATEDIFF(hour, 0, CURRENT_TIMESTAMP), 0)) ELSE StateTimestamp END AS StateTimestamp,
> COALESCE(lead(StateTimestamp) Over (Partition By AssetId Order By StateTimestamp), CURRENT_TIMESTAMP) As EventEnd
> FROM(
> Select
> t1.AssetId,
> t1.StateId,
> StateTimestamp,
> lag(t1.stateId) OVER (PARTITION BY T1.AssetId ORDER BY StateTimestamp) as PriorValue
> From dbo.OEE_Asset_State t1
> Left Join (
> Select AssetId, max(StateTimestamp) As PriorStateTs
> FROM dbo.OEE_Asset_State
> Where StateTimestamp < DATEADD(HOUR, -168, DATEADD(HOUR, DATEDIFF(hour, 0, CURRENT_TIMESTAMP), 0))
> Group By AssetId
> ) sub1 On t1.AssetId = sub1.AssetId
> Left Join (
> Select AssetId, min(StateTimestamp) As NextStateTs
> FROM dbo.OEE_Asset_State
> Where StateTimestamp >= CURRENT_TIMESTAMP
> group by AssetId
> ) sub2 On t1.AssetId = sub2.AssetId
> Where StateTimestamp >= coalesce(sub1.PriorStateTs, DATEADD(HOUR, -168, DATEADD(HOUR, DATEDIFF(hour, 0, CURRENT_TIMESTAMP), 0)))
> And StateTimestamp < coalesce(sub2.NextStateTs, CURRENT_TIMESTAMP)
> ) Inner1
> WHERE StateId != PriorValue or COALESCE(PriorValue, -100) = -100
> )Outer1
> ORDER BY AssetId ASC, EventStart DESC
> 
> ```

My table columns are `EntryId, AssetId, StateId, StateTimestamp`  
I have the default clustered index on `EntryId` and a compound unclustered index on `AssetId, StateTimestamp` including `StateId`.

According to the execution plan, my query seems to be fetching the full 4.5 million rows twice, once for for the base data to aggregate and once for finding the first `StateId` value change outside the lower timestamp range specified. That does eventually get filtered down a much smaller number but I feel like it shouldn't be grabbing all the rows from the table each time.

I'm using Microsoft SQL server 2014.

> **Execution Plan**
>
> ![ExecutionPlan](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/c/4c612b1a7bcbd8994ad20c1683fb4a08ec7bd830.png)

Is 2.5 seconds on a 4.5 million row table an acceptable time for this query?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 14, 2024, 8:14pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/74 "2024-05-14T20:14:40Z")

</div>

No, your problem is that you aren't performing any pre-filtering by timestamp in the **innermost** subqueries. If you are you are regularly getting data, simply expanding the boundaries by a fixed amount in the innermost comparison will often suffice.

The outer WHERE clauses that prune the result to the true output boundaries would remain in place.

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [May 14, 2024, 9:00pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/75 "2024-05-14T21:00:59Z")

</div>

So add a `WHERE` statement to the first innermost query, and change the other two innermost queries to define a set range in their `WHERE` statements. Something like this then:

```sql
SELECT
	AssetId,
	StateId,
	StateTimestamp as EventStart,
	EventEnd,
	dateDiff(SECOND, StateTimestamp,EventEnd) As duration 
FROM (
	Select
		AssetId,
		StateId,
		StateTimestamp,
		COALESCE(lead(StateTimestamp) Over (Partition By AssetId Order By StateTimestamp), '2024-03-01') As EventEnd
	FROM(
		SELECT
			t1.AssetId,
			StateId,
			StateTimestamp,
			PriorValue
		FROM(
			Select
				AssetId,
				StateId,
				StateTimestamp,
				lag(stateId) OVER (PARTITION BY AssetId ORDER BY StateTimestamp) as PriorValue
			From dbo.OEE_Asset_State
			WHERE StateTimestamp BETWEEN DATEADD(HOUR, -336, DATEADD(HOUR, DATEDIFF(hour, 0, '2024-03-01'), 0)) AND DATEADD(HOUR, 168, DATEADD(HOUR, DATEDIFF(hour, 0, '2024-03-01'), 0))
		) t1
		Left Join (
			Select AssetId, max(StateTimestamp) As PriorStateTs
			FROM dbo.OEE_Asset_State
			Where StateTimestamp BETWEEN DATEADD(HOUR, -336, DATEADD(HOUR, DATEDIFF(hour, 0, '2024-03-01'), 0)) AND DATEADD(HOUR, -168, DATEADD(HOUR, DATEDIFF(hour, 0, '2024-03-01'), 0))
			Group By AssetId
		) sub1 On t1.AssetId = sub1.AssetId
		Left Join (
			Select AssetId, min(StateTimestamp) As NextStateTs
			FROM dbo.OEE_Asset_State
			Where StateTimestamp BETWEEN '2024-03-01' AND DATEADD(HOUR, 168, DATEADD(HOUR, DATEDIFF(hour, 0, '2024-03-01'), 0))
			group by AssetId
		) sub2 On t1.AssetId = sub2.AssetId
		Where StateTimestamp >= coalesce(sub1.PriorStateTs, DATEADD(HOUR, -168, DATEADD(HOUR, DATEDIFF(hour, 0, '2024-03-01'), 0)))
			And StateTimestamp < coalesce(sub2.NextStateTs, '2024-03-01')
	) Inner1
	WHERE StateId != PriorValue or COALESCE(PriorValue, -100) = -100
)Outer1
ORDER BY AssetId ASC, EventStart DESC

```

For the set time offset around the given time window, what should I base that offset on? The longest time period we see between states? In my example here I'm using a week for testing, but we have had machines down for up to 3 months while doing upgrades and such.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 14, 2024, 9:12pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/76 "2024-05-14T21:12:26Z")

</div>

> [@ryan.white](#):
>
> but we have had machines down for up to 3 months

Ew!

OK, you may need another scalar select for each endpoint that finds the last TS prior to the period (use Top 1 Order by StateTimestamp Desc) and another that finds the first TS after the period.

(Are you not parameterizing the begin and end points for a reason?)

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [May 14, 2024, 9:22pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/77 "2024-05-14T21:22:32Z")

</div>

> [@pturmel](#):
>
> (Are you not parameterizing the begin and end points for a reason?)

I was building this for a query tag and didn't want to use `DECLARE` as I was under the understanding that the JDBC driver technically doesn't support that. Didn't think to parameterize for building and testing.

> [@pturmel](#):
>
> OK, you may need another scalar select for each endpoint that finds the last TS prior to the period (use Top 1 Order by StateTimestamp Desc) and another that finds the first TS after the period.

Possible other solution to this, what if I set up something to grab the last written state from each `AssetId` present and insert a new row with the same state but current timestamp? Run it at a set rate of 1 day or similar, something to basically make sure I will always have at least 1 state value per asset in the timestamp offset range I'm selecting.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 14, 2024, 9:24pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/78 "2024-05-14T21:24:46Z")

</div>

> [@ryan.white](#):
>
> Didn't think to parameterize for building and testing.

If you develop/test in the named query editor, you can use colon-delimited parameters in multiple places.

> [@ryan.white](#):
>
> Possible

Not an unreasonable approach. Certainly would simplify the query.

---

<div class="post-metadata">

**Author:** ![Zach\_Larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zach_larson/32/54388_2.png) [@Zach\_Larson](https://forum.inductiveautomation.com/u/Zach_Larson)\
**Post date:** [May 21, 2024, 6:51pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/79 "2024-05-21T18:51:22Z")

</div>

I think what I did:

I used "with" to create my subqueries for the faults by line.

Then I think used a union of sub queries from the "with" and filtered in where.

The table is indexed.

The fetch uses between to default to a range scan if it fails to get the index if I remember right.

I think if I had a machine down 3 months, it would not show in this list, so I would coalesce or case a default value to get he row.

And used things that Pturmel recommended me.

[Previous page](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322.md?page=3)