I need to produce some SQL that will show me the trend (up or down tick) in some transacitons.
Consider this table with a PlayerId and a Score
PlayerId, Score, Date
1,10,3/13
1,11,3/14
1,12,3/15
If I pull data from 3/15 I have a score of 12 with an upward trend compared to the historical data.
I did something similar in Oracle 8i about 10 years ago using some of the analytical functions like rank, however it was 10 years ago....
The results would look similar to
PlayerId, Score, Date, Trend
1,12,3/15,UP
How can I do something similar with sql azure?
This SQL:
with data as (
select * from ( values
(1,11,cast('2013/03/12' as smalldatetime)),
(1,15,cast('2013/03/13' as smalldatetime)),
(1,11,cast('2013/03/14' as smalldatetime)),
(1,12,cast('2013/03/15' as smalldatetime))
) data(PlayerId,Score,[Date])
)
select
this.*,
Prev = isnull(prev.Score,0),
tick = case when this.Score > isnull(prev.Score,0) then 'Up' else 'Down' end
from data this
left join data prev
on prev.PlayerId = this.PlayerId
and prev.[Date] = this.[Date] - 1
returns this output:
PlayerId Score Date Prev tick
----------- ----------- ----------------------- ----------- ----
1 11 2013-03-12 00:00:00 0 Up
1 15 2013-03-13 00:00:00 11 Up
1 11 2013-03-14 00:00:00 15 Down
1 12 2013-03-15 00:00:00 11 Up