Create alert for table activity

Is it possible to create an alert to notify a user when a record is added to
a specific table? This is a error log table so the activity would/should be
very minimal.
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.
|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
David Gugick
Quest Software

Create alert for table activity

Is it possible to create an alert to notify a user when a record is added to
a specific table? This is a error log table so the activity would/should be
very minimal."doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
David Gugick
Quest Software

Create alert for table activity

Is it possible to create an alert to notify a user when a record is added to
a specific table? This is a error log table so the activity would/should be
very minimal."doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
--
David Gugick
Quest Softwaresql

Create a stored procedure in schema permision

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)The best way is to create a custum role with the permissions you need: no
built-in database role has such a permission design.
So, for example:
USE MyDatabase
CREATE ROLE MyNewRole
... add members to this new role:
EXEC sp_addrolemember 'MyNewRole', 'User1'
EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
then assign permissions:
GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
TO MyNewRole
...etc.
You'll find detailed explanations in BOL at the topics dedicated to the
different statements i've showed here
Gilberto Zampatti
"×?×?" wrote:
> I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which role
> let the login make ddl changes only in on schema (not in the dbo schema)|||Sorry. I don't know how to do that.
I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?
"Gilberto Zampatti" wrote:
> The best way is to create a custum role with the permissions you need: no
> built-in database role has such a permission design.
> So, for example:
> USE MyDatabase
> CREATE ROLE MyNewRole
> ... add members to this new role:
> EXEC sp_addrolemember 'MyNewRole', 'User1'
> EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> then assign permissions:
> GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> TO MyNewRole
> ...etc.
> You'll find detailed explanations in BOL at the topics dedicated to the
> different statements i've showed here
> Gilberto Zampatti
> "×?×?" wrote:
> > I want to let my developers to create/alter stored procedure only under a
> > specific schema. which role let the login create a procedure , or Which role
> > let the login make ddl changes only in on schema (not in the dbo schema)|||No users should executes the DDL changes: just a DBA (may be you?) should
create the Schema and assign the permissions.
To do that you can implement a script, or use Query analyzer in SQL 2000 OR
SQL Server management Studio in SQL 2005.
Gilberto Zampatti
"Gal" wrote:
> Sorry. I don't know how to do that.
>
> I need to let my user no ddl changes in dbo schema, only creaete(new)/update
> permision in MySchema schema.
> Can I?
> "Gilberto Zampatti" wrote:
> > The best way is to create a custum role with the permissions you need: no
> > built-in database role has such a permission design.
> > So, for example:
> >
> > USE MyDatabase
> > CREATE ROLE MyNewRole
> >
> > ... add members to this new role:
> >
> > EXEC sp_addrolemember 'MyNewRole', 'User1'
> > EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> >
> > then assign permissions:
> >
> > GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> > TO MyNewRole
> > ...etc.
> >
> > You'll find detailed explanations in BOL at the topics dedicated to the
> > different statements i've showed here
> > Gilberto Zampatti
> > "×?×?" wrote:
> >
> > > I want to let my developers to create/alter stored procedure only under a
> > > specific schema. which role let the login create a procedure , or Which role
> > > let the login make ddl changes only in on schema (not in the dbo schema)

counting number of specific rows in SQL

hi,

I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.

Thanks,

Dror.

I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:

create function dbo.MyFunction( @.MyParam varchar(10) )

returns @.ret table (MyCount int, MyAvg float)

begin

insert into @.ret (MyCount, MyAvg)

select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam

return

end

This function returns a table. To retrieve data, you will call it as follows:

select *

from dbo.MyFunction( @.MyParam)

Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.

Thanks,
Bryan

|||I am aware of the fact I can perform the same in VB - even eassier, but there is a reason for using function on the server. Supposed I want to set the number of rows to a variable, how do I do it ?|||

A VB function is not a good choice either. Are you using this data in SSRS?

Thanks,
Bryan

|||

Hi Bryan,

To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.

Thanks again for your help,

Dror.

|||

Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.

Code Snippet

create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT

as

select @.MyCount = count(*), @.MyAvg = avg(MyField)

from MyTable

where MyOtherField = @.MyParam

return 0

end

You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.

Thanks,
Bryan Smith

|||thank you very much for your great help Bryan.

Counting members within timerange

Hi,

I want to analyse support tickets... They are opened at a specific time and closed at a specific time... The question now is: How many support tickets are open at a specific date... Or better: I want to see a statistic for each day how many tickets were open on that day...

Besides thinking about some very fancy MDX stuff I only can imagine to build up a special fact table in my ETL process which builds a record per day per open ticket... That's also not nice...

Any other ideas?

Thanks,

Hi Thomas,

It's one of those problems that's easier in SQL than MDX. You could take an approach similar to the one I describe here:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!113.entry

...although in this case it's not just a simple sum. You would have a Start Date and an End Date dimension, then sum the set of all tickets which have a) start dates between the currentmembers on Start Date and End Date, or b) end dates between the currentmembers on Start Date and End Date, or c) start dates before the currentmember on Start Date and end dates after the currentmember on End Date (I think). Performance might be a problem, but I'd still prefer to do this than blow up the fact table in the ETL.

Regards,

Chris

|||

You might find this entry in Mosha's blog interesting as well:

Counting 'in-flight' events in MDX

Time dimension has special meaning in OLAP and DW. The classic problems involve looking at previous period, parallel period, computing moving averages and running sums. Today we will look into less common, but nevertheless interesting problem of 'in-flight' events. This scenario arises when there is more than one date/time attribute associated with the fact.

...

|||

...interesting read... Thanks for the link!

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.