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

Could you subtract the time to change the starting time of the day in your group by? E. G. Subtract 7 hrs so that the group by still works as per normal grouping by the day year?
If you ever have multiple shifts I woukd use a case and for each shift check the time is >= and < a certain hour and assigning shift numbers for each then grouping on that

1 Like

I had a similar idea on Saturday

I am going to try something like:

format(dateadd(hr,-8,t_stamp),yyyy-mmm-dd)

I will be happily surprised if it works. Stack Exchange has threads struggling a decade with the issue.

I am trying to work on this, but when I use dateadd(), I am only getting 1-12 for hours with hh

when i look up the function though, I don’t see how to get the 1-24, military time.

update: adjusting the start time 8 hours is working, but i couldnt get the format in a format to produce the military time on the dateadd

when you run this: dateadd(hr,-8,t_stamp) you are telling SQL to subtract 8 hours from t_stamp. However the date is presented in the end is irrelevant, an hour is an hour independent of if you are working with a 12-hour clock or a 24-hour clock.

For instance both dateadd(hr,-8,5/10/2022 3:00 PM) and dateadd(hr,-8,5/10/2022 15:00) will both yield a result of 5/10/2022 7:00. How that time is formatted and presented to the user is up to you.

Try this (assuming that t_stamp is a datetime type):

format(dateadd(hr,-8,t_stamp),'yyyy-mmm-dd HH:mm:ss')
1 Like

Anything between midnight and 7am are considered the previous day, correct? Then a CASE statement should work for you.

SELECT SUM(hours), d_stamp
FROM (SELECT hours, FORMAT(CASE WHEN DATEPART(hour, t_stamp)<7 THEN DATEADD(day, -1, t_stamp) ELSE t_stamp END, 'yyyy MMM dd') as d_stamp
      FROM mytable
      WHERE t_stamp >= '2022-01-01 07:00:00' and t_stamp < '2022-01-31 07:00:00') subq
group by d_stamp, hours
order by d_stamp, hours
1 Like

Thanks, I see how that would work too.
That is more flexible, so I could gather up different time frames than days, and mask them as days to use format to group them up.

For today with this application I am using something like:

select 
line,
net,
format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') as Day
from mytable
group by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd'), line, net

I was messing with the military time to verify this was working right.
But, I found a way to add another value I track daily, and compare that.

Edit to simplify example, changed hours column to be net column for clarity.

Thanks for the additional way with the more adaptability.

At some point you are going to be asked to collect and summarize by rotating shifts with arbitrary rotation patterns. It can get complicated. The following generic solution may help you create the correct joins against your production records:

You’ll have to tweak it for use with SQL Server.

1 Like

Switching work shift every week seem so awful, their Mondays are way worse than mine imo.

I thought:
subquery select to define weeks from modulus of 3 from a start week
select case statements to group shift and week combo

It would be less efficient than the function?
I don’t like SQL functions because I can’t see them from within Ignition.

What do the $$ do in the code?
What is the “sid”?
There are some double colons in some of the selects, what do they do?

I appreciate you showing it to me, and I’d like to learn the tradeoffs

They are the start/end markers for the procedure code. SQL Server is a bit different, IIRC.

They are SQL-standard alternate syntax for CAST(). a::b is the same as CAST(a AS b).

1 Like

Short for shift_id within the generator function.

1 Like

The tweaking for SQL Server will require a substitute for PostgreSQL’s built-in generate_series(). I think SQL Server expects you to use a recursive Common Table Expression for such things.

1 Like

I need to join the query I worked on from this thread to the one you helped me with in another thread
here

As much as the duration query looks at t_stamp, when I join it, I can still join on t_stamp, and the duration query’s references to t_stamp for getting time won’t be impacted?
Or do i join on like machine name, and then will that mean references to like durationTable.t_stamp will not be impacted?
The queries are so big, that it is hard for me to retain how they are in my mind.

I join on the inner most table?

so far my attempts to join have been unsuccessful like not compiling

or is there a way to join the tables on format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd')

like joining on a subquery, is it a thing you can do?


I thought recursion was to code fast at the cost of efficiency, and should be avoided.

oh I can join on a subquery

SQL LEFT JOIN Subquery Alias - Stack Overflow

1 Like

I am trying to join two tables that are kind of like these:
(these are simplified versions so I can understand the complexity of the interfacing, yet nothing top secret so to speak easy, is disturbed)

select 
format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') as Day
, line
, sum(isnull(t.net,0)) as Net
, sum(isnull(t.hours,0)) as Hours
from(
       select cast(right(autoTable.line, len(autoTable.line)-2) as int) as line
       , autoTable.t_stamp
       , Row_Number() over (partition by manualTable.line, autoTable.t_stamp order by autotable.t_stamp desc) as row,
       , isnull(autoTable.net) as net
       , isnull(manualTable.hours,0) as hours
       from autoTable left join manualtable on
       cast(right(autoTable.line, len(autoTable.line)-2) as int) = case when manualTable.line not like '%k' 
       then cast(right(manualTable.line, len(manualTable.line)-2) as int) end)
       and format(autoTable.t_stamp, 'MM/dd/yy') = Format(manualTable.recordDate,'MM/dd/yy')
       --and shift joining magic not listed here

       where autoTable.line='B2' and manaulTable.hours>0
       and autoTable.t_stamp between DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0)) AND DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
        group by --all that stuff
)t
where t.row<2 -- eliminates duplicates manual entries while allowing review of all manual entries, not a removable feature
group by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd'), line
order by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') desc

and then another complicated table

select format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') as Day, sum(datediff(second,sub2.t_stamp,lead)) as Seconds, sum(datediff(second,sub2.t_stamp,lead))/60.0/60.0 as hrs	 
FROM (
	SELECT  sub1.line, 
			sub1.code, 
			case when sub1.t_stamp < DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0))  then DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0))  else sub1.t_stamp end as t_stamp, 
			coalesce(lead(sub1.t_stamp) OVER (ORDER BY sub1.t_stamp),	CASE WHEN current_timestamp <  DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) THEN current_timestamp ELSE DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) END) AS lead
	from(	
		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)) )) 				
	) sub1	
	where t_stamp <= DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
)sub2 
where sub2.code=250 	
group by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd'), sub2.Line
order by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') desc

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?