Click here to Skip to main content
15,072,714 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i have a stock data of comapy which is delayed of few mins
id update time price
5 2015-07-17 09:02:00.000 65.5
5 2015-07-17 09:03:00.000 65.5
5 2015-07-17 09:05:00.000 65.5
5 2015-07-17 09:06:00.000 66
5 2015-07-17 09:07:00.000 66
5 2015-07-17 09:08:00.000 66
5 2015-07-17 09:16:00.000 66.5
5 2015-07-17 09:17:00.000 66.3
5 2015-07-17 09:18:00.000 66.25
5 2015-07-17 09:19:00.000 66.3

i want 5min interval date and if next 5min date is not there query should latest date
(i.e 9.02,9.07,9.12<--not there so it sholud select 9.16,then 9.21 so on... )
5 is company id then updTime at last price
plz help me with logic
thanks in advance
Posted

1 solution

The solution depends how you actually want to handle missing values, is the previous or the next value taken from each 5 minutes and so on.

But some kind of idea could be to generate datetime values first and then try fetching the next stock value in time. So something like
SQL
create table stockdata (
   id int ,
   updatetime datetime,
   price float
);

insert into stockdata values ( 5, '2015-07-17T09:02:00.000', 65.5);
insert into stockdata values ( 5, '2015-07-17T09:03:00.000', 65.5);
insert into stockdata values ( 5, '2015-07-17T09:05:00.000', 65.5);
insert into stockdata values ( 5, '2015-07-17T09:06:00.000', 66);
insert into stockdata values ( 5, '2015-07-17T09:07:00.000', 66);
insert into stockdata values ( 5, '2015-07-17T09:08:00.000', 66);
insert into stockdata values ( 5, '2015-07-17T09:16:00.000', 66.5);
insert into stockdata values ( 5, '2015-07-17T09:17:00.000', 66.3);
insert into stockdata values ( 5, '2015-07-17T09:18:00.000', 66.25);
insert into stockdata values ( 5, '2015-07-17T09:19:00.000', 66.3);

declare @startdate datetime = convert(datetime, '2015-07-17T09:00:00.000', 126)
declare @enddate datetime = convert(datetime, '2015-07-17T10:00:00.000', 126)

;with minutes5 as (
	select @startdate DateAndTime
	union all
	select dateadd(minute, 5, minutes5.DateAndTime) DateAndTime
	from minutes5
	where DateAndTime <= @enddate
)
select * 
from minutes5 m, stockdata sd
where sd.updatetime >= m.DateAndTime
and sd.updatetime = (select min(sd2.updatetime)
                     from stockdata sd2
                     where sd2.updatetime >= m.DateAndTime
                     and   sd2.updatetime < dateadd(minute, 5, m.DateAndTime))
   
v3
Comments
Member 11844168 17-Jul-15 7:42am
   
error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Wendelius 17-Jul-15 7:51am
   
What is the whole query you try to run?
Member 11844168 17-Jul-15 8:02am
   
i copypaste ur code n replaced stockdata Table n updateTime col to my own table col ..
Wendelius 17-Jul-15 8:10am
   
The FROM clause was missing from the correlated inner query. I modified the query so have a try with the corrected one.
Member 11844168 17-Jul-15 9:17am
   
can u help me out this in sqlserer-2000????
Wendelius 17-Jul-15 11:46am
   
Since SQL Server 2000 does not support CTE's or anything like that I'd suggest that you create a table and using a stored procedure populate it with all the datetime values you want to use for the next 5 years or so.
Member 11844168 18-Jul-15 5:49am
   
@mika need ur help to understand this inner query
select min(sd2.updatetime)
from stockdata sd2
where sd2.updatetime >= m.DateAndTime
and sd2.updatetime < dateadd(minute, 5, m.DateAndTime)
Wendelius 19-Jul-15 2:50am
   
It fetches the smallest updatetime from the stockdata but with conditions that
- the update time must be at least the time in the outer query, so this is a correlation
- the update time must be less than the next item we are going to investigate in the outer query


Have a look at Correlated subquery[^]
Member 11844168 20-Jul-15 5:44am
   
thanks
Wendelius 20-Jul-15 5:50am
   
You're welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900