T-SQL Select Rows With 3 Consecutive int Columns Over Threshold

Question

I have a table where the records have score for each month. I would like to run a query to get the rows where the score is over the value 100 for 3 consecutive months.

I have had a look at the following example which works for consecutive rows above a threshold. However I am struggling to adapt this to columns instead of rows. I think I should be using 'OVER (PARTITION BY)' but I am not sure how.

I have started a sqlfiddle which I hope someone may be able to help me with.

Thank you.

Generally, for this sort of problem, you'd first `UNPIVOT` to transform the columns into rows and then write a sensible query. It's just a bad database design where multiple columns contain the same "type" of data (not just meaning having the same data type, but having the same meaning - such as being the count or sum of X, or a phone number, etc) — Damien_The_Unbeliever, Jun 18 '14 at 09:46

score 2 · Accepted Answer · answered Jun 18 '14 at 10:25

Unfortunately, when you store data in columns like this, queries are not as easy as they are with a more normalized format.

In your case, the brute force approach isn't that bad:

select *
from t
where (jan > 100 and feb > 100 and mar > 100) or
      (feb > 100 and mar > 100 and apr > 100) or
      (mar > 100 and apr > 100 and may > 100) or
      (apr > 100 and may > 100 and jun > 100) or
      (may > 100 and jun > 100 and jul > 100) or
      (jun > 100 and jul > 100 and aug > 100) or
      (jul > 100 and aug > 100 and sep > 100) or
      (aug > 100 and sep > 100 and oct > 100) or
      (sep > 100 and oct > 100 and nov > 100) or
      (oct > 100 and nov > 100 and decm > 100)

Thanks! I like this approach because it works and is simple enough for me to understand! — codefi, Jun 18 '14 at 10:33

score 1 · Answer 2 · answered Jun 18 '14 at 10:19

;with cte as
(
    SELECT 
        name, 
        case m 
            when 'jan' then 1
            when 'feb' then 2
            when 'mar' then 3
            when 'apr' then 4
            when 'may' then 5
            when 'jun' then 6
            when 'jul' then 7
            when 'aug' then 8
            when 'sep' then 9
            when 'oct' then 10
            when 'nov' then 11
            when 'decm' then 12 end as MonthNum,
        m, 
        score
    FROM 
       (SELECT name, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, decm
       FROM t) p
    UNPIVOT
       (score FOR m IN 
          (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, decm)
    )AS unpvt
)  
select 
  c.name, 
  c.m as 'Start month', 
  c.score as 'Score for start month',
  c_plus_1.score as 'Score for start month + 1',
  c_plus_2.score as 'Score for start month + 2'
from cte c 
    inner join cte c_plus_1 on c.name = c_plus_1.name and c.MonthNum + 1 = c_plus_1.MonthNum 
    inner join cte c_plus_2 on c.name = c_plus_2.name and c.MonthNum + 2 = c_plus_2.MonthNum 
where c.score > 100 and c_plus_1.score > 100 and c_plus_2.score > 100

Thank you for your reply. The query does exactly what I want it to but I am having difficulty understanding it at the moment. The other answer is easier for me to understand, for this reason I am selecting it as the answer. Thank you again. — codefi, Jun 18 '14 at 10:32

T-SQL Select Rows With 3 Consecutive int Columns Over Threshold

2 Answers2