Hi,
I want to get the count of rows per each 20 minutes, is that possible?
Is there a date function or any other function that I can use in Group
by clause, which will group the data of every 20 minutes and give me
the count?
Thank you.
Vidya 3 2457
Vidya wrote: Hi, I want to get the count of rows per each 20 minutes, is that possible?
One solution to the is to create a function in the SQL-server that you
can join with your table , i.e.
First, do
CREATE FUNCTION MinuteGenerator
(@startDate DATETIME,
@endDate DATETIME,
@interval int)
RETURNS @minuteRange TABLE (dtMinute DATETIME, dtMinuteEnd DATETIME)
AS
BEGIN
DECLARE @workMinute DATETIME
SET @workMinute = @startDate
WHILE @workMinute <= @endDate
BEGIN
INSERT INTO @minuteRange
VALUES (@workMinute,
DATEADD(ms,-3,DATEADD(minut e,@interval,@wo rkMinute)))
SET @workMinute = DATEADD(minute, @interval, @workMinute)
END
RETURN
END
Now you can use this in your queries like:
SELECT ranges.dtminute , count(myT.x)
FROM
myTable as myT
right outer join
MinuteGenerator ('2005-11-28 21:40','2005-11-28 22:40',20) as ranges
on (ts.time between ranges.dtMinute and ranges.dtMinute End)
GROUP BY ranges.dtMinute
Cheers,
/Morten
"Vidya" <vm*****@yahoo. com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. . Hi, I want to get the count of rows per each 20 minutes, is that possible?
Is there a date function or any other function that I can use in Group by clause, which will group the data of every 20 minutes and give me the count?
Thank you. Vidya
Assuming the time you have in each record is a datetime, then try the
following:
select Cast(cast(Group 20Min as float) / 3 / 24 as DateTime),
min(LastActive) ,
max(lastActive) ,
count(*)
from
(select cast(Cast(LastA ctive as float) * 24 * 3 as bigint) as Group20Min, *
from Accounts) as t
group by Group20Min
this example assumes that you have a table called Accounts with a datetime
field called LastActive. This seems to work on one of my databases.
Basically works by converting datetime to an integer and then grouping on
that. DateTime is basically a hole number of days from a fixed point in
time. So to convert to hours multiply by 24, to go from hours to your 20
minute blocks multiply by 3 (because there are 3 x 20 minute blocks per
hour). Then convert it to an integer to drop any fractional part. The cast
in the select is trying to convert back the other way - I did find some
slight rounding errors creeping in here. I've included min and max only as a
sanity check.
There are other solutions, but that's how I would do it. Hope it helps.
--
Brian Cryer www.cryer.co.uk/brian
On 28 Nov 2005 08:45:35 -0800, Vidya wrote: Hi, I want to get the count of rows per each 20 minutes, is that possible?
Is there a date function or any other function that I can use in Group by clause, which will group the data of every 20 minutes and give me the count?
Hi Vidya,
SELECT a, b,
DATEADD(minute,
20 * (DATEDIFF(minut e, '20050101', theDate) / 20),
'20050101') AS StartOf20Mins,
COUNT(*), SUM(Column)
FROM YourTable
GROUP BY a, b, DATEDIFF(minute , '20050101', theDate) / 20
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Simon |
last post by:
Hi,
I am having a little problem with my PHP - MySQl code, I have two tables
(shown below) and I am trying populate a template page with data from both.
<disclaimer>Now I would like to say my skills, especially with MySQL are
rudimentary</disclaimer> However my code (link below) fails, the nested
database call does not return any data and this has me stumped. Any help
will be much appreciated.
Many thanks in advance
|
by: Harald Massa |
last post by:
Old, very old informatical problem: I want to "print" grouped data with
head information, that is:
eingabe=
shall give: ( Braces are not important...)
'Stuttgart', '70197'
--data-- ('Fernsehturm', '20')
|
by: CSDunn |
last post by:
Hello,
I have a format issue on an Access 2000 ADP report that I am going to
attempt to explain from a 'ten thousand foot view' :
I have an Access 2000 ADP report that has a SQL Server 2000 Stored Procedure
as its record source. The Proc is called MM_rptTeacherGroupingTest_sp. In
order to help communicate the issue I am having with the report, please take
a look at the following:
http://www.valverde.edu/home/policy/ReportLayout.htm .
|
by: x |
last post by:
hi
i am a pilot by profession. i want to create a database of my logbook
using ms access 2002. i am facing a problem regarding the format of
time field. when i select "Data/Time" data type for my time field then
this format gives the liberty to record times uptill a figure of 59 in
different sub-formats, whereas i want the format to be able to record
the times like 80:35 or 1:10 or 1138:00. which means that i have these
many hours on a...
|
by: Jim Michaels |
last post by:
SELECT id,name, prj,SUM(TIMEDIFF(end_time,start_time)) AS elapsed
FROM work
group by name,prj;
The problem is, I need elapsed in a datetime format, and SUM truncates to
integer hours.
Is there no way to do this?
I just want the group sums as hours, minutes, dats, months, years.
| |
by: yovation |
last post by:
Hi,
I have a 3 table database.
1 parent
1 child
1 child of child
I would like to get the data into excel as 1 sheet (similar to a
grouped report).
|
by: nico3334 |
last post by:
I have a database that has a Date column (1/1/2007) and a Data column that has numerical data. I am currently running a query to sum the Data column for a certain month and grouped by day (There is more than items of Data for a specific Day). Here is my current query:
SELECT SUM(Data) as Total
FROM database
WHERE Month(Date) = 10
And Year(Date) = 2007
Group by Day(Date)
This displays the summed data which is grouped by the Days of...
|
by: Erik Nodland |
last post by:
Hi Everyone,
Just after some ideas and suggestions on how to do this. I have a large
amount of numeric data which I would like to group given a distance as a
parameter.
IE. If my dataset was simply 1, 5, 9,10,11,16,17,25,29 and I pass in a
distance of 2 then I would want to end up with groups like
1, 5, (9,10,11), (16,17) ,25,29
So is grouped because each number is within a distance of 2 from
|
by: regrat |
last post by:
Good Morning,
I'm making a report with grouped data with access 2003.
Unfortunately, Access print in this report groups with empty data to
fulfill all level.
It's possible not to print groups level which haven't data?
Thanks
|
by: marktang |
last post by:
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |