Statistical Concept - 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 2008 Forums

Transact-SQL (2008)

Statistical Concept

Author

Topic

frenchyc
Starting Member

1 Post

Posted - 2010-02-03 : 18:51:51

I'm having trouble implimenting a concept that I don't even know the term for. Basically lets say i have a table of accounts, that have 12 data values and dates (1 per month of the year). I want to know which accounts have at lest 5 consecutive data values that fall within a certain range of some central value (Perhaps the average of all 5, or the first data value of the series, its not super important). Basically i'm searching the data for pockets of consistency in these accounts. I don't know how to write a query to do this, nor do i even know the term for what I am looking for. Any help would be greatly appreciated, even if its just the name of what I'm looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:25:50

so what all will be your inputs? can you illustrate that with data sample?

divya.ce
Starting Member

16 Posts

Posted - 2010-02-08 : 07:05:10

Hi @frenchyc
I did not get your question clearly. I can answer as to what i have understood.
declare @tbl table(id int identity(1,1),accno int, data int,date datetime)

insert into @tbl(accno,data,date)
select '1','1000','02/08/2010' union all
select '1','2000','01/08/2010' union all
select '1','3000','12/08/2009' union all
select '1','3300','11/08/2009' union all
select '1','3600','10/08/2009' union all
--select '1','1200','02/08/2010' union all
select '1','3800','9/08/2009' union all
select '1','3900','8/08/2009' union all
select '2','1000','02/08/2010' union all
select '2','2000','01/08/2010' union all
select '2','3000','12/08/2009' union all
select '2','3300','11/08/2009' union all
select '2','5600','10/08/2009' union all
select '2','3400','9/08/2009' union all
select '2','3560','8/08/2009'

select distinct T.accno,r
from
(
select T1.accno,
T1.data as t1data,
T2.data as t2data ,
T1.id as t1id,
T2.id as t2id,
row_number() over(partition by T1.accno,T1.id order by (select 1)) as rownum,
T2.id - row_number() over(partition by T1.accno,T1.id order by (select 1)) as r
from @tbl T1
cross join @tbl T2
where T1.accno = T2.accno
and T2.data between T1.data - 800 and T1.data +800
)T
group by accno,t1id,r having count(*) =5

Let me know whether u have found it useful or not

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources