Query specific times across days (1 Viewer)

darkmanx

New member
Local time
Today, 11:12
Joined
Nov 12, 2020
Messages
4
Hi All!

I am trying to figure out if there is a way to query a certain set time across multiple days. Example we have three shifts (Days 7AM - 3PM, Afternoons 3PM - 11PM and Nights 11PM - 7AM) I want to pull the records for each shift and count the quantity of boxes received.

Right now I have a form where you select your date to and date from. I also have three separate queries (days, afternoon nights). The problem is night shift goes from 11PM - 7AM the next day and when I run the query it pulls 0 records. I am just using a basic criteria for the shifts. It works for days and afternoons but not nights. example: day shift would be: >"07:00" And <"15:00" afternoons would be >"15:00" And <"23:00".

Any idea if this is possible to query across multiple days but only a certain time frame?


Thanks!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:12
Joined
May 21, 2018
Messages
4,167
any chance you can post a sample db or at least some sample data? This is hard to answer because we have no idea how you are logging this data.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:12
Joined
Oct 29, 2018
Messages
13,761
One idea is to adjust the shifts on the fly similar to querying by fiscal year.

Sent from phone...
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:12
Joined
Sep 21, 2011
Messages
7,503
Use the start date and time and add the shift period as the end date/time ?
 

darkmanx

New member
Local time
Today, 11:12
Joined
Nov 12, 2020
Messages
4
any chance you can post a sample db or at least some sample data? This is hard to answer because we have no idea how you are logging this data.

Here is some sample data. I should have mentioned that this is pulling from ODBC connected to our server.

This is what I have for our dayshift numbers (7AM - 3PM) and it's working as intended.

SELECT PR1_AUDITS_CURR.WSKUSKUSKUID, PR1_AUDITS_CURR.FROM_LOAD_ID, CDate(Mid([PR1_AUDITS_CURR].[DATE_WMS],5,2) & "/" & Mid([PR1_AUDITS_CURR].[DATE_WMS],7,2) & "/" & Mid([PR1_AUDITS_CURR].[DATE_WMS],1,4)) AS [Date], Mid([PR1_AUDITS_CURR].[DATE_WMS],9,2) & ":" & Mid([PR1_AUDITS_CURR].[DATE_WMS],11,2) AS Expr1, PR1_AUDITS_CURR.TXTYPE, PR1_AUDITS_CURR.TXSUBTYPE, PR1_AUDITS_CURR.TO_LOC, PR1_AUDITS_CURR.FROM_QTY, PR1_AUDITS_CURR.TO_QTY

FROM PR1_AUDITS_CURR

GROUP BY PR1_AUDITS_CURR.WSKUSKUSKUID, PR1_AUDITS_CURR.FROM_LOAD_ID, CDate(Mid([PR1_AUDITS_CURR].[DATE_WMS],5,2) & "/" & Mid([PR1_AUDITS_CURR].[DATE_WMS],7,2) & "/" & Mid([PR1_AUDITS_CURR].[DATE_WMS],1,4)), Mid([PR1_AUDITS_CURR].[DATE_WMS],9,2) & ":" & Mid([PR1_AUDITS_CURR].[DATE_WMS],11,2), PR1_AUDITS_CURR.TXTYPE, PR1_AUDITS_CURR.TXSUBTYPE, PR1_AUDITS_CURR.TO_LOC, PR1_AUDITS_CURR.FROM_QTY, PR1_AUDITS_CURR.TO_QTY

HAVING (((CDate(Mid(PR1_AUDITS_CURR.DATE_WMS,5,2) & "/" & Mid(PR1_AUDITS_CURR.DATE_WMS,7,2) & "/" & Mid(PR1_AUDITS_CURR.DATE_WMS,1,4))) Between Forms!frmMainMenu!txtfromdate And Forms!frmMainMenu!txttodate) And ((Mid(PR1_AUDITS_CURR.DATE_WMS,9,2) & ":" & Mid(PR1_AUDITS_CURR.DATE_WMS,11,2))>"07:00" And (Mid(PR1_AUDITS_CURR.DATE_WMS,9,2) & ":" & Mid(PR1_AUDITS_CURR.DATE_WMS,11,2))<"15:00") And ((PR1_AUDITS_CURR.TXTYPE)="a") And ((PR1_AUDITS_CURR.TXSUBTYPE)="CL"))

