You want to know how much time in each code, does it ever go to 0 or a non-code value? From your example it’s always in a code.
it is always reading a code
I use 1001 for idle and 1002 for running
Everything else is stop for a code
Well, that would have been useful info in the beginning.
I am not sure what you mean.
I still need duration of those codes too.
I am having a hard time finding lag or lead in the w3schools sql functions
Ok, then disregard my comment.
I have something in the works, but Im taking care of a production issue. Gimme a bit.
oh yah, I see
in another way it would be a totally different animal, yah
no worries
Put the following into a named query, with value parameters begin_ts and end_ts of type DateTime:
SELECT stopCode,
count(stopCode) as Qty,
sum(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END -
CASE WHEN t_stamp < :begin_ts THEN :begin_ts ELSE t_stamp END) AS Duration
FROM (
SELECT t_stamp,
stopCode,
coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
CASE WHEN current_timestamp < :end_ts THEN current_timestamp ELSE :end_ts END) AS t_stamp1
WHERE t_stamp >= coalesce((
SELECT TOP 1 t_stamp
FROM myTable
WHERE t_stamp < :begin_ts
ORDER BY t_stamp DESC), '1900-01-01')
AND t_stamp <= coalesce((
SELECT TOP 1 t_stamp
FROM myTable
WHERE t_stamp > :end_ts
ORDER BY t_stamp), '2100-01-01')
) subq
WHERE t_stamp < :end_ts AND t_stamp1 > :begin_ts
GROUP BY stopCode
ORDER BY count(stopCode) desc
Provide the precise timestamps for which you wish results (shift, day, week, month) and it will provide the quantity and duration for each code. Any code active at either boundary will be counted in Qty but its Duration will be clipped to the boundary.
If endts is beyond current_timestamp, the last entry will be clipped to current_timestamp.
3 Likes
The LAG Function and the LEAD Function in SQL | LearnSQL.com
I thought I was going to use lag, to get the previous time stamp.
Then I would use datediff to get the difference
then sum to get the total of those
then group by the code to make the sum perform per code
Then I saw your post.
I need a little help to massage that and understand it.
I am getting errors near “:”
Do I need to have code above this like declares?
declare : end_ts = GETDATE()
declare : begin_ts = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 0, 0))
edit, need to make parmaeters
.oh no, I need to read your whole post
The code is written for use in a named query, hence the colons for value parameters. You can’t run it in SSMS.
begin_ts and end_ts should be provided by your UI with date pickers or similar.
BTW, I chose LEAD, not LAG, because the row for each code marks the beginning of the period of time for that code. So the next row has the ending timestamp for that code, which you get with lead().
I used a subquery to capture one row on either side of the given boundaries to which to apply the lead() operation. The outer query then clips these off.
I am getting an error “invalid column name ‘t_stamp’”
that is the name of the column though
I don’t have access to my MS SQL Server dev instance–working from home while my truck is in the shop–so I didn’t test. You might want to run in SSMS with suitable constants in place of :begin_ts and :end_ts to figure out my mistake.
I don’t know for sure
added a from myTable before the wheres
Now I have a different error, datetime2 is invalid for subtract operator
Got a bit of time…
sampleHeaders = ['linestops_ndx', 'stopCode', 't_stamp']
sampleData = [
[16, 534, system.date.parse('2021-11-19 16:58:22')],
[17, 1002, system.date.parse('2021-11-19 17:00:56')],
[18, 1001, system.date.parse('2021-11-19 17:05:11')],
[19, 1002, system.date.parse('2021-11-19 17:10:15')],
[20, 1000, system.date.parse('2021-11-19 17:14:22')],
[21, 1002, system.date.parse('2021-11-19 17:20:28')],
[22, 534, system.date.parse('2021-11-19 17:21:12')],
[23, 1001, system.date.parse('2021-11-19 17:23:13')],
[24, 1001, system.date.parse('2021-11-19 17:34:00')],
[25, 1002, system.date.parse('2021-11-19 17:41:52')],
[26, 634, system.date.parse('2021-11-19 17:52:55')]
]
sampleDataSet = system.dataset.toDataSet(sampleHeaders, sampleData)
#-----------------------------------------------------------------------
from operator import itemgetter
from collections import OrderedDict
# A query result (using system.db.runPrepQuery(), et al.) comes in as a PyDataSet
pyData = system.dataset.toPyDataSet(sampleDataSet)
# Get comumn names for the incoming data
stopCodeDict = {}
# Column names for output data
headers = ['stopCode', 'qty', 'duration']
for row, nextRow in zip(pyData, pyData[1:]):
# Get stop code and duration
stopCode = row['stopCode']
currentDuration = system.date.secondsBetween(row['t_stamp'], nextRow['t_stamp'])
# Check if stop code exists in the dictionary
if stopCode not in stopCodeDict:
stopCodeDict[stopCode] = OrderedDict({'stopCode': stopCode})
stopCodeDict[stopCode]['qty'] = 1
stopCodeDict[stopCode]['duration'] = currentDuration
else:
stopCodeDict[stopCode]['qty'] = stopCodeDict[stopCode]['qty'] + 1
stopCodeDict[stopCode]['duration'] += currentDuration
# Create a sorted list of lists sorted by duration
dataOut = sorted([item.values() for item in stopCodeDict.values()],
key = itemgetter(headers.index('duration')),
reverse = True
)
datasetOut = system.dataset.toDataSet(headers, dataOut)
Output dataset:
row | stopCode | qty | duration
-------------------------------
0 | 1001 | 3 | 1423
1 | 1002 | 4 | 1209
2 | 1000 | 1 | 366
3 | 534 | 2 | 275
2 Likes
I don’t know how to use that (where it goes, how it is read/used)
I can understand roughly how it works.
What is zip()
I got the lead value in a row. (was getting errors I didn’t know how to fix on the other query)
SELECT myTable_ndx , myCode,
t_stamp,
lead(t_stamp) over (order by t_stamp) AS lead
from myTable
order by t_stamp desc
When I try to make this a subquery though, I get some errors.
I was attempting variations of this:
select myCode, sum(datediff(second,t_stamp,lead))
from(
SELECT myTable_ndx , myCode,
t_stamp,
lead(t_stamp) over (order by t_stamp) AS lead
from myTable
order by t_stamp desc
)subq
where t_stamp>dateadd(month,-6,getdate())
order by myCode
“error near from”
Tried
select myCode, datediff(second,t_stamp,lead)
from(
SELECT myTable_ndx , myCode,
t_stamp,
lead(t_stamp) over (order by t_stamp) AS lead
from myTable
order by t_stamp desc
)subq
where t_stamp>dateadd(month,-6,getdate())
order by myCode
edit now getting “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.”
You would put it in a script module, then you could call it from a gateway timer or in a report..etc.
zip() is a python function that pairs list elements together in tuples and contains them in a new list.
IE
nums = [1,2,3]
letters = ['A','B','C']
print zip(nums,letters)
Result
>>>
[(1, 'A'), (2, 'B'), (3, 'C')]
>>>
I am constantly being told
“the column is invalid because it is not contained in the aggregate function or the group by clause”
Or I am told instead :
“The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.”
I went here to try to learn the rules
SQL Subqueries - w3resource
I also looked at this
SQL SUM() | Syntax and examples of SQL SUM() with code and output (educba.com)
In this example, the person acquires the sum of sales by team id.
This made me think, I could in a similar manner get my durations since I had the lead times in the subquery table.
See highlight. Get rid of the "Order by t_stamp" in the subquery. (But leave it in the OVER clause.)
select myCode, datediff(second,t_stamp,lead) as seconds
from(
SELECT myTable_ndx , myCode,
t_stamp,
lead(t_stamp) over (order by t_stamp) AS lead
from myTable
)subq
group by myCode
order by seconds
Column ‘subq.t_stamp’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.