January 9, 2012 at 5:04 am
hi,
i have a table with every minute data, if i want to select data for every 15 minute how can i do it.
for example
01/05/2012 5:40:00 PM 1
01/05/2012 5:39:00 PM 5
01/05/2012 5:38:00 PM 6
01/05/2012 5:37:00 PM 7
01/05/2012 5:36:00 PM 10
01/05/2012 5:35:00 PM 15
01/05/2012 5:34:00 PM 19
01/05/2012 5:33:00 PM 457
01/05/2012 5:32:00 PM 78
01/05/2012 5:31:00 PM 89
01/05/2012 5:30:00 PM 78
in the above data i want 01/05/2012 5:40:00 PM , 01/05/2012 5:35:00 PM and 01/05/2012 5:30:00 PM like this it goes.
i want to select the other columns also.
thanks,
regards,
ami
January 9, 2012 at 5:30 am
Anamika (1/9/2012)
hi,i have a table with every minute data, if i want to select data for every 15 minute how can i do it.
for example
01/05/2012 5:40:00 PM 1
01/05/2012 5:39:00 PM 5
01/05/2012 5:38:00 PM 6
01/05/2012 5:37:00 PM 7
01/05/2012 5:36:00 PM 10
01/05/2012 5:35:00 PM 15
01/05/2012 5:34:00 PM 19
01/05/2012 5:33:00 PM 457
01/05/2012 5:32:00 PM 78
01/05/2012 5:31:00 PM 89
01/05/2012 5:30:00 PM 78
in the above data i want 01/05/2012 5:40:00 PM , 01/05/2012 5:35:00 PM and 01/05/2012 5:30:00 PM like this it goes.
i want to select the other columns also.
thanks,
regards,
ami
You mention every 15 minutes, but then your sample output is every 5 - or am I missing something?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 9, 2012 at 5:35 am
Use something like this :
WHERE DatePart(minute, Your_Date_Field) % 15 = 0
January 9, 2012 at 10:23 am
sorry about the interval mistake. lets take 5 minutes only.
create table #t (d1 int primary key identity(1,1), v1 varchar(50), v2 int, v3 int)
insert into #t values('01/05/2012 5:59:00 PM',322667648,330025988)
insert into #t values('01/05/2012 5:58:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:57:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:56:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:55:00 PM',322667648,330025788)
insert into #t values('01/05/2012 5:54:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:53:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:52:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:51:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:50:00 PM',322667648,330025688)
insert into #t values('01/05/2012 5:49:00 PM',322667948,330025888)
insert into #t values('01/05/2012 5:48:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:47:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:46:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:45:00 PM',322667628,330025588)
select * from #t
from the above table i want to take every 5th record and subtract it from the 1st record. it is for every 5 minutes gap. as i find it difficult to process with dates, i've added a identity column with that we can take every 5th record. the v1 of 5th record need to get subtract from 1st record. and 10th record subtract from 5th record. and it goes like this.
how can i achieve this?
thanks,
regards,
ami
January 10, 2012 at 4:58 pm
Ami,
First of all, you should never use a VARCHAR column for dates - especially if you want to sort them. I've changed your create table statement to:
create table #t (d1 int primary key identity(1,1), v1 DATETIME, v2 int, v3 int)
Secondly. If you are using real tables in the database rather than a temp table you can't guarantee that the IDENTITY column will actually be consecutive. Any attempted insert into the table that fails will increment the identity and you will have a gap. The only way to guarantee consecutive row numbering in a table is to row number it yourself.
Third, you have to have some sort of range to deal with. You have to know where to start and where to end.. Here's an example that should give you what you want. This example works in ascending order and compares the a row to the one 5 rows back in time. If you wanted to start with the latest row and compare it to the row 5 rows earlier in time, then you just need to change the ASC to DESC in CTEOrdered.
DECLARE
@StartDateDATETIME
, @EndDateDATETIME
SET @StartDate = '1/5/2012'
SET @EndDate = '1/6/2012'
; WITH CTEOrdered AS
( SELECT v1, v2, v3
, ROW_NUMBER() OVER (ORDER BY v1 ASC) AS RowNum
FROM #t
WHERE v1 >+ @StartDate
AND v1 < @EndDate
), CTE5thRow AS
( SELECT v1, v2, v3, RowNum
FROM CTEOrdered
WHERE RowNum % 5 = 1
)
SELECT CTE1.v1, CTE1.v2, CTE1.v3, CTE1.RowNum
, C2.v1, C2.v2, C2.v3, C2.RowNum AS PriorRowNum
, CTE1.v2 - C2.v2 AS V2Diff
, CTE1.v3 - C2.v3 AS V3Diff
FROM CTE5thRow AS CTE1
OUTER APPLY
(SELECT TOP 1 CTE2.v1, CTE2.v2, CTE2.v3, CTE2.RowNum
FROM CTE5thRow AS CTE2
WHERE CTE2.v1 < CTE1.V1
ORDER BY CTE2.RowNum DESC
) AS C2
Todd Fifield
January 17, 2012 at 9:18 am
Hi,
I came up with the following, maybe you like it and is what you need.
Thanks
-- Test table definition
DECLARE @t TABLE(DateStamp DATETIME PRIMARY KEY CLUSTERED, V1 INT, V2 INT)
-- Test data population
insert into @t values('01/05/2012 5:59:00 PM',322667648,330025988)
insert into @t values('01/05/2012 5:58:00 PM',322668648,330025888)
insert into @t values('01/05/2012 5:57:00 PM',322668648,330025888)
insert into @t values('01/05/2012 5:56:00 PM',322667648,330025888)
insert into @t values('01/05/2012 5:55:00 PM',322667648,330025788)
insert into @t values('01/05/2012 5:54:00 PM',322668648,330025888)
insert into @t values('01/05/2012 5:53:00 PM',322667648,330025888)
insert into @t values('01/05/2012 5:52:00 PM',322667648,330025888)
insert into @t values('01/05/2012 5:51:00 PM',322667648,330025888)
insert into @t values('01/05/2012 5:50:00 PM',322667648,330025688)
insert into @t values('01/05/2012 5:49:00 PM',322667948,330025888)
insert into @t values('01/05/2012 5:48:00 PM',322667648,330025888)
insert into @t values('01/05/2012 5:47:00 PM',322667648,330025888)
insert into @t values('01/05/2012 5:46:00 PM',322667648,330025888)
insert into @t values('01/05/2012 5:45:00 PM',322667628,330025588)
-- Get the first value recorded value
;WITH FirstReading
AS
(
SELECT TOP 1 * FROM @t
ORDER BY DateStamp
)
-- Get the comparison of the first record whith the records that have a differense of 5 minutes increments.
SELECT FirstDate = fr.DateStamp,
FirtsValue1 = fr.V1,
FirstValue2 = fr.V2,
LaterDate = lr.DateStamp,
LaterValue1 = lr.V1,
LaterValue2 = lr.V2,
MinuteDifference = datediff(minute ,fr.DateStamp, lr.DateStamp),
Value1Difference = lr.V1 - fr.V1,
Value2Difference = lr.V2 - fr.V2
FROM FirstReading fr
JOIN @t lr
ON lr.DateStamp > fr.DateStamp AND
datediff(minute ,fr.DateStamp, lr.DateStamp) % 5 = 0
January 18, 2012 at 4:19 am
Hi anamika
you are there
you already insert a identity col
instead of starting from 1 just start from 0
and then select the records using the remainder operator
select * from tbl where Id%5=0
from the result
now you can process easily what you want
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
January 18, 2012 at 4:39 am
Anamika (1/9/2012)
sorry about the interval mistake. lets take 5 minutes only.
create table #t (d1 int primary key identity(1,1), v1 varchar(50), v2 int, v3 int)
insert into #t values('01/05/2012 5:59:00 PM',322667648,330025988)
insert into #t values('01/05/2012 5:58:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:57:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:56:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:55:00 PM',322667648,330025788)
insert into #t values('01/05/2012 5:54:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:53:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:52:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:51:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:50:00 PM',322667648,330025688)
insert into #t values('01/05/2012 5:49:00 PM',322667948,330025888)
insert into #t values('01/05/2012 5:48:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:47:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:46:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:45:00 PM',322667628,330025588)
select * from #t
from the above table i want to take every 5th record and subtract it from the 1st record. it is for every 5 minutes gap. as i find it difficult to process with dates, i've added a identity column with that we can take every 5th record. the v1 of 5th record need to get subtract from 1st record. and 10th record subtract from 5th record. and it goes like this.
how can i achieve this?
thanks,
regards,
ami
You haven't specified what you want to subtract from what. Have a look at the result of the following query, it returns the current row on the left and the row from 5 rows back on the right. Remove the columns you don't need and add your calculation to the output.
drop table #t
create table #t (d1 int primary key identity(1,1), v1 DATETIME, v2 int, v3 int)
insert into #t values('01/05/2012 5:59:00 PM',322667648,330025988)
insert into #t values('01/05/2012 5:58:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:57:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:56:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:55:00 PM',322667648,330025788)
insert into #t values('01/05/2012 5:54:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:53:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:52:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:51:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:50:00 PM',322667648,330025688)
insert into #t values('01/05/2012 5:49:00 PM',322667948,330025888)
insert into #t values('01/05/2012 5:48:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:47:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:46:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:45:00 PM',322667628,330025588)
;WITH SequencedData AS (
SELECT d1, v1, v2, v3, seq = ROW_NUMBER() OVER (ORDER BY v1 DESC)
from #t newest
)
SELECT Newest.*, Oldest.*
FROM SequencedData Newest
LEFT JOIN SequencedData Oldest ON Oldest.seq = Newest.seq+5
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply