Counting consecutive days

My specific problem:
I have a DB full of daily stock prices including the open, close, high,
and low. I want to A) select all stocks whose current day high is lower
then the previous day's high for at least 5 consecutive days and B)
count the number of days that stock has displayed this trend.
I can do A. I created a UDF that returns all stocks that display this
trend for at least 5 days. I am having problems with B. I've been
trying to write to different queries and then try to combine them but
I'm having some problems.
A)
========================================
=================================
/* returns list of stocks that fit the 5 day HIGH */
CREATE FUNCTION dbo.udf_List5DayHigh(@.date1 DATETIME, @.date2
DATETIME,@.date3 DATETIME,@.date4 DATETIME,@.date5 DATETIME,@.date6 DATETIME )
RETURNS TABLE
AS
RETURN
select e.equityID, e.tickerRealtick, p1.high from equities e, prices
p1, prices p2, prices p3, prices p4, prices p5, prices p6
where e.equityID = p1.equityID and e.equityID = p2.equityID and
e.equityID = p3.equityID and e.equityID = p4.equityID and e.equityID =
p5.equityID
and e.equityID = p6.equityID
and p1.priceDate = @.date1 and p2.priceDate = @.date2 and p3.priceDate =
@.date3 and
p4.priceDate = @.date4 and p5.priceDate = @.date5 and p6.priceDate =@.date6
and p1.high < p2.high and p2.high < p3.high and p3.high < p4.high and
p4.high < p5.high and p5.high < p6.high
========================================
=================================
Table Descriptions:
Equities
--
equityID (PK)
tickerRealtick
Prices
--
priceID (PK)
equityID
priceDate
high
low
close
open
==============
select e.tickerRealTick, p.priceDate, p.high,p2.priceDate, p2.high
from prices p, prices p2, equities e
where e.equityID = 3783 and p.equityID = e.equityID and p2.equityID =
e.equityID and p.priceDate = dateadd(d, case when
datepart(dw,p.PriceDate) = 2 then -3
else -1 end, p2.priceDate)
and p.high > p2.high and p.priceDate = '3/1/05'
order by p.priceDate desc
====================
I think couting should be something like this.
General problem others might be more familiar with:
You store total sales for a retail store in a SQL server. You want to
count the number of consecutive days that sales are up?Won,
See if this is a helpful start.
create table T (
cust int,
dte smalldatetime,
sale money,
primary key (cust, dte)
)
insert into T
select EmployeeID, cast('20050101' as datetime) + ProductID,
abs(binary_checksum(newid())%10000)/$100 + 2*(100+ProductID)
from Northwind..Products, Northwind..Employees
go
create view UpsAndDowns as
select Tnow.cust, Tnow.dte, sign(Tnow.sale - Tyest.sale) as trend
from T as Tnow, T as Tyest
where Tnow.dte = Tyest.dte + 1
and Tnow.cust = Tyest.cust
go
select cust, trendStart, trendEnd, trendLen
from (
select V1.cust, V1.dte - 1 as trendStart, V2.dte as trendEnd,
1+datediff(day,V1.dte,V2.dte) as trendLen
from UpsAndDowns as V1, UpsAndDowns as V2
where V2.cust = V1.cust
and V2.trend = 1
and V2.trend <> -V1.trend
and V2.dte >= V1.dte
and not exists (
select * from UpsAndDowns as V3
where V3.cust = V1.cust
and V3.dte > V1.dte
and V3.dte < V2.dte
and V3.trend = -V1.trend
)
) T
order by cust, trendStart, trendEnd
go
drop view UpsAndDowns
drop table T
Steve Kass
Drew University
Won Lee wrote:

