Блог

Я должен составить отчет в T-SQL из нескольких таблиц. Я могу объединить всю необходимую таблицу, но после я не знаю, как получить мою информацию.

Объяснение :

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

 Tbl_User (UserId, Username)
Tbl_Customer (CustomeriD, CustomerName)   
Tbl_DocA (DocId, CustomerID, DateCreate, DateAdd, UseriD)    
Tbl_DocB (DocId, CustomerID, DateCreate, DateAdd, UseriD)    
Tbl_DocC (DocId, CustomerID, DateCreate, DateAdd, UseriD)
  

Я пытаюсь получить отчет, подобный этому :

После того, как я смогу получить это, идея состоит в том, чтобы иметь фильтр с датой в SQL reporting.

Вы можете объединить все таблицы документа вместе и объединить пользователей и заказчиков в нем.

 SELECT Customer.CustomerID 
      ,Customer.CustomerName 
      ,COUNT(CASE WHEN DocType = 'A' THEN 1 END) AS doc_a_total
      ,COUNT(CASE WHEN DocType = 'B' THEN 1 END) AS doc_b_total
      ,COUNT(CASE WHEN DocType = 'C' THEN 1 END) AS doc_c_total
      ,COUNT(CASE WHEN DocType = 'A' AND user.username ='azerty' THEN 1 END) AS doc_a_made_by_azerty
      ,COUNT(CASE WHEN DocType = 'B' AND user.username ='azerty' THEN 1 END) AS doc_b_made_by_azerty
      ,COUNT(CASE WHEN DocType = 'C' AND user.username ='azerty' THEN 1 END) AS doc_c_made_by_azerty

FROM (
(SELECT 'A' AS DocType, * FROM Tbl_DocA) 
UNION ALL
(SELECT 'B' AS DocType, * FROM Tbl_DocB)
UNION ALL
(SELECT 'C' AS DocType, * FROM Tbl_DocC)
) AS docs 
JOIN Tbl_User AS user ON user.UserId = docs.UseriD
JOIN Tbl_Customer AS Customer ON Customer.CustomeriD = docs.CustomeriD
GROUP BY Customer.CustomerID , Customer.CustomerName
  

Вы можете использовать общие табличные выражения, чтобы получить количество для каждого типа отчета для каждого клиента с условной агрегацией для отчетов, созданных конкретным пользователем, и присоединить их к таблице customers.
Что-то подобное должно дать вам желаемые результаты:

 DECLARE @UserId int = 1; -- or whatever the id of the user you need
WITH CTEDocA AS
(
    SELECT CustomerID
         , COUNT(DocId) As NumberOfReports
         , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
    FROM Tbl_DocA 
    GROUP BY CustomerID
), CTEDocB AS
(
    SELECT CustomerID
         , COUNT(DocId) As NumberOfReports
         , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
    FROM Tbl_DocB
    GROUP BY CustomerID
), CTEDocC AS
(
    SELECT CustomerID
         , COUNT(DocId) As NumberOfReports
         , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
    FROM Tbl_DocC
    GROUP BY CustomerID
)

SELECT cust.CustomeriD
      ,cust.CustomerName
      ,ISNULL(a.NumberOfReports, 0) As NumberOfDocA
      ,ISNULL(a.NumberOfReportsByUserAzerty, 0) As NumberOfDocAByAzerty
      ,ISNULL(b.NumberOfReports, 0) As NumberOfDocB
      ,ISNULL(b.NumberOfReportsByUserAzerty, 0) As NumberOfDocBByAzerty
      ,ISNULL(c.NumberOfReports, 0) As NumberOfDocC
      ,ISNULL(c.NumberOfReportsByUserAzerty, 0) As NumberOfDocCByAzerty
FROM Tbl_Customer cust
LEFT JOIN CTEDocA As a
    ON cust.CustomeriD = a.CustomerID
LEFT JOIN CTEDocA As b
    ON cust.CustomeriD = b.CustomerID
LEFT JOIN CTEDocA As c
    ON cust.CustomeriD = c.CustomerID
  