ORDER BY Mid([PR1_AUDITS_CURR].[DATE_WMS],9,2) & ":" & Mid([PR1_AUDITS_CURR].[DATE_WMS],11,2);

Output:

Receiving Days Receiving Days

WSKUSKUSKUIDFROM_LOAD_IDDateExpr1TXTYPETXSUBTYPETO_LOCFROM_QTYTO_QTY
206329122844727997
11/11/2020​
07:18aCLLN452
0​
99​
206887372842115805
11/11/2020​
07:22aCLLN452
0​
56​
212471762844727996
11/11/2020​
07:22aCLLN452
0​
20​
210244152844700324
11/11/2020​
07:23aCLLN509
0​
12​
210244152844700325
11/11/2020​
07:23aCLLN509
0​
60​
211698992844727995
11/11/2020​
07:25aCLLN452
0​
82​
211698992844727994
11/11/2020​
07:26aCLLN452
0​
82​
210244252844700323
11/11/2020​
07:27aCLLN509
0​
40​
201439132844727993
11/11/2020​
07:28aCLLN452
0​
228​
210244252844700320
11/11/2020​
07:29aCLLN509
0​
40​
210244252844700321
11/11/2020​
07:29aCLLN509
0​
40​
210244252844700322
11/11/2020​
07:29aCLLN509
0​
40​
210244252844700317
11/11/2020​
07:30aCLLN509
0​
10​
210244252844700318
11/11/2020​
07:30aCLLN509
0​
10​
210244252844700316
11/11/2020​
07:30aCLLN509
0​
10​
210244252844700315
11/11/2020​
07:30aCLLN509
0​
10​
210244252844700319
11/11/2020​
07:30aCLLN509
0​
40​
208620172842115823
11/11/2020​
07:31aCLLN452
0​
50​
210244252844700312
11/11/2020​
07:31aCLLN509
0​
10​
210244252844700313
11/11/2020​
07:31aCLLN509
0​
40​
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:12
Joined
May 21, 2018
Messages
4,167
If it was me I would find an easy way to tag a record as day or night shift in a query. I build this table.
tblShift tblShift

ShiftIDShiftStartShiftEndShiftName
1​
7:00​
14:59​
Morning
2​
15:00​
22:59​
Evening
3​
23:00​
23:59​
Night
5​
0:00​
6:59​
Night
Now I can use this in a dlookup or a query to identify a shift.
Example
SQL:
SELECT TblDateTime.TheDate, TblDateTime.TheTime, tblShift.ShiftID, tblShift.ShiftName
FROM TblDateTime, tblShift
WHERE (((TblDateTime.TheTime)>=[shiftStart] And (TblDateTime.TheTime)<=[shiftEnd]));

Then now it would be much easier
Query2 Query2

TheDateTheTimeShiftIDShiftName
11/9/2020​
12:30:00 AM​
5​
Night
11/9/2020​
1:00:00 AM​
5​
Night
11/9/2020​
1:30:00 AM​
5​
Night
11/9/2020​
2:00:00 AM​
5​
Night
11/9/2020​
2:30:00 AM​
5​
Night
11/9/2020​
3:00:00 AM​
5​
Night
11/9/2020​
3:30:00 AM​
5​
Night
11/9/2020​
4:00:00 AM​
5​
Night
11/9/2020​
4:30:00 AM​
5​
Night
11/9/2020​
5:00:00 AM​
5​
Night
11/9/2020​
5:30:00 AM​
5​
Night
11/9/2020​
6:00:00 AM​
5​
Night
11/9/2020​
6:30:00 AM​
5​
Night
11/9/2020​
7:30:00 AM​
1​
Morning
11/9/2020​
8:00:00 AM​
1​
Morning
11/9/2020​
8:30:00 AM​
1​
Morning
11/9/2020​
9:00:00 AM​
1​
Morning
11/9/2020​
9:30:00 AM​
1​
Morning
11/9/2020​
10:00:00 AM​
1​
Morning
11/9/2020​
10:30:00 AM​
1​
Morning
11/9/2020​
11:00:00 AM​
1​
Morning
11/9/2020​
11:30:00 AM​
1​
Morning
11/9/2020​
12:00:00 PM​
1​
Morning
11/9/2020​
12:30:00 PM​
1​
Morning
11/9/2020​
1:00:00 PM​
1​
Morning
11/9/2020​
1:30:00 PM​
1​
Morning
11/9/2020​
2:00:00 PM​
1​
Morning
11/9/2020​
2:30:00 PM​
1​
Morning
11/9/2020​
3:00:00 PM​
2​
Evening
11/9/2020​
3:30:00 PM​
2​
Evening
11/9/2020​
4:00:00 PM​
2​
Evening
11/9/2020​
4:30:00 PM​
2​
Evening
11/9/2020​
5:00:00 PM​
2​
Evening
11/9/2020​
5:30:00 PM​
2​
Evening
11/9/2020​
6:00:00 PM​
2​
Evening
11/9/2020​
6:30:00 PM​
2​
Evening
11/9/2020​
7:00:00 PM​
2​
Evening
11/9/2020​
7:30:00 PM​
2​
Evening
11/9/2020​
8:00:00 PM​
2​
Evening
11/9/2020​
8:30:00 PM​
2​
Evening
11/9/2020​
9:00:00 PM​
2​
Evening
11/9/2020​
9:30:00 PM​
2​
Evening
11/9/2020​
10:00:00 PM​
2​
Evening
11/9/2020​
10:30:00 PM​
2​
Evening
11/9/2020​
11:30:00 PM​
3​
Night
11/10/2020​
12:30:00 AM​
5​
Night
11/10/2020​
1:00:00 AM​
5​
Night
11/10/2020​
1:30:00 AM​
5​
Night
11/10/2020​
2:00:00 AM​
5​
Night
11/10/2020​
2:30:00 AM​
5​
Night
11/10/2020​
3:00:00 AM​
5​
Night
11/10/2020​
3:30:00 AM​
5​
Night
11/10/2020​
4:00:00 AM​
5​
Night
11/10/2020​
4:30:00 AM​
5​
Night
11/10/2020​
5:00:00 AM​
5​
Night
11/10/2020​
5:30:00 AM​
5​
Night
11/10/2020​
6:00:00 AM​
5​
Night
 

darkmanx

New member
Local time
Today, 11:12
Joined
Nov 12, 2020
Messages
4
If it was me I would find an easy way to tag a record as day or night shift in a query. I build this table.
tblShift tblShift

ShiftIDShiftStartShiftEndShiftName
1​
7:00​
14:59​
Morning
2​
15:00​
22:59​
Evening
3​
23:00​
23:59​
Night
5​
0:00​
6:59​
Night
Now I can use this in a dlookup or a query to identify a shift.
Example
SQL:
SELECT TblDateTime.TheDate, TblDateTime.TheTime, tblShift.ShiftID, tblShift.ShiftName
FROM TblDateTime, tblShift
WHERE (((TblDateTime.TheTime)>=[shiftStart] And (TblDateTime.TheTime)<=[shiftEnd]));

Then now it would be much easier
Query2 Query2

