CODEWITHSUNDEEP
c#sql-servert-sqlanalyticsdata-analysis
jason
jason

Reputation: 767

Analytical TSQL

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?

Upvotes: 0

Views: 98

Answers (1)

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

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

Upvotes: 2

Related Questions