Time series query question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Time series query question

Author

Topic

imaginethat
Starting Member

5 Posts

Posted - 2007-09-04 : 18:44:02

I'll preface by saying I'm a bit new to SQL programming...

I'm trying to do a select statement over some time series data where I need to look at previous row values in my select statement. Consider I have a table:

dayNum, date, sales, staff
1, 1/01/07, 35000, 10
2, 1/02/07, 25000, 8
3, 1/03/07, 15000, 5
4, 1/04/07, 18000, 4
5, 1/08/07, 23000, 8
6, 1/09/07, 43000, 12
7, 1/10/07, 38000, 11
...

An example query I'd like to be able to run would be to select all the dayNums, where sales were greater than 30000 AND where 3 days prior the staff was < 6 AND 2 days prior the sales were < 25000. In this case the results would be dayNum 6 and 7. I'll need to do more complicated queries but hoping that this basic example will give me the tools to complete the others.

thanks much in advance,

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-04 : 19:13:11

[code]
Declare @T Table (dayNum int, date datetime, sales int, staff int)
Insert into @T
Select 1, '1/01/07', 35000, 10 Union all
Select 2, '1/02/07', 25000, 8 Union all
Select 3, '1/03/07', 15000, 5 Union all
Select 4, '1/04/07', 18000, 4 Union all
Select 5, '1/08/07', 23000, 8 Union all
Select 6, '1/09/07', 43000, 12 Union all
Select 7, '1/10/07', 38000, 11

Select T.*
From @t T
WHERE T.sales > 30000
And ((Select Sales From @T T2 Where T2.dayNum = T.dayNum - 2) < 25000)
And ((Select Staff From @T T3 Where T3.dayNum = T.dayNum - 3) < 6)

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

imaginethat
Starting Member

5 Posts

Posted - 2007-09-04 : 20:18:29

Thanks much, figured a syntax like this had to exist. Much appreciated!

imaginethat
Starting Member

5 Posts

Posted - 2007-09-10 : 17:21:30

One additional follow-up question:

I'd like to do the following - clearly I'm still thinking like a C programmer:

For each day in the database, I would like to look over the past 30 days, find the max and min value for the sales data, then compute this percentage change for each of these max/min pairs.

For a table of 300 days of data I would expect back 270 results, each with a value between 0 and 1 that represented the delta (max-min)/max.

I've got the syntax for the individual pieces, but I'm getting tripped up trying to figure out how to express the rolling look back range.

Any help would be most appreciated,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 17:39:41

Do you have some proper sample data?
And expected output?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 17:50:40

[code]Declare @T Table (dayNum int, date datetime, sales int, staff int)
Insert into @T
Select 1, '1/01/07', 35000, 10 Union all
Select 2, '1/02/07', 25000, 8 Union all
Select 3, '1/03/07', 15000, 5 Union all
Select 4, '1/04/07', 18000, 4 Union all
Select 5, '1/08/07', 23000, 8 Union all
Select 6, '1/09/07', 43000, 12 Union all
Select 7, '1/10/07', 38000, 11

SELECT daynum, date, sales, staff,
case when ma is null or ma = 0 then null
else 1.0 * (ma - mi) / ma
end as delta
from (
SELECT p.daynum, p.date, p.sales, p.staff,
(select min(sales) from @t as a where a.date between p.date - 30 and p.date) as mi,
(select max(sales) from @t as a where a.date between p.date - 30 and p.date) as ma
FROM @t as p
) as d[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources