I have a field (date_time) containing various timings (short time format) and another field (sold) containing the number of items customers has purchased.
I would like to sum the number of items sold during the day in 15 mins interval. The desired outcome will be 4:00PM:4:15PM, 10 sold; 4:16PM:4:30PM, 15 sold, and so on.
I tried to achieve it by using PARTITION in query to no avail.
SELECT CStr(Hour([date_time])) & ":" & CStr(Format((Minute([date_time])\15)*15,"00") & " to " & CStr(Hour([date_time])) & ":" & Format((Minute([date_time])\15)*15,"00")+14) AS Time_Slot, Sum(Sheet1.Sold) AS SumOfSold
FROM Sheet1
GROUP BY CStr(Hour([date_time])) & ":" & CStr(Format((Minute([date_time])\15)*15,"00") & " to " & CStr(Hour([date_time])) & ":" & Format((Minute([date_time])\15)*15,"00")+14);
I would add that sheet1 is your excel sheet so presumably this will convert to an Access table.
Basically it converts the minutes into 15 minute slots and adds 14 minutes to show the start and end of the slot.
Then a simple group by the time slot and a sum of the number sold.
Please check the data carefully to ensure the results are correct
In the greyish area next to your tables in the query editor
1) Right click
2) Select SQL View in the quick menu that opens
3) Copy the text shown, <ctrl><a>;<ctrl><c>
4) Come back to this thread, and in the post box <ctrl><v>
5) Select the pasted text and click on the [CODE/] tool in the post formatting toolbar.
The SQL is much more helpful to use than the screen shots.
Also, please keep in mind, many of our experts are unable to d/l unrequested attachments as many of us help out from work and/or are using secured PCs.
SELECT Partition(Minute([Date_Time]),0,1440,15) AS Range, Sum([Transaction by DateTime and Ope].Sold) AS TotalSold
FROM [Transaction by DateTime and Ope]
GROUP BY Partition(Minute([Date_Time]),0,1440,15);
Expand|Select|Wrap|Line Numbers
SELECT Partition([Date_Time],TimeSerial(0,0,0),TimeSerial(23,59,0),TimeSerial(0,15,0)) AS Range, Sum([Transaction by DateTime and Ope].Sold) AS TotalSold
FROM [Transaction by DateTime and Ope]
GROUP BY Partition([Date_Time],TimeSerial(0,0,0),TimeSerial(23,59,0),TimeSerial(0,15,0));
Thanks, ZMBD.
I have added the SQL above. The first one is example 2 and the latter, example 1.
SELECT CStr(Hour([date_time])) & ":" & CStr(Format((Minute([date_time])\15)*15,"00") & " to " & CStr(Hour([date_time])) & ":" & Format((Minute([date_time])\15)*15,"00")+14) AS Time_Slot, Sum(Sheet1.Sold) AS SumOfSold
FROM Sheet1
GROUP BY CStr(Hour([date_time])) & ":" & CStr(Format((Minute([date_time])\15)*15,"00") & " to " & CStr(Hour([date_time])) & ":" & Format((Minute([date_time])\15)*15,"00")+14);
I would add that sheet1 is your excel sheet so presumably this will convert to an Access table.
Basically it converts the minutes into 15 minute slots and adds 14 minutes to show the start and end of the slot.
Then a simple group by the time slot and a sum of the number sold.
Please check the data carefully to ensure the results are correct
Strangely enough, I never needed the datetime and calendar module
before,
so I just looked at them today. I am surprised I don't easily find an
interval
function such this:
import datetime
...
Take a table, where not all the columns are populated:
CREATE TABLE #T (A int, B int, C int, D int)
INSERT #T (A,B) VALUES (1,2)
INSERT #T (A,B) VALUES (3,4)
INSERT #T (A,B) VALUES (5,6)...
Can anyone tell me how to write a routine that uses time-intervals, I would
like to dynamically create an array of times like this:
9:00
9:30
10:00
10:30
This has an interval of half an...
Hi
I want an access table to auto export the data in a HTML format, at
regular time intervals specified...Like every hour. The html file
should have the same name, so that it can display on...
Hi!
I am new to C.
If I want to know the CPU time needed to run a sequence of my C
code(not necessary the whole program, maybe just part of my program),
What function should I use? Some...
Hi,
I need to create a partition table but the column on which I need to
create a partition may not have any logical ranges. So while creating
or defining partition function I can not use any...
Hi I have a MSSQL table with many time intervals stored as datetime. Each time interval is also assigned a numeric type that specifies what type of job was done during the time interval.
I need to...
I can't seem to find a way to do the following:
create table part_table (
col1 int,
col2 datetime
) on psX (datename(week,col2))
I want to partition based on the week number of a date field....
The inspiration for this Tip was an initial reply by one of our resident Experts to a specific Post. In this Post, the User wanted to know how he could calculate the total number of test results...
USING PARTITION
===================
PARTITION BY RANGE-as per Oracle 8
--------------------------------------
CREATE TABLE RANGEPART
(
ID NUMBER(2) PRIMARY KEY,
NAME VARCHAR2(20)
)
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...