Блог

Я работаю над Microsoft SQL Server 2017. Я пытаюсь заполнить недостающие месяцы в запросе данных.

Запрос:

 SELECT Concat(Datename(mm, temp1.InvoiceDate), '-', Datepart(yy, Getdate())) AS 'Date' , temp1.[Partner], Count(temp1.issues) AS 'Total' 
FROM   temp1 
GROUP  BY temp1.InvoiceDate, temp1.[partner] 
ORDER  BY Datepart(mm, temp1.InvoiceDate), temp1.[partner];
  

Не обращайте внимания на getdate, поскольку я всегда буду работать с фактическими данными за год.

И это показывает мне этот результат:

 Date        Partner             Total
April-2020  Enterprise1, S.A.   1
May-2020    Enterprise2, S.A.   1
July-2020   Enterprise2, S.A.   2
  

Это правильно, но мне также нужны недостающие месяцы до фактического (фактический месяц getdate), где нет данных.

Что-то вроде:

 Date           Partner           Total
January-2020   N/A                0
February-2020  N/A                0
March-2020     N/A                0
April-2020     Enterprise1, S.A.  1
May-2020       Enterprise2, S.A.  1
June-2020      N/A                0
July-2020      Enterprise2, S.A.  2
August-2020    N/A                0
September-2020 N/A                0
  

Кроме того, я не хочу, чтобы отображались месяцы из фактического.

PS: я не могу использовать код T-SQL (объявить и т.д.) В своей среде разработки.

Заранее спасибо.

Я бы использовал рекурсивный CTE для получения дат за год:

 with dates as (
      select datefromparts(year(getdate()), 1, 1) as yyyymm
      union all
      select dateadd(month, 1, yyyymm)
      from dates
      where yyyymm < getdate()
     )
select d.yyyymm, t1.partner, count(t1.issues)
from dates d left join
     temp1 t1 
     ON t1.InvoiceDate >= d.yyyymm AND
        t1.InvoiceDate < dateadd(month, 1, d.yyyymm)
group by d.yyyymm, t1.partner
order by d.yyyymm, t1.partner;
  

Обратите внимание на некоторые изменения:

• Это возвращает дату. Он не форматирует его так, как у вас. Вы можете отформатировать его так, как вам нравится.
• Он возвращает 0 вместо «N / A». 0 кажется более подходящим.

На самом деле вышеприведенное будет работать только для одного партнера (так что это работает для примера в вопросе). Я подозреваю, что вам нужны все месяцы для всех партнеров. Если это так, используйте cross join для генерации строк, а затем left join и group by :

 with dates as (
      select datefromparts(year(getdate()), 1, 1) as yyyymm
      union all
      select dateadd(month, 1, yyyymm)
      from dates
      where yyyymm < getdate()
     )
select d.yyyymm, p.partner, count(t1.issues)
from dates d cross join
     (select distinct partner from temp1) p left join
     temp1 t1 
     on t1.partner = p.partner and
        t1.InvoiceDate >= d.yyyymm and
        t1.InvoiceDate < dateadd(month, 1, d.yyyymm)
group by d.yyyymm, p.partner
order by d.yyyymm, p.partner;
  

Альтернативой рекурсивному CTE может быть запрос с таблицей чисел (иногда называемой ‘tally’). Есть только 12 месяцев, поэтому их можно просто перечислить в коде, и тогда рекурсия не требуется. Кроме того, для ввода лет я добавил переменную, которая принимает список, разделенный запятыми. Пример в этом случае имеет 2 года, 2020 и 2021

Что-то вроде этого

 declare
  @report_yrs  varchar(100)='2020,2021';

with ent_cte(InvoiceDate, [Partner], [issues]) as
    (select '2020-03-01', 'Enterprise1, S.A.', 'x'
     union all
     select '2020-04-01', 'Enterprise1, S.A.', 'x'
     union all
     select '2020-05-01', 'Enterprise1, S.A.', 'x'
     union all
     select '2020-12-01', 'Enterprise1, S.A.', 'x')
SELECT Concat(Datename(mm, dfp.dfp), '-', Datepart(yy, dfp.dfp)) AS [Date],
       isnull(t.[Partner], 'N/A') [Partner], Count(t.issues) AS [Total] 
FROM string_split(@report_yrs, ',') sp
     cross join
     (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(mo)
     cross apply
     (select datefromparts(cast(sp.[value] as int), v.mo, 1) dfp) dfp
     left join 
     ent_cte t on dfp.dfp=t.InvoiceDate
GROUP  BY dfp.dfp, t.[partner] 
ORDER  BY dfp.dfp, t.[partner];
  

Вывод

 Date            Partner Total
January-2020    N/A     0
February-2020   N/A     0
March-2020      Enterprise1, S.A.   1
April-2020      Enterprise1, S.A.   1
May-2020        Enterprise1, S.A.   1
June-2020       N/A     0
July-2020       N/A     0
August-2020     N/A     0
September-2020  N/A     0
October-2020    N/A     0
November-2020   N/A     0
December-2020   Enterprise1, S.A.   1
January-2021    N/A     0
February-2021   N/A     0
March-2021      N/A     0
April-2021      N/A     0
May-2021        N/A     0
June-2021       N/A     0
July-2021       N/A     0
August-2021     N/A     0
September-2021  N/A     0
October-2021    N/A     0
November-2021   N/A     0
December-2021   N/A     0
  

Вы найдете в базе данных только те данные, которые вы в нее ввели. В таком случае рекомендуется добавить некоторую таблицу даты и времени, такую как таблица календаря (одна строка в день) и некоторые другие таблицы, такие как месяц, дни, недели…

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

В вашем случае это может быть :

 CREATE TABLE T_MONTH 
(
MONTH_NUM   TINYINT PRIMARY KEY,
MONTH_NAME  VARCHAR(16) NOT NULL UNIQUE
);
DECLARE @I INT = 1, @D DATE = '2000-01-01'
WHILE @I <= 12
BEGIN
   INSERT INTO T_MONTH
   SELECT @I, DATENAME(month, DATEADD(month, @I - 1, @D));
   SET @I = @I   1;
END
  

И запрос что-то вроде :

 SELECT Concat(MONTH_NAME, '-', Datepart(yy, Getdate())) AS 'Date' , 
       COALESCE(temp1.[Partner], 'N/A'), 
       COALESCE(Count(temp1.issues), 0) AS 'Total' 
FROM   temp1 
       RIGHT OUTER JOIN T_MONTH ON MONTH_NAME = Datename(mm, temp1.InvoiceDate)
GROUP  BY MONTH_NAME, temp1.[partner] 
ORDER  BY MONTH_NAME, temp1.[partner];