CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Metaphor
Metaphor

Reputation: 6415

T-SQL: Months in Top 5 query

Base query is:

SELECT Top 5 
[Year], [Month], Title, Units, [Rank]
from aTable
WHERE [Year] = @Year and [Month] = @Month
order by [Rank] desc

What I would like to add, is a [Months in Top 5] column, that would show me a number of consecutive months each of the rows in Top 5 has been rank 5 or less.

Upvotes: 0

Views: 162

Answers (1)

JC Ford
JC Ford

Reputation: 7066

;with myTable as (
    select
        dateadd(yy,[Year]-1900,0) + dateadd(mm,[Month]-1,0) YearMonth,
        Title,
        Units,
        Rank
    from
        aTable
),
results as (

    select
        YearMonth,
        Title, 
        Units, 
        [Rank]
    from
        myTable
    where
        YearMonth = dateadd(m,datediff(m,0,getdate()),0) and
        Rank <= 5
    union all
    select
        b.YearMonth,
        b.Title, 
        b.Units, 
        0 Rank
    from
        myTable b
        join results a on
            b.Title = a.Title and 
            b.YearMonth = dateadd(m,-1,a.YearMonth) and 
            b.Rank <=5
)
select 
    max(YearMonth),
    Title,
    sum(Units) Units,
    max(Rank) Rank,
    count(*) MonthsInTop5
from
    results
group by
    Title
order by
    Rank Desc

Upvotes: 1

Related Questions