TheDateTheTimeShiftIDShiftName
11/9/2020​
12:30:00 AM​
5​
Night
11/9/2020​
1:00:00 AM​
5​
Night
11/9/2020​
1:30:00 AM​
5​
Night
11/9/2020​
2:00:00 AM​
5​
Night
11/9/2020​
2:30:00 AM​
5​
Night
11/9/2020​
3:00:00 AM​
5​
Night
11/9/2020​
3:30:00 AM​
5​
Night
11/9/2020​
4:00:00 AM​
5​
Night
11/9/2020​
4:30:00 AM​
5​
Night
11/9/2020​
5:00:00 AM​
5​
Night
11/9/2020​
5:30:00 AM​
5​
Night
11/9/2020​
6:00:00 AM​
5​
Night
11/9/2020​
6:30:00 AM​
5​
Night
11/9/2020​
7:30:00 AM​
1​
Morning
11/9/2020​
8:00:00 AM​
1​
Morning
11/9/2020​
8:30:00 AM​
1​
Morning
11/9/2020​
9:00:00 AM​
1​
Morning
11/9/2020​
9:30:00 AM​
1​
Morning
11/9/2020​
10:00:00 AM​
1​
Morning
11/9/2020​
10:30:00 AM​
1​
Morning
11/9/2020​
11:00:00 AM​
1​
Morning
11/9/2020​
11:30:00 AM​
1​
Morning
11/9/2020​
12:00:00 PM​
1​
Morning
11/9/2020​
12:30:00 PM​
1​
Morning
11/9/2020​
1:00:00 PM​
1​
Morning
11/9/2020​
1:30:00 PM​
1​
Morning
11/9/2020​
2:00:00 PM​
1​
Morning
11/9/2020​
2:30:00 PM​
1​
Morning
11/9/2020​
3:00:00 PM​
2​
Evening
11/9/2020​
3:30:00 PM​
2​
Evening
11/9/2020​
4:00:00 PM​
2​
Evening
11/9/2020​
4:30:00 PM​
2​
Evening
11/9/2020​
5:00:00 PM​
2​
Evening
11/9/2020​
5:30:00 PM​
2​
Evening
11/9/2020​
6:00:00 PM​
2​
Evening
11/9/2020​
6:30:00 PM​
2​
Evening
11/9/2020​
7:00:00 PM​
2​
Evening
11/9/2020​
7:30:00 PM​
2​
Evening
11/9/2020​
8:00:00 PM​
2​
Evening
11/9/2020​
8:30:00 PM​
2​
Evening
11/9/2020​
9:00:00 PM​
2​
Evening
11/9/2020​
9:30:00 PM​
2​
Evening
11/9/2020​
10:00:00 PM​
2​
Evening
11/9/2020​
10:30:00 PM​
2​
Evening
11/9/2020​
11:30:00 PM​
3​
Night
11/10/2020​
12:30:00 AM​
5​
Night
11/10/2020​
1:00:00 AM​
5​
Night
11/10/2020​
1:30:00 AM​
5​
Night
11/10/2020​
2:00:00 AM​
5​
Night
11/10/2020​
2:30:00 AM​
5​
Night
11/10/2020​
3:00:00 AM​
5​
Night
11/10/2020​
3:30:00 AM​
5​
Night
11/10/2020​
4:00:00 AM​
5​
Night
11/10/2020​
4:30:00 AM​
5​
Night
11/10/2020​
5:00:00 AM​
5​
Night
11/10/2020​
5:30:00 AM​
5​
Night
11/10/2020​
6:00:00 AM​
5​
Night
Thanks I will give this a try.
 

darkmanx

New member
Local time
Today, 11:12
Joined
Nov 12, 2020
Messages
4
I tried but no luck. I have no idea how to join the ODBC table with the custom table since the date and time field are one field on our server which is why I have to separate the two fields using MID function (Example: 2020110911494471)

I think my ask is to ambitious and most likely I won't understand how to do it.

Thanks for trying to help anyways.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:12
Joined
Sep 21, 2011
Messages
7,503
There is a Timevalue() function?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:12
Joined
May 21, 2018
Messages
4,167
Assuming a single dateTime field (which is good)
Code:
SELECT tbldatetime2.dateandtime,
       Int([dateandtime])       AS TheDate,
       Timevalue([dateandtime]) AS TheTime,
       tblshift.shiftid,
       tblshift.shiftname
FROM   tblshift,
       tbldatetime2
WHERE  (( ( Timevalue([dateandtime]) ) >= [shiftstart]
          AND ( Timevalue([dateandtime]) ) <= [shiftend] ));


