# Query help sql date range between grouping 7am to 7am

**URL:** <https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315>\
**Category:** Ignition\
**Created:** [May 6, 2022, 6:33pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315 "2022-05-06T18:33:42Z")\
**Posts on this page:** 18\
**Page:** 2

<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 11, 2022, 5:03pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/21 "2022-05-11T17:03:58Z")

</div>

I think I got it.

after the " `)t`"  
I put like `left join ( most that query) sub3`

I joined on

```auto
on format(dateadd(hh,-8, t.t_stamp), 'yyyy-MMM-dd') = sub3.day

```

query takes 1.175 seconds right now

gets me the last 60days of 7am to 7am of manual info, auto info, and durations of codes for one machine

Thanks for help

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 11, 2022, 5:13pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/22 "2022-05-11T17:13:26Z")

</div>

> [@zacharyw.larson](#):
>
> 1.175 seconds

Wow, that's quite a long time. How many rows does that return?

---

<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 11, 2022, 5:22pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/23 "2022-05-11T17:22:14Z")

</div>

38 rows is the final

it is gathering a ton more initially from both the subqueries

one of them is collecting the total of all time when the machine was running

one is getting manual and auto data for the last 60 days, no repeats

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 11, 2022, 5:25pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/24 "2022-05-11T17:25:54Z")

</div>

😲 That seems like ages for only 38 rows.

I will suggest that there is a better way. I don’t really know/understand your schema or what your trying to accomplish, but anytime a query takes over a couple hundred ms to run and I’m not collecting 10,000 + rows, I take a step back.

---

<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 11, 2022, 5:29pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/25 "2022-05-11T17:29:13Z")

</div>

it is collecting over 10,000 rows before the final trim

fetches 30,000 rows for one machine for the section

```auto
select line, code, eventTable.t_stamp as t_stamp
		from eventTable 
		WHERE t_stamp >= coalesce((
					SELECT TOP 1 t_stamp FROM eventTable
					WHERE eventTable.t_stamp < DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0)) and code <260 and Line= 'B2'												
					ORDER BY t_stamp DESC), '2021-01-01') AND 
				t_stamp <= coalesce((
					SELECT TOP 1 t_stamp FROM eventTable
					WHERE eventTable.t_stamp > DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) and code <260 and Line='B2'						
					ORDER BY eventTable.t_stamp), dateadd(hour,5, DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) )) 				
	

```

500ms here for 30k+ rows

60ms the other subquery gets 200 rows

trims it all down to 38 rows, organized by day over 60 days

would have all 60days show, but they don’t run all days

I thought 1.2 seconds was doing really well for this

Though I also think I will set it as a query tag that gets driven once a day, when I figure out how in the next few hours.

Is there a way to make the subqueries much faster?

---

<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 11, 2022, 7:32pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/26 "2022-05-11T19:32:12Z")

</div>

I am getting an error index 73 is out of range.

trying to speed up the query  
if I am using this in my query:

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

```

Can I do it in the declare instead?  
W3schools, unless I missed it, didn’t really cover declare and set.  
so before my query I would say like:

```auto
declare @thisVariable varchar(50)
set @thisVariable = (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 )

```

or if I say

```auto
declare @thisVariable varchar(50)
set @thisVariable = 'B2'

