Блог

У меня есть ЗАПРОС:

  SELECT dept,csedept_name,January,February,March,April,May,June,July,August,September,October,November,December
FROM (SELECT CAST(employeedept AS INT) as dept,
        ROUND(AVG(case when rating1>0 THEN CAST(rating1 AS FLOAT) ELSE null END), 2) as q1,
        ROUND(AVG(case when rating2>0 THEN CAST(rating2 AS FLOAT) ELSE null END), 2) as q2,
        ROUND(AVG(case when rating3>0 THEN CAST(rating3 AS FLOAT) ELSE null END), 2) as q3,
        ROUND(AVG(case when rating4>0 THEN CAST(rating4 AS FLOAT) ELSE null END), 2) as q4,
        ROUND(AVG(case when rating5>0 THEN CAST(rating5 AS FLOAT) ELSE null END), 2) as q5,
count(*) as 'totalstars',month_cse= datename(month,execoffice_date),YEAR_cse =YEAR(execoffice_date)
        FROM CSEReduxResponses
        WHERE 
      execoffice_status = 1 
      and employeedept =17
        group by employeedept,month(execoffice_date),YEAR(execoffice_date),DATENAME(month,execoffice_date)

      ) 
    AS r JOIN CSEReduxDepts d
ON d.csedept_id = r.dept and d.csedept_id=17
PIVOT( SUM(q1)
    FOR [month_cse] IN (
        [January],[February],[March],[April],[May],[June],[July],[August], [September],[October],[November],[December]
        )) AS pvt 
  

Который получает среднее значение за каждый месяц в зависимости от отдела.
В приведенном выше запросе я получаю сумму для ‘q1’ и отображаю правильное число для этого месяца и отдела
, но оно отображается каждый месяц в 1 строке, и я могу показывать ‘q1’ только тогда, когда я хотел бы показать q1-q5.

Возможно, я использую длинный / неправильный способ сделать это, возможно, использование pivot — неправильный путь.

Есть ли способ, которым я смогу добавить q1-q5 и показать в соответствующем месяце?

Я сделал http://sqlfiddle.com /#!3/05390/1

Как насчет этого запроса:

SQL скрипка

Запрос 1:

 ;with
years as 
(select distinct year(execoffice_date) y from CSEReduxResponses),
months as 
(select 'January' as m, 1 n
union select 'February', 2 n
union select 'March', 3 n
union select 'April', 4 n
union select 'May', 5 n
union select 'June', 6 n
union select 'July', 7 n
union select 'August', 8 n
union select 'September', 9 n
union select 'October', 10 n
union select 'November', 11 n
union select 'December', 12 n),
cse as (SELECT CAST(employeedept AS INT) as dept,
        ROUND(AVG(case when rating1>0 THEN CAST(rating1 AS FLOAT) ELSE null END), 2) as q1,
        ROUND(AVG(case when rating2>0 THEN CAST(rating2 AS FLOAT) ELSE null END), 2) as q2,
        ROUND(AVG(case when rating3>0 THEN CAST(rating3 AS FLOAT) ELSE null END), 2) as q3,
        ROUND(AVG(case when rating4>0 THEN CAST(rating4 AS FLOAT) ELSE null END), 2) as q4,
        ROUND(AVG(case when rating5>0 THEN CAST(rating5 AS FLOAT) ELSE null END), 2) as q5,
        count(*) as 'totalstars', YEAR(execoffice_date) as YEAR_cse, month(execoffice_date) as m_cse
        FROM CSEReduxResponses
        group by employeedept, month(execoffice_date), YEAR(execoffice_date)

      ) 
SELECT csedept_id, d.csedept_name, years.y, months.m, q1, q2, q3, q4, q5, totalstars
FROM years cross join months
cross join CSEReduxDepts d
left join cse on
months.n = cse.m_cse and d.csedept_id = cse.dept
where d.csedept_id = 17
order by years.y, months.n, csedept_id, csedept_name
  

Результаты:

 | CSEDEPT_ID |  CSEDEPT_NAME |    Y |         M |     Q1 |     Q2 |     Q3 |     Q4 |     Q5 | TOTALSTARS |
|------------|---------------|------|-----------|--------|--------|--------|--------|--------|------------|
|         17 | department 17 | 2014 |   January | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |  February | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |     March | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |     April | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |       May |   2.83 |    4.5 |   3.67 |   1.75 |      1 |          6 |
|         17 | department 17 | 2014 |      June |   2.33 |      4 |   3.33 |      2 |      1 |          3 |
|         17 | department 17 | 2014 |      July | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |    August | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 | September | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |   October | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |  November | (null) | (null) | (null) | (null) | (null) |     (null) |
|         17 | department 17 | 2014 |  December | (null) | (null) | (null) | (null) | (null) |     (null) |
  

Вы даже можете прокомментировать последнее where d.csedept_id = 17 предложение, чтобы получить сведения для всех отделов. Я не думаю, что отображение q1, q2, q3, q4, q5, TOTALSTARS в отдельных столбцах за каждый месяц будет разумным. В этом случае у вас будет 60 столбцов