Блог

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

 SELECT Department
       ,Status
       ,count(Department) AS "Number in status" 
       ,convert(VARCHAR, getdate(), 103) AS "Date report ran"
FROM [Server name].[xxx].[Database name]
WHERE STATUS NOT IN (
              'Closed'
              ,'Cancelled'
              )
       AND Department IN (
              'Department name'
              )
GROUP BY Department,Status

ORDER BY CASE 
              WHEN STATUS = 'Open'
                     THEN 1
              WHEN STATUS = 'In Progress'
                     THEN 2
              WHEN STATUS = 'Authorised'
                     THEN 3
              WHEN STATUS = 'Awaiting Auth'
                     THEN 4
              WHEN STATUS = 'Awaiting Collection'
                     THEN 5
              WHEN STATUS = 'Awaiting Delivery'
                     THEN 6
              WHEN STATUS = 'Awaiting Development'
                     THEN 7
              WHEN STATUS = 'Awaiting Engineer'
                     THEN 8
              WHEN STATUS = 'Awaiting Invoice/Credit'
                     THEN 9
              WHEN STATUS = 'Awaiting Quote'
                     THEN 10
              WHEN STATUS = 'Cancelled'
                     THEN 11
              WHEN STATUS = 'Chase End User'
                     THEN 12
              WHEN STATUS = 'Final Chase End User'
                     THEN 13
              WHEN STATUS = 'Closed'
                     THEN 14
              WHEN STATUS = 'Future Requirements'
                     THEN 15
              WHEN STATUS = 'In Test'
                     THEN 16
              WHEN STATUS = 'Next Release'
                     THEN 17
                     WHEN STATUS = 'On Hold'
                     THEN 18
                     WHEN STATUS = 'With End-User'
                     THEN 22
                     WHEN STATUS = 'With IIT'
                     THEN 23
                     WHEN STATUS = 'Processing Via Demand Management'
                     THEN 27
              END;
  

Результаты приведены ниже, но они не включают все другие состояния, ‘ которые могут иметь значение 0:

  ----------------- ---------------------------------- ---- ------------ 
|                 |                                  |    |            |
 ----------------- ---------------------------------- ---- ------------ 
| Department name | Open                             | 92 | 29/03/2019 |
| Department name | In Progress                      |  9 | 29/03/2019 |
| Department name | Awaiting Development             |  4 | 29/03/2019 |
| Department name | Future Requirements              |  1 | 29/03/2019 |
| Department name | In Test                          |  7 | 29/03/2019 |
| Department name | On Hold                          | 15 | 29/03/2019 |
| Department name | With End-User                    | 28 | 29/03/2019 |
| Department name | With IIT                         |  2 | 29/03/2019 |
| Department name | Processing Via Demand Management |  2 | 29/03/2019 |
 ----------------- ---------------------------------- ---- ------------ 
  

Вам нужна таблица состояния, содержащая, по крайней мере, описание состояния и (возможно) порядок представления отчетов:

 CREATE TABLE Statuses (
    Status varchar(30) not null,
    ReportOrder int not null,
    ShowInReport bit not null,
    constraint PK_Statuses (Status)
)
  

И теперь вы можете написать свой запрос, используя LEFT JOIN из этой таблицы в вашу другую неназванную таблицу (поскольку ваш запрос в настоящее время утверждает, что запрашивает базу данных, а не таблицу в своем FROM предложении:

 SELECT Department
       ,s.Status
       ,count(Department) AS "Number in status" 
       ,convert(VARCHAR, getdate(), 103) AS "Date report ran"
FROM
    Statuses s
       left join
    unnamedTable t
       on
          s.Status = t.Status
WHERE s.ShowInReport = 1
       AND Department IN (
              'Department name'
              )
GROUP BY Department,s.Status
ORDER BY s.ReportOrder
  

Также может быть разумно иметь Departments таблицу, если вы все еще хотите, чтобы названия отделов в выходных данных для 0 строк использовались очень похоже на приведенные выше:

 ...
FROM
    Statuses s
       cross join
    Departments d
       left join
    unnamedTable t
       on
          s.Status = t.Status and
          d.Department = t.Department
...
  

(У вас также должно быть значение FK между безымянной таблицей и Statuses , чтобы убедиться, что все используемые в ней значения статуса являются допустимыми)

Если ваша база данных хорошо спроектирована, ваш статус и подразделение отображаются в отдельной таблице, которую вы объединили со своей записью. Итак, вместо выполнения естественного объединения вы хотите выполнить :

Статус отдела X -> Вы получаете все возможные комбинации отдела и статуса и выполняете ЛЕВОЕ объединение в результирующей таблице с вашей записью. Затем вы группируете по подразделению и статусу, как вы делаете, и выполняете подсчет.