> My specific problem:
> I have a DB full of daily stock prices including the open, close,
> high, and low. I want to A) select all stocks whose current day high
> is lower then the previous day's high for at least 5 consecutive days
> and B) count the number of days that stock has displayed this trend.
> I can do A. I created a UDF that returns all stocks that display this
> trend for at least 5 days. I am having problems with B. I've been
> trying to write to different queries and then try to combine them but
> I'm having some problems.
> A)
> ========================================
=================================
> /* returns list of stocks that fit the 5 day HIGH */
> CREATE FUNCTION dbo.udf_List5DayHigh(@.date1 DATETIME, @.date2
> DATETIME,@.date3 DATETIME,@.date4 DATETIME,@.date5 DATETIME,@.date6
> DATETIME )
> RETURNS TABLE
> AS
> RETURN
> select e.equityID, e.tickerRealtick, p1.high from equities e, prices
> p1, prices p2, prices p3, prices p4, prices p5, prices p6
> where e.equityID = p1.equityID and e.equityID = p2.equityID and
> e.equityID = p3.equityID and e.equityID = p4.equityID and e.equityID =
> p5.equityID
> and e.equityID = p6.equityID
> and p1.priceDate = @.date1 and p2.priceDate = @.date2 and p3.priceDate =
> @.date3 and
> p4.priceDate = @.date4 and p5.priceDate = @.date5 and p6.priceDate
> =@.date6
> and p1.high < p2.high and p2.high < p3.high and p3.high < p4.high and
> p4.high < p5.high and p5.high < p6.high
> ========================================
=================================
> Table Descriptions:
>
> Equities
> --
> equityID (PK)
> tickerRealtick
> Prices
> --
> priceID (PK)
> equityID
> priceDate
> high
> low
> close
> open
> ==============
> select e.tickerRealTick, p.priceDate, p.high,p2.priceDate, p2.high
> from prices p, prices p2, equities e
> where e.equityID = 3783 and p.equityID = e.equityID and p2.equityID =
> e.equityID and p.priceDate = dateadd(d, case when
> datepart(dw,p.PriceDate) = 2 then -3
> else -1 end, p2.priceDate)
> and p.high > p2.high and p.priceDate = '3/1/05'
> order by p.priceDate desc
> ====================
> I think couting should be something like this.
>
>
> General problem others might be more familiar with:
> You store total sales for a retail store in a SQL server. You want to
> count the number of consecutive days that sales are up?|||Steve Kass wrote:
> Won,
> See if this is a helpful start.
> create table T (
> cust int,
> dte smalldatetime,
> sale money,
> primary key (cust, dte)
> )
> insert into T
> select EmployeeID, cast('20050101' as datetime) + ProductID,
> abs(binary_checksum(newid())%10000)/$100 + 2*(100+ProductID)
> from Northwind..Products, Northwind..Employees
> go
> create view UpsAndDowns as
> select Tnow.cust, Tnow.dte, sign(Tnow.sale - Tyest.sale) as trend
> from T as Tnow, T as Tyest
> where Tnow.dte = Tyest.dte + 1
> and Tnow.cust = Tyest.cust
> go
> select cust, trendStart, trendEnd, trendLen
> from (
> select V1.cust, V1.dte - 1 as trendStart, V2.dte as trendEnd,
> 1+datediff(day,V1.dte,V2.dte) as trendLen
> from UpsAndDowns as V1, UpsAndDowns as V2
> where V2.cust = V1.cust
> and V2.trend = 1
> and V2.trend <> -V1.trend
> and V2.dte >= V1.dte
> and not exists (
> select * from UpsAndDowns as V3
> where V3.cust = V1.cust
> and V3.dte > V1.dte
> and V3.dte < V2.dte
> and V3.trend = -V1.trend
> )
> ) T
> order by cust, trendStart, trendEnd
> go
> drop view UpsAndDowns
> drop table T
>
> Steve Kass
> Drew University
Steve,
Thanks will take a look at that.|||"Won Lee" <nospam@.nospam.com> wrote in message
news:%23pN4HO0HFHA.2620@.tk2msftngp13.phx.gbl...
> My specific problem:
> I have a DB full of daily stock prices including the open, close, high, an
d
> low. I want to A) select all stocks whose current day high is lower then
the
> previous day's high for at least 5 consecutive days and B) count the numbe
r of
> days that stock has displayed this trend.
> I can do A. I created a UDF that returns all stocks that display this tre
nd
> for at least 5 days. I am having problems with B. I've been trying to wr
ite
> to different queries and then try to combine them but I'm having some
> problems.
> A)
> ========================================
=================================
> /* returns list of stocks that fit the 5 day HIGH */
> CREATE FUNCTION dbo.udf_List5DayHigh(@.date1 DATETIME, @.date2 DATETIME,@.dat
e3
> DATETIME,@.date4 DATETIME,@.date5 DATETIME,@.date6 DATETIME )
> RETURNS TABLE
> AS
> RETURN
> select e.equityID, e.tickerRealtick, p1.high from equities e, prices p1,
> prices p2, prices p3, prices p4, prices p5, prices p6
> where e.equityID = p1.equityID and e.equityID = p2.equityID and e.equityID
=
> p3.equityID and e.equityID = p4.equityID and e.equityID = p5.equityID
> and e.equityID = p6.equityID
> and p1.priceDate = @.date1 and p2.priceDate = @.date2 and p3.priceDate = @.da
te3
> and
> p4.priceDate = @.date4 and p5.priceDate = @.date5 and p6.priceDate =@.date6
> and p1.high < p2.high and p2.high < p3.high and p3.high < p4.high and p4.h
igh
> < p5.high and p5.high < p6.high
> ========================================
=================================
> Table Descriptions:
>
> Equities
> --
> equityID (PK)
> tickerRealtick
> Prices
> --
> priceID (PK)
> equityID
> priceDate
> high
> low
> close
> open
> ==============
> select e.tickerRealTick, p.priceDate, p.high,p2.priceDate, p2.high
> from prices p, prices p2, equities e
> where e.equityID = 3783 and p.equityID = e.equityID and p2.equityID =
> e.equityID and p.priceDate = dateadd(d, case when datepart(dw,p.PriceDate)
= 2
> then -3
> else -1 end, p2.priceDate)
> and p.high > p2.high and p.priceDate = '3/1/05'
> order by p.priceDate desc
> ====================
> I think couting should be something like this.
>
>
> General problem others might be more familiar with:
> You store total sales for a retail store in a SQL server. You want to cou
nt
> the number of consecutive days that sales are up?
CREATE TABLE Prices
(
ticker VARCHAR(5) NOT NULL,
priceDate DATETIME NOT NULL,
highPrice DECIMAL (8,2) NOT NULL CHECK (highPrice > 0),
PRIMARY KEY (ticker, priceDate)
)
-- 1-day trends, that is, how price moves between consecutive trading
-- days for each stock
-- Direction of +1 is an uptrend, -1 is a downtrend, and 0 is no change
-- Note that stock price dates are not necessarily consecutive days
-- because of wends, holidays, unplanned closings, suspension
-- of trading of a particular issue, or simply missing data
CREATE VIEW PriceTrends1Day (ticker, priceDate, direction)
AS
SELECT P2.ticker, P2.priceDate, SIGN(P2.highPrice - P1.highPrice)
FROM Prices AS P1
INNER JOIN
Prices AS P2
ON P1.ticker = P2.ticker AND
P1.priceDate =
(SELECT MAX(priceDate)
FROM Prices
WHERE ticker = P2.ticker AND
priceDate < P2.priceDate)
-- startDate is inclusive for a trend and endDate is exclusive, i.e.,
-- [startDate, endDate)
-- An endDate of NULL indicates trend is current
-- nDays is the number of days of trend
-- Note that if IBM on 3 consecutive trading days is 100, 101, and 102
-- then this is a 2-day uptrend
CREATE VIEW PriceTrends (ticker, startDate, endDate, direction, nDays)
AS
SELECT T.ticker, T.startDate, T.endDate, T.direction, COUNT(*)
FROM (SELECT ticker,
MIN(priceDate), newDirectionDate,
direction
FROM (SELECT PT1.ticker,
PT1.priceDate,
MIN(PT2.priceDate),
PT1.direction
FROM PriceTrends1Day AS PT1
LEFT OUTER JOIN
PriceTrends1Day AS PT2
ON
PT1.ticker = PT2.ticker AND
PT2.priceDate > PT1.priceDate AND
PT2.direction <> PT1.direction
GROUP BY PT1.ticker, PT1.priceDate, PT1.direction)
AS
CT(ticker, priceDate, newDirectionDate, direction)
GROUP BY ticker, newDirectionDate, direction) AS
T(ticker, startDate, endDate, direction)
INNER JOIN
Prices AS P
ON P.ticker = T.ticker AND
P.priceDate >= T.startDate AND
P.priceDate < COALESCE(T.endDate, '99991231')
GROUP BY T.ticker, T.startDate, T.endDate, T.direction
-- All stocks and dates with a downtrend of at least 5 days
SELECT ticker, startDate, endDate, direction, nDays
FROM PriceTrends
WHERE direction = -1 AND nDays >= 5
JAG|||>
> CREATE TABLE Prices
> (
> ticker VARCHAR(5) NOT NULL,
> priceDate DATETIME NOT NULL,
> highPrice DECIMAL (8,2) NOT NULL CHECK (highPrice > 0),
> PRIMARY KEY (ticker, priceDate)
> )
> -- 1-day trends, that is, how price moves between consecutive trading
> -- days for each stock
> -- Direction of +1 is an uptrend, -1 is a downtrend, and 0 is no change
> -- Note that stock price dates are not necessarily consecutive days
> -- because of wends, holidays, unplanned closings, suspension
> -- of trading of a particular issue, or simply missing data
> CREATE VIEW PriceTrends1Day (ticker, priceDate, direction)
> AS
> SELECT P2.ticker, P2.priceDate, SIGN(P2.highPrice - P1.highPrice)
> FROM Prices AS P1
> INNER JOIN
> Prices AS P2
> ON P1.ticker = P2.ticker AND
> P1.priceDate =
> (SELECT MAX(priceDate)
> FROM Prices
> WHERE ticker = P2.ticker AND
> priceDate < P2.priceDate)
> -- startDate is inclusive for a trend and endDate is exclusive, i.e.,
> -- [startDate, endDate)
> -- An endDate of NULL indicates trend is current
> -- nDays is the number of days of trend
> -- Note that if IBM on 3 consecutive trading days is 100, 101, and 102
> -- then this is a 2-day uptrend
> CREATE VIEW PriceTrends (ticker, startDate, endDate, direction, nDays)
> AS
> SELECT T.ticker, T.startDate, T.endDate, T.direction, COUNT(*)
> FROM (SELECT ticker,
> MIN(priceDate), newDirectionDate,
> direction
> FROM (SELECT PT1.ticker,
> PT1.priceDate,
> MIN(PT2.priceDate),
> PT1.direction
> FROM PriceTrends1Day AS PT1
> LEFT OUTER JOIN
> PriceTrends1Day AS PT2
> ON
> PT1.ticker = PT2.ticker AND
> PT2.priceDate > PT1.priceDate
AND
> PT2.direction <> PT1.direction
> GROUP BY PT1.ticker, PT1.priceDate, PT1.direc
tion)
> AS
> CT(ticker, priceDate, newDirectionDate, direct
ion)
> GROUP BY ticker, newDirectionDate, direction) AS
> T(ticker, startDate, endDate, direction)
> INNER JOIN
> Prices AS P
> ON P.ticker = T.ticker AND
> P.priceDate >= T.startDate AND
> P.priceDate < COALESCE(T.endDate, '99991231')
> GROUP BY T.ticker, T.startDate, T.endDate, T.direction
> -- All stocks and dates with a downtrend of at least 5 days
> SELECT ticker, startDate, endDate, direction, nDays
> FROM PriceTrends
> WHERE direction = -1 AND nDays >= 5
> --
> JAG
John,
Thanks for post. I am going to read over it carefully and see how it
works out.|||John Gilson wrote:
> CREATE TABLE Prices
> (
> ticker VARCHAR(5) NOT NULL,
> priceDate DATETIME NOT NULL,
> highPrice DECIMAL (8,2) NOT NULL CHECK (highPrice > 0),
> PRIMARY KEY (ticker, priceDate)
> )
> -- 1-day trends, that is, how price moves between consecutive trading
> -- days for each stock
> -- Direction of +1 is an uptrend, -1 is a downtrend, and 0 is no change
> -- Note that stock price dates are not necessarily consecutive days
> -- because of wends, holidays, unplanned closings, suspension
> -- of trading of a particular issue, or simply missing data
> CREATE VIEW PriceTrends1Day (ticker, priceDate, direction)
> AS
> SELECT P2.ticker, P2.priceDate, SIGN(P2.highPrice - P1.highPrice)
> FROM Prices AS P1
> INNER JOIN
> Prices AS P2
> ON P1.ticker = P2.ticker AND
> P1.priceDate =
> (SELECT MAX(priceDate)
> FROM Prices
> WHERE ticker = P2.ticker AND
> priceDate < P2.priceDate)
> -- startDate is inclusive for a trend and endDate is exclusive, i.e.,
> -- [startDate, endDate)
> -- An endDate of NULL indicates trend is current
> -- nDays is the number of days of trend
> -- Note that if IBM on 3 consecutive trading days is 100, 101, and 102
> -- then this is a 2-day uptrend
> CREATE VIEW PriceTrends (ticker, startDate, endDate, direction, nDays)
> AS
> SELECT T.ticker, T.startDate, T.endDate, T.direction, COUNT(*)
> FROM (SELECT ticker,
> MIN(priceDate), newDirectionDate,
> direction
> FROM (SELECT PT1.ticker,
> PT1.priceDate,
> MIN(PT2.priceDate),
> PT1.direction
> FROM PriceTrends1Day AS PT1
> LEFT OUTER JOIN
> PriceTrends1Day AS PT2
> ON
> PT1.ticker = PT2.ticker AND
> PT2.priceDate > PT1.priceDate
AND
> PT2.direction <> PT1.direction
> GROUP BY PT1.ticker, PT1.priceDate, PT1.direc
tion)
> AS
> CT(ticker, priceDate, newDirectionDate, direct
ion)
> GROUP BY ticker, newDirectionDate, direction) AS
> T(ticker, startDate, endDate, direction)
> INNER JOIN
> Prices AS P
> ON P.ticker = T.ticker AND
> P.priceDate >= T.startDate AND
> P.priceDate < COALESCE(T.endDate, '99991231')
> GROUP BY T.ticker, T.startDate, T.endDate, T.direction
> -- All stocks and dates with a downtrend of at least 5 days
> SELECT ticker, startDate, endDate, direction, nDays
> FROM PriceTrends
> WHERE direction = -1 AND nDays >= 5
> --
> JAG
>
John,
The first part of query works. I went through it to make sure I
understand. The second view returns an error.
SELECT equityID, startDate, endDate, direction, nDays
FROM PriceTrends
WHERE direction = -1 AND nDays >= 5
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.|||> The first part of query works. I went through it to make sure I
> understand. The second view returns an error.
> SELECT equityID, startDate, endDate, direction, nDays
> FROM PriceTrends
> WHERE direction = -1 AND nDays >= 5
>
> Server: Msg 8624, Level 16, State 3, Line 1
> Internal SQL Server error.
Never mind. I googled the sql server error code and was
because the KB was about delete with not in. Then I returned to google
and looked a few lines down and it described my issue.
Thanks.