Блог

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

Вот несколько рабочий запрос:

 with Months as (
    select dateadd(month,datediff(month,0,getdate()) - n, 0) MonthDate
    from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n))
SELECT  
    m.MonthDate,
    C.Name,
    Count(*) as 'Count'
from 
    Months m 
    LEFT OUTER JOIN Incident I
        on (m.MonthDate = DATEADD(MONTH, DATEDIFF(MONTH,0,i.CreatedDateTime),0))
    Inner JOIN CI C on C.RecId = I.SystemName_Valid
WHERE   
    I.CreatedDateTime >= DATEADD(MONTH,-11,GETDATE()) 
GROUP BY    
    m.MonthDate, C.Name
  

Результаты показывают только даты, когда происходят системные сбои.

Я ожидаю, что результат будет выглядеть следующим образом

 MonthDate   Name     Count
1/1/2019    System1    0
1/1/2019    System2    0
1/1/2019    System3    0
1/1/2019    System4    0
2/1/2019    System1    0
2/1/2019    System2    0
2/1/2019    System3    0
2/1/2019    System4    1
3/1/2019    System1    1
3/1/2019    System2    0
3/1/2019    System3    0
3/1/2019    System4    0
4/1/2019    System1    0
4/1/2019    System2    0
4/1/2019    System3    0
4/1/2019    System4    0
  

Возвращаемый набор данных, хотя

 MonthDate   Name     Count
2/1/2019    System4    1
3/1/2019    System1    1
  

Попробуйте изменить свое FROM предложение на:

 from 
    Months m 
    CROSS JOIN dbo.CI -- you want to have a row per month per system
    LEFT OUTER JOIN dbo.Incident I -- this needs to be *outer* so that combos
                                   -- without a match are still included
      ON (m.MonthDate = DATEADD(MONTH, DATEDIFF(MONTH,0,i.CreatedDateTime),0))
      AND C.RecId = I.SystemName_Valid
      AND I.CreatedDateTime >= DATEADD(MONTH,-11,GETDATE()) -- this is redundant
  

Когда вы помещаете необязательную таблицу либо в предложение INNER / ON, либо в предложение WHERE, вы превращаете внешнее соединение (укажите мне все месяцы и включите любые строки инцидентов, когда мы найдем совпадающие) во внутреннее соединение (укажите мне только месяцы, в которых есть совпадающие инциденты).

Я бы также изменил это:

 on (m.MonthDate = DATEADD(MONTH, DATEDIFF(MONTH,0,i.CreatedDateTime),0))
  

К этому:

 ON i.CreatedDateTime >= m.MonthDate
AND i.CreatedDateTime < DATEADD(MONTH, 1, m.MonthDate)
  

Это позволит использовать существующий (или будущий) индекс, который имеет CreatedDateTime в ключе, который может быть или не быть более полезным в зависимости от объема данных в таблице инцидентов. Ваше текущее выражение принудительно проверит всю таблицу, поскольку ему придется оценивать выходные данные для каждой отдельной строки.

Используя то, что опубликовал Аарон, я изменил запрос, и я заставил его работать со следующим:

 with Months as (
    select dateadd(month,datediff(month,0,getdate()) - n, 0) MonthDate
    from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))dt(n))
SELECT  
    m.MonthDate,
    CI.Name,
    (Select 
        count(*) 
    from 
        Incident I 
    where 
        i.SystemOutageIssue = 1 and I.CreatedDateTime >= m.MonthDate and 
        I.CreatedDateTime < DateAdd(day,1,EOMONTH(m.MonthDate)) and
        I.SystemName = CI.Name) as 'Count'
from 
    Months m 
    Cross JOIN CI
GROUP BY    
    m.MonthDate, CI.Name
  

Спасибо Аарону за вашу помощь, я раньше не использовал перекрестное соединение, во всяком случае, не так.