SQL for this query ?

[dartmus]

I'm not writing it, you are. If by cursorless u mean a list of all the stats then add print statements to send the list to an output window. If u want the list in a column on a price chart send the stats to text objects.

 

[syswizard]

-- LET'S SAY WE NEED TO LIST THROUGH ALL LAST NAMES AND PERFORM SOME ACTION -- ON LAST NAMES BEGINNING WITH THE LETTER S SELECT StaffId, LastName FROM Staff WHERE LastName LIKE 'S%'
-- THIS RETURNS A SET AS SUCH -- StaffId LastName -- ======= ======== -- 11 Sherwood -- 35 Schmidt -- 41 Sauvé -- 52 Sookhoo -- 61 Stojkovich -- 66 Sander
-- ONE MIGHT LOAD THIS DATASET INTO A CURSOR BY DOING THE FOLLOWING DECLARE @StaffId int, @LastName nvarchar(50)
DECLARE RecSet CURSOR FOR SELECT StaffId, LastName FROM Staff WHERE LastName LIKE 'S%'
OPEN RecSet FETCH NEXT FROM RecSet INTO @StaffId, @LastName
WHILE (@@FETCH_STATUS = 0)
BEGIN SELECT @LastName AS LastName FETCH NEXT FROM RecSet INTO @StaffId, @LastName
END
CLOSE RecSet DEALLOCATE RecSet

-- CURSORS ARE VERY HEAVY AND NOT PERFORMANT, BECAUSE THEY TAKE UP MEMORY AND CREATE LOCKS -- READ MORE: http://stackoverflow.com/a/58154/76302

-- HERE IS HOW TO DO THE SAME THING WITHOUT A CURSOR
DECLARE @Temp TABLE ( Id int identity(1,1), StaffId int, LastName nvarchar(50) )
INSERT INTO @Temp SELECT StaffId, LastName FROM Staff WHERE LastName LIKE 'S%'
DECLARE @xMax INT
DECLARE @x INT = 1
SELECT @xMax = MAX(Id) FROM @Temp
WHILE @x < @xMax
BEGIN
SELECT LastName FROM @Temp WHERE Id = @x SET @x = @x + 1
END

Above shows both approaches - cursor and cursorless.

Best to insert the final resultset into a table. Interestingly, that table then needs to be read so that we don't recompute the same data as we move from drop to drop. In other words, only read data since the last drop.

 

[Surgo]

This is still not a well-defined problem. You specified that a 10%+ drop from a given date represented such a gap to be recorded, but what if the previous date was higher? That would also be a 10% drop and would result in a gap of the same number of days + 1. Basically, you need to define how a "drop" both ends and begins.

If you can actually completely define the problem, doing it in SQL is no more difficult than doing it in any other logic programming language.

 

[dartmus]

This is still not a well-defined problem. You specified that a 10%+ drop from a given date represented such a gap to be recorded, but what if the previous date was higher? That would also be a 10% drop and would result in a gap of the same number of days + 1. Basically, you need to define how a "drop" both ends and begins.

If you can actually completely define the problem, doing it in SQL is no more difficult than doing it in any other logic programming language.

I constructively agree. Constructively because this is the most obtuse specification I've ever seen. Please don't take this the wrong way syswizard. The facts are u sent us to a link that's primarily discussion unrelated to what u want. And what u want is a tiny almost hidden detail within that link.

 

[syswizard]

re: "Basically, you need to define how a 'drop' both ends and begins."
I understand that. I am still formulating the details.
My gut is telling me that some implementation of the HIGHEST(CLOSE,nn) and LOWEST(CLOSE,nn) functions with a sliding date window is the way to go.
Start with the earliest date in the dataset. Loop thru incrementing nn starting with 1 and ending with the count of the records in the dataset. Continually compute the difference between the highest and lowest...call it DIFF.
Once DIFF is -10% record the date and price....Point "A".
Then determine the date of the upward price retracement which matches the price of Point "A".
That indicates the a new low is "in". Determine the lowest price between Point "A" and Point "B".
Now record the date, "nn" and lowest price of that interval. That is the first data point....call it RETRACE(1).
Start the whole process over starting with the RETRACE(1) data point's date.
Continue to loop thru dataset beginning with offset "nn".

 

[Surgo]

Forget about the *how* to do it. Just focus on actually defining the problem completely. Then once you have it defined, implementation is straightforward. This is literally just picking data out of a database and doing some division.

 

[dartmus]

The specification on the chart said the number of days between a High and a Low. Or a Low and a High? But not both. It only tracked 1 direction. I believe what follows is how they determined direction changes.

If a pullback down from an extreme H exceeds 10% it terminates the H, and establishes a temporary Low. The temporary Low can continue lower but if it rallies 10% from the Lowest Low it terminates the LL and establishes a temporary H, (which is permitted to go higher, but must be terminated if it declines 10%).

 

[syswizard]

Then once you have it defined, implementation is straightforward. This is literally just picking data out of a database and doing some division.

I do have it defined....especially since I've provided the methodology.
So if it's that easy Dude, why don't you write the code ?

 

[syswizard]

The specification on the chart said the number of days between a High and a Low. Or a Low and a High? But not both.

Drat - a clever programmer can abstract this problem to work BOTH WAYS.
And that's what I intend to do.
This particular example is only the swing-low counts......the other would be the swing-high counts.
The real challenge here is doing BOTH WAYS in a single pass of the source data table.....whew, now that's looks very tough.

My interest in this is:
1) Do these stats reveal anything about market micro-structure and the next trend ?
2) Any value to moving this analysis to lower time-frames ?

 

[Surgo]

I do have it defined....especially since I've provided the methodology.
So if it's that easy Dude, why don't you write the code ?

We just spent two pages teasing the exact description out of you, and I'm still not sure you exactly have it.

If you want the code, you are welcome to pay my consulting rates.