Для фильтрации по дате вы можете добавить предложение where к каждому выражению в общей таблице.

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

Есть несколько способов сделать это. Необходима одна ключевая функция — считать конкретного пользователя отдельно от других. Это делается с помощью условной агрегации. Например.:

 select
  customerid,
  count(*),
  count(case when userid = <particular user ID here> then 1 end)
from tbl_doca
group by customerid;
  

Вот один из возможных запросов, использующих a cross join , чтобы получить пользователя, о котором идет речь, один раз и cross apply для получения чисел.

 select
  c.customerid,
  c.customername,
  doca.total  as doc_a_total,
  doca.az     as doc_a_by_azerty,
  docb.total  as doc_b_total,
  docb.az     as doc_b_by_azerty,
  docc.total  as doc_c_total,
  docc.az     as doc_c_by_azerty
from tbl_customer c
cross join
(
  select userid from tbl_user where username = 'Azerty'
) azerty
cross apply
(
  select
    count(*) as total,
    count(case when da.userid = azerty.userid then 1 end)n as az
  from tbl_doca da
  where da.customerid = c.customerid
) doca
cross apply
(
  select
    count(*) as total,
    count(case when db.userid = azerty.userid then 1 end)n as az
  from tbl_docb db
  where db.customerid = c.customerid
) docb
cross apply
(
  select
    count(*) as total,
    count(case when dc.userid = azerty.userid then 1 end)n as az
  from tbl_docc dc
  where dc.customerid = c.customerid
) docc
order by c.customerid;
  

Другими вариантами были бы замена подзапросов cross apply на left outer join и некоррелированных или включение подзапросов в предложение select.

Объединение итогов для документов — это еще один метод.

Затем используйте условную агрегацию для подсчетов.

непроверенные каракули в блокноте:

 ;WITH SPECIFICUSER AS
(
  SELECT UseriD
  FROM Tbl_User 
  WHERE UserName = 'azerty'
),
DOCTOTALS (  
   SELECT CustomeriD, UseriD, 'DocA' AS Src, COUNT(DocId) AS Total
   FROM Tbl_DocA 
   GROUP BY CustomeriD, UseriD

   UNION ALL

   SELECT CustomeriD, UseriD, 'DocB', COUNT(DocId)
   FROM Tbl_DocB
   GROUP BY CustomeriD, UseriD

   UNION ALL

   SELECT CustomeriD, UseriD, 'DocC', COUNT(DocId)
   FROM Tbl_DocC
   GROUP BY CustomeriD, UseriD
) 
SELECT 
 docs.CustomeriD, 
 cust.CustomerName,

 SUM(CASE WHEN usrX.UseriD is not null AND docs.Src = 'DocA' THEN docs.Total ELSE 0 END) AS Total_DocA_userX,
 SUM(CASE WHEN Src = 'DocA' THEN docs.Total ELSE 0 END) AS Total_DocA,

 SUM(CASE WHEN usrX.UseriD is not null AND docs.Src = 'DocB' THEN docs.Total ELSE 0 END) AS Total_DocB_userX,
 SUM(CASE WHEN Src = 'DocB' THEN docs.Total ELSE 0 END) AS Total_DocB,

 SUM(CASE WHEN usrX.UseriD is not null AND docs.Src = 'DocC' THEN docs.Total ELSE 0 END) AS Total_DocC_userX,
 SUM(CASE WHEN Src = 'DocC' THEN docs.Total ELSE 0 END) AS Total_DocC

FROM DOCTOTALS docs
LEFT JOIN Tbl_Customer cust ON cust.CustomeriD = docs.CustomeriD
LEFT JOIN Tbl_User usr ON usr.UseriD = docs.UseriD
LEFT JOIN SPECIFICUSER usrX ON usrX.UseriD = docs.UseriD
GROUP BY docs.CustomeriD, cust.CustomerName
ORDER BY docs.CustomeriD
  

Эти длинные имена столбцов могут быть заданы на стороне отчета