I need to make a report that takes a rolling 12 month period.
Each row needs to show the sum of the current months value and all the 11 months prior.
For example, I have a count of items in their respective Months
2 - Jan 06
5 - Feb 06
2 - Mar 06
4 - April 06
2 - May 06
1 - June 06
2 - July 06
5 - Aug 06
2 - Sep 06
2 - Oct 06
4 - Nov 06
2 - Dec 06
2 - Jan 07
3 - Feb 07
1 - Mar 07
What I need as an output is
Jan 07 = 35
Feb 07 = 33
Mar 07 = 32
So each row sums the current and previous 11 rows.
I am using Crystal Report v10 and SQL Database.
Thanks,
DavidThe quick solution in my mind is.
Create a subreport and store the whole data in an array.
Now come to the actual report and while showing the current record you can sum the previous 11 months records from the array and show it there.|||Great thanks for the Idea, I got it working with an array and then another formula summing the 12 in the array. I haven't played with the chart function yet, i have to get all these fields totaling first. Any suggestions getting all the fields in the details section ploted into a chart (the ultimate goal. Each displayed point is a date and the summed 12 month total.|||Keep one thing in mind while dealing with the charts.
You can show/draw more then one values on change of some value. e.g on change of date you can show the sum of previous 11 months.
Also you can get the accumulative .
You have to explore the charts for your project.|||Thanks so much for your help. Sometimes I just need a little bump into the right direction and I can figure it out. I got it working now.
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Sunday, March 25, 2012
Saturday, February 25, 2012
CPackage::LoadFromXML Failure
My current package and all backups of previous packages are giving me this error. Anyone got any ideas? I have 1 GB free disk space. Last OS update a month ago. Ready to reinstall SSIS.
Error 1 Error loading SanDiegoRiverside.dtsx: The connection type "FLATFILE" specified for connection manager "ExtractDenormalizedErrors" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
Error 2 Error loading SanDiegoRiverside.dtsx: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ExtractDenormalizedErrors</DTS:Property><DTS:Property DTS:Name="DTSID">{92AFE6E1-8EA4-4CE5-BDA1-" from node "DTS:ConnectionManager". c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
Error 3 Error loading 'SanDiegoRiverside.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=179671&SiteID=1
and the KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817|||
FYI: This was caused by Flash8 Player Installer.
Laurence
|||We got the same in production tonight, the package have been running fine for 209 nights and have never been changed. Rerunning the package works fine, I can not reproduce the error. We are running Win2003 X64 Ent. + SQL2K5 X64 Ent SP1. SSIS and RDBMS runs on the same box.
I'm not sure I would like re-register any dll's until I know the cause. Any thoughts?
Thanks in advance!
Niklas
Started: 01:13:52 Error: 2007-03-22 01:13:52.67
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">NAV_det</DTS:Property><DTS:Property DTS:Name="DTSID">{C7269043-F2DD-4AE4-A831-C4E5D0632016}</DTS" from node "DTS:ConnectionManager".
End Error
Could not load package "Import_File_To_Table.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source:
Started: 01:13:52
Finished: 01:13:52
Elapsed: 0.188 seconds
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
I am running into the same issue as LKHall ... I am confused... How come does "This was caused by Flash8 Player Installer" FIX the issue. It does not make sense to me.
Any one knows what is the root cause for this error ?
|||I have unintalled the Flash driver and reboot my PC and I am still running into the package loading failure. Help....|||
The issue seems to be related to DLLs not being registered correctly. When you install new software (the Flash Player, for example) it may register an older or newer version of a DLL, which can can break other applications. Uninstalling may not fix the problem, as that usually does not reverse the changes to the registry.
Instead you may need to re-install the application that is failing, or re-register the DLLs indicated in the articles above.
|||Thanks John.... Can you please be more specific on what applications (or what DLLs) i need to re-install ? Do you suggest that I need to re-install SQL 2005 again?
I have used the link above to check registry and did not see any wrong registry.
|||Yes, reinstalling SQL 2005 is one way to make sure eveything is set up correctly.|||Reinstalling SQL 2005 is a good suggestion but any other better ideas ? I have SQL 2005 SP2 installed and reinstalling the entire software is not quite easy....
Plus, I really want to know what is the root cause behind this kind of weird behavior? Doesn't MSFT SSIS team want to know this so that they can fix it in the future SPs?
CPackage::LoadFromXML Failure
My current package and all backups of previous packages are giving me this error. Anyone got any ideas? I have 1 GB free disk space. Last OS update a month ago. Ready to reinstall SSIS.
Error 1 Error loading SanDiegoRiverside.dtsx: The connection type "FLATFILE" specified for connection manager "ExtractDenormalizedErrors" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
Error 2 Error loading SanDiegoRiverside.dtsx: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ExtractDenormalizedErrors</DTS:Property><DTS:Property DTS:Name="DTSID">{92AFE6E1-8EA4-4CE5-BDA1-" from node "DTS:ConnectionManager". c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
Error 3 Error loading 'SanDiegoRiverside.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=179671&SiteID=1
and the KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817|||
FYI: This was caused by Flash8 Player Installer.
Laurence
|||We got the same in production tonight, the package have been running fine for 209 nights and have never been changed. Rerunning the package works fine, I can not reproduce the error. We are running Win2003 X64 Ent. + SQL2K5 X64 Ent SP1. SSIS and RDBMS runs on the same box.
I'm not sure I would like re-register any dll's until I know the cause. Any thoughts?
Thanks in advance!
Niklas
Started: 01:13:52 Error: 2007-03-22 01:13:52.67
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">NAV_det</DTS:Property><DTS:Property DTS:Name="DTSID">{C7269043-F2DD-4AE4-A831-C4E5D0632016}</DTS" from node "DTS:ConnectionManager".
End Error
Could not load package "Import_File_To_Table.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source:
Started: 01:13:52
Finished: 01:13:52
Elapsed: 0.188 seconds
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
I am running into the same issue as LKHall ... I am confused... How come does "This was caused by Flash8 Player Installer" FIX the issue. It does not make sense to me.
Any one knows what is the root cause for this error ?
|||I have unintalled the Flash driver and reboot my PC and I am still running into the package loading failure. Help....|||
The issue seems to be related to DLLs not being registered correctly. When you install new software (the Flash Player, for example) it may register an older or newer version of a DLL, which can can break other applications. Uninstalling may not fix the problem, as that usually does not reverse the changes to the registry.
Instead you may need to re-install the application that is failing, or re-register the DLLs indicated in the articles above.
|||Thanks John.... Can you please be more specific on what applications (or what DLLs) i need to re-install ? Do you suggest that I need to re-install SQL 2005 again?
I have used the link above to check registry and did not see any wrong registry.
|||Yes, reinstalling SQL 2005 is one way to make sure eveything is set up correctly.|||Reinstalling SQL 2005 is a good suggestion but any other better ideas ? I have SQL 2005 SP2 installed and reinstalling the entire software is not quite easy....
Plus, I really want to know what is the root cause behind this kind of weird behavior? Doesn't MSFT SSIS team want to know this so that they can fix it in the future SPs?
CPackage::LoadFromXML Failure
My current package and all backups of previous packages are giving me this error. Anyone got any ideas? I have 1 GB free disk space. Last OS update a month ago. Ready to reinstall SSIS.
Error 1 Error loading SanDiegoRiverside.dtsx: The connection type "FLATFILE" specified for connection manager "ExtractDenormalizedErrors" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
Error 2 Error loading SanDiegoRiverside.dtsx: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ExtractDenormalizedErrors</DTS:Property><DTS:Property DTS:Name="DTSID">{92AFE6E1-8EA4-4CE5-BDA1-" from node "DTS:ConnectionManager". c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
Error 3 Error loading 'SanDiegoRiverside.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=179671&SiteID=1
and the KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817|||
FYI: This was caused by Flash8 Player Installer.
Laurence
|||We got the same in production tonight, the package have been running fine for 209 nights and have never been changed. Rerunning the package works fine, I can not reproduce the error. We are running Win2003 X64 Ent. + SQL2K5 X64 Ent SP1. SSIS and RDBMS runs on the same box.
I'm not sure I would like re-register any dll's until I know the cause. Any thoughts?
Thanks in advance!
Niklas
Started: 01:13:52 Error: 2007-03-22 01:13:52.67
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">NAV_det</DTS:Property><DTS:Property DTS:Name="DTSID">{C7269043-F2DD-4AE4-A831-C4E5D0632016}</DTS" from node "DTS:ConnectionManager".
End Error
Could not load package "Import_File_To_Table.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source:
Started: 01:13:52
Finished: 01:13:52
Elapsed: 0.188 seconds
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
I am running into the same issue as LKHall ... I am confused... How come does "This was caused by Flash8 Player Installer" FIX the issue. It does not make sense to me.
Any one knows what is the root cause for this error ?
|||I have unintalled the Flash driver and reboot my PC and I am still running into the package loading failure. Help....|||
The issue seems to be related to DLLs not being registered correctly. When you install new software (the Flash Player, for example) it may register an older or newer version of a DLL, which can can break other applications. Uninstalling may not fix the problem, as that usually does not reverse the changes to the registry.
Instead you may need to re-install the application that is failing, or re-register the DLLs indicated in the articles above.
|||Thanks John.... Can you please be more specific on what applications (or what DLLs) i need to re-install ? Do you suggest that I need to re-install SQL 2005 again?
I have used the link above to check registry and did not see any wrong registry.
|||Yes, reinstalling SQL 2005 is one way to make sure eveything is set up correctly.|||Reinstalling SQL 2005 is a good suggestion but any other better ideas ? I have SQL 2005 SP2 installed and reinstalling the entire software is not quite easy....
Plus, I really want to know what is the root cause behind this kind of weird behavior? Doesn't MSFT SSIS team want to know this so that they can fix it in the future SPs?
Tuesday, February 14, 2012
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 w
ends, 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.
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 w
ends, 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 w
ends, 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 w
ends, 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.
Subscribe to:
Posts (Atom)