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

[zacharyw.larson]

all the queries I find are close, but not right

So I think there is a way using datepart, between, and my t_stamp to group by a range of 7am to 7am

I thought like

select 
line,
hours,
 t_stamp between ( dateadd(hour,7, convert(datetime,convert(date,t_stamp)) ),		dateadd(hour,7,dateadd(day,-1,convert(datetime,convert(date,t_stamp))))) as this

from mytable
group by line, hours, this

is something like this possible?

I have grouped by year month before like

select format(t_stamp, 'yyyy-MMM') as year_month

group by format(t_stamp, 'yyyy-MMM') as year_month

I am not sure how to get this result though.

[lrose]

You can not use the BETWEEN operator outside of a WHERE clause.

If you want to GROUP by a RANGE, then you would need to generate a list of the appropriate ranges, and then LEFT JOIN to that list. Not what I would call trivial.

You could group by a date part.

SELECT line,hours,datepart(HOUR,t_stamp) AS 'Hour'
FROM mytable
GROUP BY line, hours, datepart(HOUR,t_stamp)

Or you can select between a specific datetime

SELECT line,hours
FROM mytabl
WHERE t_stamp BETWEEN @starttime and @endtime
GROUP BY line,hours

I’m not sure that either of those is really what you’re looking for, but I’m also not sure what your looking for in general.

What I think you want is data group by line,hours, 7am - 7am over a range of dates. I would probably resort to pulling all data over the range I wanted and then doing the grouping in a script. Maybe not the best solution but it is what I know.

[zacharyw.larson]

select hours,
datemagic
from mytable
group by datemagic

if input is like

3,Wed Mar 23 20:40:40 EDT 2022
3,Wed Mar 23 20:40:39 EDT 2022
4,Wed Mar 23 03:36:10 EDT 2022
3,Wed Mar 22 15:46:44 EST 2022
3,Tue Mar 22 04:16:52 EST 2022
4,Sat Mar 22 03:13:08 EDT 2022
3,Sat Mar 22 03:13:05 EDT 2022
4,Sat Mar 21 04:10:36 EDT 2022

output should be like

 6,  Mar 23
 7,  Mar 22
10,  Mar 21
 4,  Mar 20 

whatever that datemagic is, I need to learn it

[lrose]

This would generate the output you have given, from the input you gave. But it is agnostic to the time. As soon as the day rolls over hours recorded from then to the next time it rolls over will be summed.

SELECT sum(hours),format(t_stamp,'MMM DD')
FROM mytable
GROUP BY format(t_stamp,'MMM DD')

If hours is not included in the GROUP BY, then it must be in an aggregate in the SELECT.

[zacharyw.larson]

@lrose

SELECT sum(hours),format(t_stamp,'MMM dd')
FROM mytable
GROUP BY format(t_stamp,'MMM dd')

I think it adds up values from last year.
I think it adds up values from midnight to midnight.

[lrose]

It does.

As I said.

@lrose

But it is agnostic to the time. As soon as the day rolls over hours recorded from then to the next time it rolls over will be summed.

WHERE clauses are used to NARROW a search. GROUP BY is used to AGGREGATE based on some criteria which defines a GROUP within the search.

For instance you could do:

SELECT sum(hours), datepart(DAY,t_stamp)
FROM mytable
WHERE datepart(YEAR,t_stamp) = 2022
GROUP BY datepart(DAY,t_stamp)

This would sum the hours for each day in the year 2022

You have to define what data you want to GROUP over, otherwise it will GROUP over all data.

SQL Server DATEPART() Function

[nminchin]

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

[zacharyw.larson]

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.

[zacharyw.larson]

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

[lrose]

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')

[JordanCClark]

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

[zacharyw.larson]

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.

[pturmel]

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:

Script for periodically changing shift time

Hi Berkay, I like to solve this kind of problem with a smart database, like PostgreSQL, where I can define a pattern and repeat it with table generating functions. I tweaked one of my implementations to serve as an example: create table shift_schedules (shift_id serial, shift_name varchar(255), refdate date, repeat int, tzname varchar(255), primary key (shift_id)); insert into shift_schedules (shift_name, refdate, repeat, tzname) values ('Berkay Cinar 3wk', '2021-07-26', 21, 'America/Ne…

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

[zacharyw.larson]

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

[pturmel]

@zacharyw.larson

What do the $ do in the code?

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

@zacharyw.larson

There are some double colons in some of the selects, what do they do?

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

[pturmel]

@zacharyw.larson

What is the “sid”?

Short for shift_id within the generator function.

[pturmel]

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.

[zacharyw.larson]

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.

[zacharyw.larson]

oh I can join on a subquery

SQL LEFT JOIN Subquery Alias - Stack Overflow

[zacharyw.larson]

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