Re: Data grouped by 20 minutes?

"Vidya" <vmswar2@xxxxxxxxx> wrote in message
news:1133196335.529841.201590@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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(Group20Min as float) / 3 / 24 as DateTime),
min(LastActive),
max(lastActive),
count(*)
from
(select cast(Cast(LastActive 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


.

Relevant Pages

  • Re: Sum and GroupBy problem,
    ... CREATE TABLE Accounts (acc_no INTEGER, acc_date DATETIME, acc_amount ... NUMERICNOT NULL, PRIMARY KEY ) ... DECLARE @dt_from DATETIME, @dt_to DATETIME ...
    (microsoft.public.sqlserver.programming)
  • SQL Query help
    ... I have a SQL table with the following fields: ... accounts, orderid's and datetime ... Using Query Analyzer, I'd like to run a query where the results are a count ...
    (comp.databases.ms-sqlserver)
  • Re: Dates, Times, and Databases
    ... > the datetime information down to the millisecond. ... > regardless of the trailing time units ... It seems that databases should be much smarter about ... Another idea would be to use your own implementation of data columns as ...
    (comp.lang.java.databases)
  • RE: Field mapping on Importing
    ... > I regularly import data from MS access databases to ... > SQL server using the enterprise manager. ... I click on every table and select the DateTime ... > methods for importing very different databases into my one I would love to ...
    (microsoft.public.sqlserver.programming)
  • Dates, Times, and Databases
    ... I have recently been asked to add a datetime column to my database ... that is not how databases currently work with datetime in my ... If you have a datetime field and the entry is '11/04/2004 ...
    (comp.lang.java.databases)