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