This is an excellent method to do this, going to tuck this one away for future use, thank you.
SELECT Year_Month, Total
FROM (
select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total
from tableName
where (t_stamp>DATEADD(month, -6, GETDATE()))
group by FORMAT(t_stamp, 'yyyy-MMM')
) subq
ORDER BY earliest
I converted the query back to not showing the months for those months without data.
I like the function.
I don't like that the functions are not more accessible within Ignition though.
I linked the function post in the first post, and here
SELECT Year_Month, Total
FROM (
select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total
from tableName
where (t_stamp>DATEADD(month, -6, GETDATE()))
group by FORMAT(t_stamp, 'yyyy-MMM')
) subq
ORDER BY earliest
This is my query
Now I want to count my infeed and outfeed that are in two more columns in my table, collected on stops.
The sum of some column was easy. Each record is the number from that recorded event.
My infeed is like 1002 then ten more are fed in, and it reads 1012.
So I think I need to use Lead because my infeed is the next infeed value - current infeed cell value.
lead(infeed,1,0) over order by [t_stamp]-infeed as countinfeed
However it complains constantly that what I am doing is not allowed because I need some of it in the group section
one of my attempts for the sub query
select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month,
sum(someColumn) as Total, lead(infeed,1,0) over order by [t_stamp]-infeed as countinfeed
from tableName
where (t_stamp>DATEADD(month, -6, GETDATE()))
group by FORMAT(t_stamp, 'yyyy-MMM')
Making it too difficult, and need to do the subtraction before it goes into my query
Or maybe I need to partition after the subquery
I made a little progress maybe.
select subq.Line,Year_Month, Stops, low, high
from (
select min(t_stamp) as earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, coalesce(count(Code),0) as Stops,Line,min(infeed)as low, max(infeed)as high
from tableName
where (t_stamp>DATEADD(month, -6,EOMONTH( GETDATE()))) and (Code<1002) and (:code=0 or :code=code) and ( :machine=0 or
Line=Case when :machine=1 then 'bluemachine'
When :machine=2 then 'redmachine'
When :machine=3 then 'yellowmachine'
else null end )
group by FORMAT(t_stamp, 'yyyy-MMM'),Line
) subq
order by subq.Line,earliest
when just looking at the yellowmachine, I do get the min and max of infeed
Since I only have the one month of data though, I will have to put in fake data for previous months and see if it is tallying within the specific months, I think it it. So I think I can say like high-low as countedinfeed
holy moly I think it is working
inserts
results
select subq.Line,Year_Month, Stops, high-low as countInfeed
from (
select min(t_stamp) as earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, coalesce(count(Code),0) as Stops,Line,min(infeed)as low, max(infeed)as high
from tableName
where (t_stamp>DATEADD(month, -6,EOMONTH( GETDATE()))) and (Code<1002) and (:code=0 or :code=code) and ( :machine=0 or
Line=Case when :machine=1 then 'bluemachine'
When :machine=2 then 'redmachine'
When :machine=3 then 'yellowmachine'
else null end )
group by FORMAT(t_stamp, 'yyyy-MMM'),Line
) subq
order by subq.Line,earliest
err I can test, I am testing it
order by cast(RIGHT(Machine_ID, LEN(Machine_ID) - 2) as int)
or
order by len(Machine_ID), Machine_ID
input is like
mm1
mm4
mm11
mm14
mm2
mm5
mm10
The cast was 0.103s
the len() one was 0.175s
I thought it was working, but it is not
output
query order by clause
select clause in a subquery has within it:
min(mytable1.t_stamp) as earliest, mytable.t_stamp
the outside query has t_samp in the select query
in some way, the earliest part of the order by is dominating the clause
and some reason, it is placing the 5 and 12 machines after the others in the same shift from the same showing timestamp
is t_stamp hiding milliseconds from me that are making the difference here?
is there a way to round this earliest value?
I set it to order by day, shift, then machine id for now
i think that doesnt work though when I get months with a 1 in front though
I think I need to round off the milliseconds in the t_stamp somehow
I believe your cast(right...) is your problem. Instead try
cast(replace(Machine_ID,'mm','') as int)
EDIT:
A bit of advice, I would have my DB table setup where one column is machine_type which would hold the mm portion, then another column for id.
I think the issue is the earliest/t_stamp milliseconds because I get the right order with this order clause
ORDER BY day DESC, shift cast(RIGHT(Machine_ID, LEN(Machine_ID) - 2) as int) asc
at least until October when 10/1/22 comes before 2/1/22
Yeah, after another glace, your right() expression seems to work but definitely not very readable. It is very possible the milliseconds is the issue. You could try ordering by earliest with date formatting.
Casting to a SMALLDATETIME should truncate it.
EDIT: casting to SMALLDATETIME rounds it to the nearest minute. Using DATEADD with it will turn it to truncate
select getDate(), CAST(DATEADD(S, -30, getDate()) AS SMALLDATETIME)
In Datagrip, which will show mllis:
1 Like
I don’t understand exactly what you mean when you say turn it to truncate.
You mean like smalldatetime() sets to 0 seconds
and dateadd() you can set the seconds?
or is there more nuance in there?
I am getting errors trying
min(cast(table.t_stamp as smalldatetime)) as earliest
syntax error near cast
Casting to smalldatetime will round to the nearest minute. If the seconds are at 30+, it rounds up to the next minute.
Truncate should work like a floor function. 1:23:45.678 should return 1:23:00. Subtracting 30 seconds makes up for the rounding when casting to smalldatetime. The end result is a truncate.
1 Like
Thanks
I still get an error of syntax near cast
min(cast(table.t_stamp as smalldatetime)) as earliest
and
min(cast(dateadd(s,-30,table.t_stamp) as smalldatetime)) as earliest
and in this format
cast(table.t_stamp as smalldatetime) as earliest
smalldatetime doesnt seem to show up as a keyword maybe
Not sure what to tell you. I tested it in the Query browser, before saying anything.
What if you put this in a query by itself?
cast(GETDATE() as smalldatetime)
1 Like
I removed that cast and still got the error.
ah it was a different spot the causing the bug with cast syntax
Thanks again
works great
for this application I am looking at shifts 8 hours apart so if it is off by 30 seconds, will be fine I think so I did not use the dateadd() part
I put the cast(earlierst as smalldatetime) desc in the order by, and that seemed to be much faster than in the select while still ordering them correctly
1 Like