Блог

У меня есть таблица, и она имеет следующую структуру:

    DEVICE_ID |    DATE    |  STATUS
------------------------------------------
       1     | 2021/01/05 | accepted
       2     | 2021/01/23 | success
       3     | 2021/02/07 | success
       4     | 2021/03/11 | accepted
       5     | 2021/03/20 | unsuccess
       6     | 2021/03/26 | success
 

Я хочу рассчитать количество записей в 2021 году по статусу и GROUP BY названию месяца, как это :

      MONTH    | ACCEPTED | SUCCESS | UNSUCCESS 
------------------------------------------------
    January   |    1     |    1    |     0
    February  |    0     |    1    |     0
    March     |    1     |    1    |     1
    April     |    0     |    0    |     0
    May       |    0     |    0    |     0
    June      |    0     |    0    |     0
    July      |    0     |    0    |     0
    August    |    0     |    0    |     0
    September |    0     |    0    |     0
    October   |    0     |    0    |     0
    November  |    0     |    0    |     0
    December  |    0     |    0    |     0
 

Пожалуйста, помогите мне решить эту проблему

Объяснение — поскольку вам нужен полный список месяцев, вам нужно, чтобы все 12 месяцев были где-то в данных. Затем вы хотите, чтобы настраиваемые столбцы состояния отображались так, как вы просили.

Вам следует хотя бы в следующий раз рассказать нам, что вы пробовали. Это помогает нам понять, как вы думаете об этом и как мы можем помочь вам преодолеть любые препятствия, с которыми вы столкнулись.

 IF OBJECT_ID('TempDb..#tmp') IS NOT NULL DROP TABLE #tmp;
IF OBJECT_ID('TempDb..#tmp2') IS NOT NULL DROP TABLE #tmp2;

CREATE TABLE #tmp
    (
        Device_ID INT
        , Date VARCHAR(12)
        , Status VARCHAR(15)
    )
;

CREATE TABLE #tmp2
    (
        MOnthName VARCHAR(25)
    )
;

INSERT INTO #tmp2 
    (MonthName) 
VALUES
    ('January'),
    ('February'),
    ('March'),
    ('April'),
    ('May'),
    ('June'),
    ('July'),
    ('August'),
    ('September'),
    ('October'),
    ('November'),
    ('December')
;

INSERT INTO #tmp
    (
        Device_ID
        , Date
        , Status
    )
VALUES
    (1,'2021/01/05','accepted'),
    (2,'2021/01/23','success'),
    (3,'2021/02/07','success'),
    (4,'2021/03/11','accepted'),
    (5,'2021/03/20','unsuccess'),
    (6,'2021/03/26','success')
;

SELECT 
    MOnthName
    , success
    , accepted
    , unsuccess
FROM
    (
        SELECT
            tt.MonthName
            , Status
        FROM 
            #tmp2 tt
            LEFT JOIN #tmp t ON tt.MOnthName = DATENAME(month, CAST(Date AS DATE))
        GROUP BY
            tt.MonthName 
            , Status
    ) AS SourceTable
PIVOT
    (
        COUNT(Status) FOR Status IN ([accepted], [success], [unsuccess])
    ) AS PivotTable
ORDER BY
    CASE 
        WHEN MonthName ='January' THEN 1
        WHEN MonthName ='February' THEN 2
        WHEN MonthName ='March' THEN 3
        WHEN MonthName ='April' THEN 4
        WHEN MonthName ='May' THEN 5
        WHEN MonthName ='June' THEN 6
        WHEN MonthName ='July' THEN 7
        WHEN MonthName ='August' THEN 8
        WHEN MonthName ='September' THEN 9
        WHEN MonthName ='October' THEN 10
        WHEN MonthName ='November' THEN 11
        WHEN MonthName ='December' THEN 12
END
 

  create table yourtable(DEVICE_ID int, DATE date, STATUS varchar(50));
 
 insert into yourtable values(1, '2021/01/05' , 'accepted');
 insert into yourtable values(2, '2021/01/23' , 'success');
 insert into yourtable values(3, '2021/02/07' , 'success');
 insert into yourtable values(4, '2021/03/11' , 'accepted');
 insert into yourtable values(5, '2021/03/20' , 'unsuccess');
 insert into yourtable values(6, '2021/03/26' , 'success');
 

Запрос:

  ;WITH months(MonthNumber) AS
 (
     SELECT 0
     UNION ALL
     SELECT MonthNumber 1 
     FROM months
     WHERE MonthNumber < 11
 )
 SELECT DATENAME(MONTH,DATEADD(MONTH,MonthNumber,'01-01-2021')) AS [month],
 coalesce(sum(case when status='ACCEPTED' then 1 end),0) ACCEPTED,
 coalesce(sum(case when status='SUCCESS' then 1 end),0) SUCCESS,
 coalesce(sum(case when status='UNSUCCESS' then 1 end),0) UNSUCCESS
 
 FROM months m left join yourtable y
 on m.monthnumber=month(y.[date])-1
 group by monthnumber
 

Выход:

бд<>скрипка <>здесь