Query2 Query2

DateAndTimeTheDateTheTimeShiftIDShiftName
11/9/2020 12:30:00 AM​
11/9/2020​
12:30:00 AM​
5​
Night
11/9/2020 1:00:00 AM​
11/9/2020​
1:00:00 AM​
5​
Night
11/9/2020 1:30:00 AM​
11/9/2020​
1:30:00 AM​
5​
Night
11/9/2020 2:00:00 AM​
11/9/2020​
2:00:00 AM​
5​
Night
11/9/2020 2:30:00 AM​
11/9/2020​
2:30:00 AM​
5​
Night
11/9/2020 3:00:00 AM​
11/9/2020​
3:00:00 AM​
5​
Night
11/9/2020 3:30:00 AM​
11/9/2020​
3:30:00 AM​
5​
Night
11/9/2020 4:00:00 AM​
11/9/2020​
4:00:00 AM​
5​
Night
11/9/2020 4:30:00 AM​
11/9/2020​
4:30:00 AM​
5​
Night
11/9/2020 5:00:00 AM​
11/9/2020​
5:00:00 AM​
5​
Night
11/9/2020 5:30:00 AM​
11/9/2020​
5:30:00 AM​
5​
Night
11/9/2020 6:00:00 AM​
11/9/2020​
6:00:00 AM​
5​
Night
11/9/2020 6:30:00 AM​
11/9/2020​
6:30:00 AM​
5​
Night
11/9/2020 7:00:00 AM​
11/9/2020​
7:00:00 AM​
1​
Morning
11/9/2020 7:30:00 AM​
11/9/2020​
7:30:00 AM​
1​
Morning
11/9/2020 8:00:00 AM​
11/9/2020​
8:00:00 AM​
1​
Morning
11/9/2020 8:30:00 AM​
11/9/2020​
8:30:00 AM​
1​
Morning
11/9/2020 9:00:00 AM​
11/9/2020​
9:00:00 AM​
1​
Morning
11/9/2020 9:30:00 AM​
11/9/2020​
9:30:00 AM​
1​
Morning
11/9/2020 10:00:00 AM​
11/9/2020​
10:00:00 AM​
1​
Morning
11/9/2020 10:30:00 AM​
11/9/2020​
10:30:00 AM​
1​
Morning
11/9/2020 11:00:00 AM​
11/9/2020​
11:00:00 AM​
1​
Morning
11/9/2020 11:30:00 AM​
11/9/2020​
11:30:00 AM​
1​
Morning
11/9/2020 12:00:00 PM​
11/9/2020​
12:00:00 PM​
1​
Morning
11/9/2020 12:30:00 PM​
11/9/2020​
12:30:00 PM​
1​
Morning
11/9/2020 1:00:00 PM​
11/9/2020​
1:00:00 PM​
1​
Morning
11/9/2020 1:30:00 PM​
11/9/2020​
1:30:00 PM​
1​
Morning
11/9/2020 2:00:00 PM​
11/9/2020​
2:00:00 PM​
1​
Morning
11/9/2020 2:30:00 PM​
11/9/2020​
2:30:00 PM​
1​
Morning
11/9/2020 3:00:00 PM​
11/9/2020​
3:00:00 PM​
2​
Evening
11/9/2020 3:30:00 PM​
11/9/2020​
3:30:00 PM​
2​
Evening
11/9/2020 4:00:00 PM​
11/9/2020​
4:00:00 PM​
2​
Evening
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:12
Joined
Sep 21, 2011
Messages
7,503
@MajP May I ask why you did not use DateValue for the date portion.?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:12
Joined
May 21, 2018
Messages
4,167
Now your next query with the date range for night gets a little confusing if you want to include the complete shift.

Because on your form you will have start and end date. So if the enddate is 11/1/2020 you need to include the 11/2/2020 where the shift ID is 5 (the period from midnight to 6:59)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:12
Joined
May 21, 2018
Messages
4,167
DateValue for the date portion
I could, it was just quicker to type.
 

Users who are viewing this thread

Top Bottom