How to fetch the top record with in the every 15 mins slot?

Question

I have to categorize the data based on the datetime, on the basis of 15 mins slot, 30 mins slot and 1 hour basis. I wrote the query like this for 1 hour slot,

select
    a.datetime, feederid,
    vr, vy, vb
from (
    select datetime,feederid, vr, vy, vb, tt, DENSE_RANK() OVER (
        partition by dateadd(hour, datediff(hour, 0, datetime), 0), feederid
        ORDER BY feederid,datetime
    ) rank1
    from pseb.dbo.datasource
    where 
    convert(datetime,datetime) between '2011-06-12' and '2011-06-12 23:59:00'
) a
where rank1 = 1

but i don't know how to partition the data for 15 mins slot values.

My table resultset is like this

    DATETIME           FeederID  VR     VY  VB
    2011-06-12 00:09:50 4731    199.148 0   212.69
    2011-06-12 00:05:31 4731    178.531 0   242.838
    2011-06-12 00:36:20 4731    174.622 0   239.756
    2011-06-12 01:10:03 4731    175.645 0   240.328
    2011-06-12 13:10:07 4731    196.387 76.991  241.798
    2011-06-12 18:35:46 4731    207.719 54.756  251.855

here 00:00 to 15:00 minutes slot there is 2 records, I need the top 1 order by datetime desc.

Help me to do it.

I can write this query for you. I charge $100/hour with a minimum billed time of two hours, paid up-front. Do you have Paypal? — , Feb 22 '13 at 06:32
@Saranya: the downvotes on this question are as a result of not appearing to have tried to solve it yourself. Questions here should contain detail about what you have attempted, what happened, and what you expected. — halfer, Feb 22 '13 at 07:09
Great! Edit that comment into your question, and next time you ask something, remember to add a similar level of detail `:)`. Aside: I have a "how to ask questions" guide linked in my profile, which may be of interest to you. — halfer, Feb 22 '13 at 07:27
A _much_ better question! Undownvoted and upvoted instead. You can delete your comment above if you wish (the one with your query in) since it duplicates the info you've provided. The one minor improvement I'd suggest is to format the SQL query in your question with indentation, so it is easier to read. A good tip for asking questions is: make life easy for people helping you `:)`. — halfer, Feb 22 '13 at 07:40
[What is the best way to truncate a date in SQL Server?](http://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server) — Andriy M, Feb 22 '13 at 08:03
I've reformatted your query for you, and it seems there are two errors - you've used `a` where you meant `AND`, and you have an extra `WHERE` keyword in there, both of which will stop the query from working. Always try queries in a database server before asking `:)` — halfer, Feb 22 '13 at 10:02
no I named the complete resultset as 'a'. Resultset contains all the records, from that im taking only the Rank 1 records, Thats y i gave the condition for the a subset where Rank1=1. — Saranya Jothiprakasam, Feb 22 '13 at 10:16
Ah, my apologies; I miscounted the brackets in my own formatting! Reformatted again now. — halfer, Feb 22 '13 at 13:34

score 2 · Accepted Answer · edited Feb 22 '13 at 13:39

2

Either use a windowing function or a group by. The real trick is to use a sort key of a 15 minute window.

Select * from 
  (
    Select
      dateadd(
        second, -1 * datepart(second, datetime)
        , dateadd(minute, mod(datepart(minute, datetime),15) * -1, datetime)
      )
      , feederid, vr, vy, vb
      , rank() over (
        partition by dateadd(
          second
          , -1 * datepart(second, datetime)
          , dateadd(minute, mod(datepart(minute, datetime),15) * -1, datetime)
        ) order by datetime desc
      ) rnk
   ) where rnk = 1

edited Feb 22 '13 at 13:39

halfer

19,824
17
99
186

answered Feb 22 '13 at 06:44

Jeffrey Melloy

421
1
3
7

Thanks Jeffrey , I had an idea to use the rank function, but i don't know how to partition those datetime values. now i get to know.. :) – Saranya Jothiprakasam Feb 22 '13 at 07:02

score 2 · Answer 2 · answered Feb 22 '13 at 06:45

firstly, it is a humble request to be precise and explain your problem more deeply.

Still, I had given solution on basis of your question. try this one. Please inform whether it is working or not?

declare @s datetime 
select @s=(SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond
)
print @s
select top 1 * from tablename where m like @s group by datetime desc

score 1 · Answer 3 · answered Feb 22 '13 at 06:31

You need to first fetch all records within the fifteen min slot and order them by the column datetime using desc and set the limit as 1 to return the topmost row of the fetched rows to more about limits look at http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

http://www.w3schools.com/sql/sql_top.asp

How to fetch the top record with in the every 15 mins slot?

3 Answers3