looking for patterns in data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

looking for patterns in data

Author

Topic

ttribe
Starting Member

6 Posts

Posted - 2010-08-16 : 16:17:06

I have a table that has a date field, and then fields showing various statistics about events that occurred on that day (all numeric). Think stock charts with an open, high, low, close, as well as additional fields.

I want to take data from the past five days and compare it to any other set of consecutive five days in the table. I've got the self join part down, but i'm struggling to construct something that will say compare the first day, and then if it is a match compare the next day, etc.

Any ideas/help would be much appreciated.

Thanks.

ttribe
Starting Member

6 Posts

Posted - 2010-08-16 : 17:57:14

maybe some sample data will help clarify my question:

create table stockprice (
DATE DATETIME,
CLOSE numeric (8,4));

insert into stockprice values ('8/13/2010', 100.3);
insert into stockprice values ('8/12/2010', 99.8);
insert into stockprice values ('8/11/2010', 101.1);
insert into stockprice values ('8/10/2010', 98);
insert into stockprice values ('8/9/2010', 98.5);
insert into stockprice values ('8/6/2010', 100);
insert into stockprice values ('6/8/2004', 100.3);
insert into stockprice values ('6/7/2004', 99.8);

etc. etc. going back 10+ years.

The first question, which i know how to answer is what other dates are there in the data set where the close is the same as is was on 8/13/2010?

I get this using the following:

select
*
from
stockprice x, stockprice y
where
x.date >= '8/13/2010' and x.close = y.close

Assuming they occur in the database, this query will find several instances where the close was 100.3, the same as it was on 8/13. In this example, it will returnn '6/8/2004'.

So my question is, how do i take that one step further and compare the previous dates, i.e., 8/12 to 6/7 to find the next result?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-17 : 14:26:46

so which date and|or close are you trying to get?

can you write also desired output for clarification.

thank you

ttribe
Starting Member

6 Posts

Posted - 2010-08-17 : 17:36:27

I realize the example isn't the best, but it's partially due to the inability to show lots of data points in the example. In my table I have thousands of close values going back years. I want to find a simple way to look at the last five days, and compare it to the previous close value to determine if there was ever a point in time where the values were the same. So if my last five days were:

8/13/2010' 100.3
'8/12/2010' 99.8
'8/11/2010', 101.1
'8/10/2010', 98
'8/9/2010', 98.5

I want to find consecutive dates, if any, where the close values were also:

100.3
99.8
101.1
98
98.5

The point though is to write it such that i can run it on a periodic basis to check for matches.

It's simple enough to find a match for any particular day, but what I'm struggling with is how to write it such that if I get a match on the close value of 100.3, to have the query then check to see if the previous day was 99.8, and then if that matches, the day previous to that was 101.1, etc.

I'm not sure if it makes it easier to pull the last five days into a view, for example, but it seems like that's the easy part, and it's looping through the multiple sets of conditions that I can't figure out.

Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-18 : 04:02:38

Is this what you want ?


-- sample table & data
DECLARE	@sample	TABLE
(
	datecol	datetime,
	val	int
)

INSERT INTO @sample
SELECT	'2010-07-05',	1	UNION ALL
SELECT	'2010-07-04',	2	UNION ALL
SELECT	'2010-07-03',	3	UNION ALL
SELECT	'2010-07-02',	4	UNION ALL
SELECT	'2010-07-01',	5	UNION ALL
SELECT	'2010-06-30',	6	UNION ALL
SELECT	'2010-06-03',	1	UNION ALL
SELECT	'2010-06-02',	2	UNION ALL
SELECT	'2010-06-01',	3	UNION ALL
SELECT	'2010-02-20',	1	UNION ALL
SELECT	'2010-02-19',	2	UNION ALL
SELECT	'2010-02-18',	3	UNION ALL
SELECT	'2010-02-17',	4	UNION ALL
SELECT	'2010-02-16',	5	UNION ALL
SELECT	'2010-02-15',	6	UNION ALL
SELECT	'2010-01-10',	1	UNION ALL
SELECT	'2010-01-09',	2	UNION ALL
SELECT	'2009-07-30',	1	UNION ALL
SELECT	'2009-07-29',	2	UNION ALL
SELECT	'2009-07-28',	3	UNION ALL
SELECT	'2009-07-27',	4	UNION ALL
SELECT	'2009-07-26',	5

-- Query
DECLARE	@start_date	datetime,
	@no_of_dates	int

SELECT	@start_date	= '20100705',
	@no_of_dates	= 5

; WITH 
source
AS
(
	SELECT	*, row_no = row_number() OVER (ORDER BY	datecol	DESC)
	FROM	@sample
	WHERE	datecol		<= @start_date
	AND	datecol		>  DATEADD(DAY,	-@no_of_dates, @start_date)
),
match
AS
(
	SELECT	datecol, match_no = row_number() OVER (ORDER BY	datecol)
	FROM	@sample	s
		CROSS APPLY
		(
			SELECT	cnt = COUNT(*)
			FROM	source s
				INNER JOIN
				(
					SELECT	datecol, val, row_no = row_number() OVER (order	BY datecol DESC)
					FROM	@sample	x
					WHERE	x.datecol	<= s.datecol
					AND	x.datecol	>  DATEADD(DAY,	-@no_of_dates, s.datecol)
				) m	ON	s.row_no	= m.row_no
					AND	s.val		= m.val
		) m
	WHERE	s.datecol	< DATEADD(DAY, -@no_of_dates, @start_date)
	AND	m.cnt		= @no_of_dates
)
SELECT	m.match_no, s.datecol, s.val
FROM	match m
	INNER JOIN @sample s	ON	s.datecol	<= m.datecol
				AND	s.datecol	> DATEADD(DAY, -@no_of_dates, m.datecol)

/*
match_no   datecol     val         
---------- ----------- ----------- 
1          2009-07-30  1
1          2009-07-29  2
1          2009-07-28  3
1          2009-07-27  4
1          2009-07-26  5
2          2010-02-20  1
2          2010-02-19  2
2          2010-02-18  3
2          2010-02-17  4
2          2010-02-16  5

(10 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

ttribe
Starting Member

6 Posts

Posted - 2010-08-18 : 13:34:34

Exactly what i needed. Thank you. Now i need to see if i can understand what you did so i can modify it slightly to adjust for what i need. But that's my issue, not yours.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources