Calculating Total Time in Seconds

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.

1 Like

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.

You aren’t doing a sum() or count() or anything else that needs grouping, so ditch the group by clause.

ah, so when I do the sum, then I can group?

select myCode, Sum(Seconds)

from(
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

)subqtwo

group by myCode
order by sum(seconds) desc

ITS ALIVE

I just need to go to a meeting and come back and order by date

omg thanks so much

And probably add the truncating for dates like you had

2 Likes