returning only first record of partition that exceeds threshold

Hi Gurus
Need some help. I have a account table with the following columns. I want
to
only see the first date the account threshold is >= 105 or 120 under
theOCLWatch column. However, if I have consecutive dates that exceed my
threshold I only want to see the first time it exceeds 105 or 120. I want t
o
evaluate every record in the account table and only return the first time my
account exceeds the treshold. My account could exceed the threshold in day
1
then go under the threshold in day 2, then go back over the threshold in day
3. In this case I ony want to see Day 1 and Day 3. I am thinking I need a
varaible and cursor. I need to partition this by account because my table ma
y
contain multiple accounts. Any help would be greatly appreciated . Example
Below(USING SQL 2005)
SELECT
Date
,AccountId
,CurrentBalance
,CreditBalance
,CurrentBalance/CreditLimt*100 as OCLWatch
FROM Account
ORDER BY Date ASC
Sample Results:
Date Account CurrentBalance CreditLimit OCLWatch
12/1/05 123 208 200 104
12/2/05 123 209 200 104
12/3/05 123 211 200 105 Want to
see this only
12/4/05 123 211 200 105
12/5/05 123 211 200 105
12/6/05 123 150 200 75
12/7/05 123 225 200 125 Want to
see this only
12/8/05 123 225 200 125
12/9/05 123 209 200 104
12/10/05 123 211 200 105 Want to
see this only
12/11/05 123 225 200 125 Want to
see this too!!Can you give the create script and the inserts for sample data. It will be
easier for us to give the query :)
Also tell us if there will be one record per day?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Randy" wrote:

> Hi Gurus
> Need some help. I have a account table with the following columns. I wan
t to
> only see the first date the account threshold is >= 105 or 120 under
> theOCLWatch column. However, if I have consecutive dates that exceed my
> threshold I only want to see the first time it exceeds 105 or 120. I want
to
> evaluate every record in the account table and only return the first time
my
> account exceeds the treshold. My account could exceed the threshold in da
y 1
> then go under the threshold in day 2, then go back over the threshold in d
ay
> 3. In this case I ony want to see Day 1 and Day 3. I am thinking I need
a
> varaible and cursor. I need to partition this by account because my table
may
> contain multiple accounts. Any help would be greatly appreciated . Examp
le
> Below(USING SQL 2005)
>
> SELECT
> Date
> ,AccountId
> ,CurrentBalance
> ,CreditBalance
> ,CurrentBalance/CreditLimt*100 as OCLWatch
> FROM Account
> ORDER BY Date ASC
> Sample Results:
> Date Account CurrentBalance CreditLimit OCLWatch
> 12/1/05 123 208 200 104
> 12/2/05 123 209 200 104
> 12/3/05 123 211 200 105 Want to
> see this only
> 12/4/05 123 211 200 105
> 12/5/05 123 211 200 105
> 12/6/05 123 150 200 75
> 12/7/05 123 225 200 125 Want to
> see this only
> 12/8/05 123 225 200 125
> 12/9/05 123 209 200 104
> 12/10/05 123 211 200 105 Want to
> see this only
> 12/11/05 123 225 200 125 Want to
> see this too!!
>
>
>
>|||Randy,
I am not sure i understand your problem, but try this:
select '20051201' tr_date, 1 account, 104 OCLWatch
into #t
union all
select '20051202' tr_date, 1 account, 104 OCLWatch
union all
select '20051203' tr_date, 1 account, 105 OCLWatch
union all
select '20051204' tr_date, 1 account, 105 OCLWatch
union all
select '20051206' tr_date, 1 account, 104 OCLWatch
union all
select '20051207' tr_date, 1 account, 106 OCLWatch
union all
select '20051208' tr_date, 1 account, 126 OCLWatch
union all
select '20051209' tr_date, 1 account, 127 OCLWatch
go
select * from #t
where
(case when OCLWatch <105 then 0
when OCLWatch > 119 then 2
else 1 end) >
(select top 1
case when t1.OCLWatch <105 then 0
when t1.OCLWatch > 119 then 2
else 1 end
from #t t1
where t1.account=#t.account and t1.tr_date<#t.tr_date
order by t1.tr_date desc
)
tr_date account OCLWatch
-- -- --
20051203 1 105
20051207 1 106
20051208 1 126
(3 row(s) affected)