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

I think I got it.

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

I joined on

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

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

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

:astonished: 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.

2 Likes

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

fetches 30,000 rows for one machine for the section

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?

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:

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:

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

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?