```

Then I get an index 55 out of range

* * *

getting 3 to 1.5 seconds for the query when I use execute now that I am using case statement for each machine

* * *

found uptime and downtime are stored in plc, in tags, and now adding to transactions so I am removing the join to the query for the durations for at least a few machines that I can

* * *

How can I speed the query up? how do I fix those index errors and use declare and set to get through some of the case statement early?

---

<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:** [October 4, 2022, 5:32pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/27 "2022-10-04T17:32:52Z")

</div>

> [@zacharyw.larson](#):
>
> format(dateadd(hh,-8, t\_stamp), 'yyyy-MMM-dd')

This grouped by the day offset by 8 hours.

How can I group in sets of 8hrs though?

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [October 4, 2022, 7:49pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/28 "2022-10-04T19:49:13Z")

</div>

I think you need to add an index to t\_stamp and include the fields you're returning to that index.

Subqueries are typically very fast. You probably don't have an index on t\_stamp so it has to do a table scan to figure out what records apply to your query. You want to avoid table scans.

You probably don't have an index on the other 2 things in your where clause but I would check out performance after adding an index to t\_stamp before going crazy with indexes. Too many indexes can be a bad thing too. I typically find that adding an index to t\_stamp is sufficient on Ignition data.

It's good to put Ignition and SQL on different servers on large systems. I don't think that applies to what you're doing in this case but it's something to keep in mind if you're doing a lot of transactions.

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [October 4, 2022, 7:49pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/29 "2022-10-04T19:49:42Z")

</div>

If you're using MSSQL server, look into windowing functions.

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [October 4, 2022, 7:57pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/30 "2022-10-04T19:57:52Z")

</div>

Case statement is for doing inline conditional stuff. You don't want to assign that to a variable because that would mean you're doing dynamic SQL which is something you should avoid at all costs.

It's hard to tell what your schema is but typically you'll have a lookup table with a list of indexes (machine number) and descriptions. So you'd have a machineID and the machinename in that table. You could add another field to classify machines by groups or however you want to organize them. This allows you to collect the data with the machine index in your table and query it by the index. If you want to show the machine name you just join with your lookup table to show the machine name or to filter your data by a machine group.

---

<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:** [October 4, 2022, 8:08pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/31 "2022-10-04T20:08:51Z")

</div>

I did index my table.  
Tested my indexing to verify that the queries were using the index.  
The query is fantastic.

Yet, I still need help to find a way group into 8 hour sections for one of the requests I have been given.

I am using MSSQL.

I will check out the windowing functions thanks.

I avoid joins like the plague, but I do have one to get descriptions of some things.

---

<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:** [October 4, 2022, 8:13pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/32 "2022-10-04T20:13:25Z")

</div>

> [@zacharyw.larson](#):
>
> I avoid joins like the plague

This is a bad habit. Joins, when properly indexed themselves, are very efficient. Way more than large CASE statements.

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [October 4, 2022, 8:22pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/33 "2022-10-04T20:22:09Z")

</div>

100% agree.

---

<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:** [October 4, 2022, 8:28pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/34 "2022-10-04T20:28:30Z")

</div>

I avoid big case statements like the plague too. I appreciate the help.

If you have a way to group on 8 hours, I would much appreciate it.  
I am going to look into the windowing function which might solve it as you suggested, just trying to meet a deadline on another thing.

---

<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:** [October 4, 2022, 9:05pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/35 "2022-10-04T21:05:00Z")

</div>

I posted a _ **comprehensive** _ solution for grouping by shift some time ago (fully dynamic table-driven shift definitions):

> [@Script for periodically changing shift time](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058/3):
>
> Hi Berkay, I like to solve this kind of problem with a smart database, like PostgreSQL, where I can define a pattern and repeat it with table generating functions. I tweaked one of my implementations to serve as an example: create table shift\_schedules (shift\_id serial, shift\_name varchar(255), refdate date, repeat int, tzname varchar(255), primary key (shift\_id)); insert into shift\_schedules (shift\_name, refdate, repeat, tzname) values ('Berkay Cinar 3wk', '2021-07-26', 21, 'America/Ne…

It relies on a PostgreSQL table-generating function for efficiency and convenience, but that can be replaced with a recursive common table expression.

The key is, given a timestamp, produce the timestamp for the beginning of the shift that falls in. As a computed column in a subquery. Then the outer query can group on that start-of-shift timestamp.

The comprehensive solution provides a joinable, dynamically-generated set of shift periods that can also be used to truncate timespans.

---

<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:** [October 5, 2022, 1:39pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/36 "2022-10-05T13:39:56Z")

</div>

A dynamically-generated set of shifts sounds good for this.  
I don't understand how it works, though for sure if I had shift times that were changing, then I would use it.

I was hoping that since I was formatting for days for offset using addition, that there would be a way to scale the t\_stamp for grouping. If I scale down, then group 8 hours in an hour, or if I can scale up to 1 day per 8 hour shift, but then later reverse that, it seems like the best option.

Is there a kind of way to multiplex and demultiplex the grouping of t\_stamps?

---

<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:** [October 5, 2022, 1:54pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/37 "2022-10-05T13:54:01Z")

</div>

> [@pturmel](#):
>
> The key is, given a timestamp, produce the timestamp for the beginning of the shift that falls in. As a computed column in a subquery. Then the outer query can group on that start-of-shift timestamp.

Do this.

---

<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:** [October 5, 2022, 2:01pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/38 "2022-10-05T14:01:34Z")

</div>

Thanks, I can do that. Even when you quoted just that bit, I had to read a few times to grasp. Thanks again.

Seems so obvious now that you said it. Thanks so much.

[Previous page](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315.md?page=1)