SQL CTE Вычислить ожидающие итоги?
у меня есть база данных с несколькими отделами, у меня есть запрос на выбор оплаченных, собранных и ожидающих платежей из счетов-фактур и затрат, таким образом, им необходимо иметь отчет, показывающий «Собрано не оплачено, если > 0», и «Оплачено не получено, если < 0». поэтому я написал этот запрос, но не знаю, правильный ли он, или его можно написать иначе для оптимизации больших данных.
DECLARE @JobSImport TABLE([Id] int NOT NULL identity(1, 1), [JobNo] int, [JobDate] varchar(10),
[DepartmentId] int, [DepartmentName] varchar(200), [CustomerName] varchar(200), [FullPaid] bit);
insert into @JobSImport
select 3862, '2024-06-21', 16, 'SEA IMPORT', 'Antranik Baljian', 0 union all
select 3861, '2024-06-20', 16, 'SEA IMPORT', 'Saber Industrial Corporation', 0 union all
select 3860, '2024-06-20', 16, 'SEA IMPORT', 'Antranik Baljian', 0 union all
select 3859, '2024-06-20', 16, 'SEA IMPORT', 'Antranik Baljian', 0 union all
select 3858, '2024-06-19', 16, 'SEA IMPORT', 'Mr.Rafik Bader', 0 union all
select 3857, '2024-06-14', 16, 'SEA IMPORT', 'S.M.J ( Offshore )', 0 union all
select 3856, '2024-06-13', 16, 'SEA IMPORT', 'Oya Trading', 0 union all
select 3855, '2024-06-13', 16, 'SEA IMPORT', 'Maalouf General Trading', 0 union all
select 3854, '2024-06-12', 16, 'SEA IMPORT', 'Mousa Mazloum', 0 union all
select 3853, '2024-06-11', 16, 'SEA IMPORT', 'Aj Concept Group', 0;
DECLARE @JobSeaExport TABLE([Id] int NOT NULL identity(1, 1), [JobNo] int, [JobDate] varchar(10),
[DepartmentId] int, [DepartmentName] varchar(200), [CustomerName] varchar(200), [FullPaid] bit);
insert into @JobSeaExport
select 497, '6-14-2024', 18, 'SEA EXPORT', 'Antranik Baljian', 0 union all
select 496, '6-14-2024', 18, 'SEA EXPORT', 'Tala General Trading Company', 0 union all
select 495, '5-30-2024', 18, 'SEA EXPORT', 'Tb Steel', 1 union all
select 494, '5-29-2024', 18, 'SEA EXPORT', 'Inkript Securities', 0 union all
select 493, '5-15-2024', 18, 'SEA EXPORT', 'Ets Kamel Saad Trading', 1 union all
select 492, '5-10-2024', 18, 'SEA EXPORT', 'Ali Nour Eldein', 1 union all
select 491, '5-8-2024', 18, 'SEA EXPORT', 'Tb Steel', 1 union all
select 490, '5-2-2024', 18, 'SEA EXPORT', 'Spedicon Maldives (Pvt) Ltd.', 0 union all
select 489, '4-22-2024', 18, 'SEA EXPORT', 'Inkript Securities', 1 union all
select 488, '4-22-2024', 18, 'SEA EXPORT', 'Ets Kamel Saad Trading', 1;
DECLARE @JobSeaClearance TABLE([Id] int NOT NULL identity(1, 1), [JobNo] int, [JobDate] varchar(10),
[DepartmentId] int, [DepartmentName] varchar(200), [CustomerName] varchar(200), [FullPaid] bit);
insert into @JobSeaClearance
select 244, '2024-06-14', 17, 'SEA CLEARANCE', 'Unigaz', 0 union all
select 243, '2024-05-14', 17, 'SEA CLEARANCE', 'Pierre Merhej', 0 union all
select 242, '2024-05-13', 17, 'SEA CLEARANCE', 'Pierre Merhej', 0 union all
select 241, '2024-05-10', 17, 'SEA CLEARANCE', 'Unicorn Supply Chain Co', 0 union all
select 240, '2024-05-09', 17, 'SEA CLEARANCE', 'Joseph Fadous Est', 1 union all
select 239, '2024-03-28', 17, 'SEA CLEARANCE', 'Sucasu', 0 union all
select 238, '2024-03-14', 17, 'SEA CLEARANCE', 'Care First Pharmacy', 1 union all
select 237, '2024-01-15', 17, 'SEA CLEARANCE', 'Custom Roast', 1 union all
select 236, '2024-01-11', 17, 'SEA CLEARANCE', 'Rafic Daou', 1 union all
select 235, '2023-12-05', 17, 'SEA CLEARANCE', 'Tb Steel', 1;
DECLARE @MainInvoices TABLE([Id] int NOT NULL identity(1, 1), [JobId] int, [DepartmentId] int,
S_Amount DECIMAL(19, 4), Paid bit);
insert into @MainInvoices
select 3853, 16, 13248.83, 0 union all
select 3854, 16, 4264.40, 0 union all
select 3855, 16, 6641.05, 0 union all
select 3856, 16, 5824.40, 0 union all
select 3857, 16, 6300.00, 0 union all
select 3858, 16, 9420.44, 0 union all
select 3859, 16, 23409.40, 0 union all
select 3860, 16, 7404.40, 0 union all
select 3861, 16, 5008.05, 0 union all
select 3862, 16, 5809.40, 0 union all
select 488, 18, 2170.00, 1 union all
select 489, 18, 2593.00, 1 union all
select 490, 18, 4530.00, 0 union all
select 491, 18, 1950.00, 1 union all
select 492, 18, 1187.00, 1 union all
select 493, 18, 1700.00, 1 union all
select 494, 18, 2645.00, 0 union all
select 495, 18, 2885.00, 1 union all
select 496, 18, 2730.00, 0 union all
select 497, 18, 1930.00, 0 union all
select 235, 17, 399.60, 1 union all
select 235, 17, 52.31, 1 union all
select 235, 17, 4574.32, 1 union all
select 236, 17, 710.40, 1 union all
select 237, 17, 338.55, 1 union all
select 237, 17, 5488.35, 1 union all
select 237, 17, 388.00, 1 union all
select 237, 17, 55.87, 1 union all
select 237, 17, 30.00, 1 union all
select 236, 17, 3223.80, 1 union all
select 236, 17, 670.00, 1 union all
select 236, 17, 84.11, 1 union all
select 238, 17, 432.90, 1 union all
select 239, 17, 705.00, 0 union all
select 240, 17, 780.00, 1 union all
select 241, 17, 250.00, 0 union all
select 242, 17, 876.90, 0 union all
select 243, 17, 826.95, 0 union all
select 238, 17, 1238.99, 1 union all
select 238, 17, 360.00, 1 union all
select 240, 17, 1099.00, 1 union all
select 240, 17, 112.04, 1 union all
select 240, 17, 3745.03, 1 union all
select 239, 17, 26135.00, 1 union all
select 239, 17, 279.33, 0 union all
select 239, 17, 600.00, 0 union all
select 244, 17, 310.80, 0;
DECLARE @CostSheet TABLE([Id] int NOT NULL identity(1, 1), [JobId] int, [DepartmentId] int,
S_Amount DECIMAL(19, 4), Paid bit);
insert into @CostSheet
select 3853, 16, 13076.83, 0 union all
select 3854, 16, 3500.00, 0 union all
select 3854, 16, 373.97, 0 union all
select 3855, 16, 6567.83, 0 union all
select 3856, 16, 5640.09, 0 union all
select 3856, 16, 35.00, 0 union all
select 3857, 16, 6164.00, 0 union all
select 3858, 16, 9230.00, 0 union all
select 3859, 16, 22982.09, 0 union all
select 3860, 16, 7325.62, 0 union all
select 3861, 16, 4910.83, 0 union all
select 3862, 16, 5660.09, 0 union all
select 488, 18, 2079.00, 1 union all
select 489, 18, 1844.00, 1 union all
select 489, 18, 185.00, 1 union all
select 489, 18, 155.00, 1 union all
select 489, 18, 140.00, 1 union all
select 489, 18, 67.49, 1 union all
select 490, 18, 3925.00, 0 union all
select 490, 18, 7.54, 0 union all
select 490, 18, 150.00, 0 union all
select 490, 18, 150.00, 0 union all
select 490, 18, 60.00, 0 union all
select 491, 18, 1107.00, 1 union all
select 491, 18, 200.00, 1 union all
select 491, 18, 157.00, 1 union all
select 491, 18, 30.75, 1 union all
select 491, 18, 156.46, 1 union all
select 492, 18, 971.00, 1 union all
select 492, 18, 41.23, 1 union all
select 490, 18, 41.24, 0 union all
select 493, 18, 1614.00, 1 union all
select 492, 18, 20.00, 1 union all
select 490, 18, 20.00, 0 union all
select 494, 18, 1914.00, 0 union all
select 494, 18, 185.00, 0 union all
select 494, 18, 155.00, 0 union all
select 494, 18, 140.00, 1 union all
select 495, 18, 1777.00, 1 union all
select 495, 18, 65.94, 1 union all
select 495, 18, 245.00, 0 union all
select 495, 18, 230.00, 0 union all
select 495, 18, 157.47, 1 union all
select 495, 18, 70.18, 0 union all
select 494, 18, 67.49, 0 union all
select 496, 18, 163.00, 0 union all
select 496, 18, 250.00, 0 union all
select 496, 18, 2054.00, 0 union all
select 497, 18, 1457.00, 0 union all
select 497, 18, 163.00, 0 union all
select 497, 18, 130.00, 0 union all
select 495, 18, 20.00, 1 union all
select 495, 18, 9.99, 1;
-- ==================================================================================================
;WITH TotalData_CTE
AS
(
SELECT JobNo, JobDate, DepartmentId, DepartmentName, CustomerName, FullPaid
FROM @JobSImport job
UNION
SELECT JobNo, JobDate, DepartmentId, DepartmentName, CustomerName, FullPaid
FROM @JobSeaExport job
UNION
SELECT JobNo, JobDate, DepartmentId, DepartmentName, CustomerName, FullPaid
FROM @JobSeaClearance job
),
TotalCollectedInvocies
AS
(
SELECT JobNo, job.DepartmentId,
ISNULL(SUM(m.S_Amount), 0) AS TotalCollected
FROM TotalData_CTE job
INNER JOIN @MainInvoices m ON m.JobId = job.JobNo AND m.DepartmentId = job.DepartmentId
WHERE Paid = 1
GROUP BY JobNo, job.DepartmentId
),
TotalPaidCosts
AS
(
SELECT JobNo, job.DepartmentId,
ISNULL(SUM(cs.S_Amount), 0) AS TotalPaid
FROM TotalData_CTE job
INNER JOIN @CostSheet cs ON cs.JobId = job.JobNo AND cs.DepartmentId = job.DepartmentId
WHERE Paid = 1
GROUP BY JobNo, job.DepartmentId
)
SELECT TotalPaidCosts.JobNo, TotalPaidCosts.DepartmentId,
TotalCollected - TotalPaid AS TotalCollected
FROM TotalCollectedInvocies
INNER JOIN TotalPaidCosts on TotalPaidCosts.JobNo = TotalCollectedInvocies.JobNo
AND TotalPaidCosts.DepartmentId = TotalCollectedInvocies.DepartmentId
ORDER BY TotalPaidCosts.DepartmentId, TotalPaidCosts.JobNo
;
я не знаю, как оптимизировать CTE и лучший ли это подход на первый взгляд если у кого-то есть другая точка зрения, зная, что этот запрос выполнил свою работу, но он слишком медленный
Спасибо
это исходный запрос для отображения следующего отчета:
ALTER PROCEDURE [dbo].[rp_DetailedProfitReport_V1]
-- Add the parameters for the stored procedure here
@DateFrom Date = NULL,
@DateTo Date = NULL,
@MemberId int = 0,
@UserId int = 0,
@SalesId int = 0,
@DepartmentId int = 0,
@DetailedJobs bit = 1,
@PendingFullPaid int = 0
AS
BEGIN
SET NOCOUNT ON;
;WITH TotalData_CTE
AS
(
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobSImport job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
UNION
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobSeaExport job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
UNION
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobSeaClearance job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
UNION
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobAirImport job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
UNION
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobAirExport job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
UNION
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobAirClearance job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
UNION
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobLandFreight job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
UNION
SELECT DISTINCT JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid
FROM JobWarehouse job
INNER JOIN Departments d ON d.Id = job.DepartmentId
INNER JOIN Customers m ON m.Id = job.CustomerId
WHERE CanceledJob = 0
AND closed = 0
),
TotalInvocies_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
SUM(ISNULL(m.S_Amount, 0)) AS TotalInvoices
FROM TotalData_CTE job
LEFT JOIN MainInvoice m ON m.JobId = job.JobNo AND m.DepartmentId = job.DepartmentId
WHERE m.DebitNote = 0
AND m.CreditNote = 0
GROUP BY JobNo, job.DepartmentId
),
TotalPendingInvoices_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
COUNT(ISNULL(m.InvoiceId, 0)) AS PendingInvoices
FROM TotalData_CTE job
LEFT JOIN MainInvoice m ON m.JobId = job.JobNo AND m.DepartmentId = job.DepartmentId
WHERE m.Paid = 0
-- AND m.DebitNote = 0
GROUP BY JobNo, job.DepartmentId
),
TotalCosts_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
SUM(ISNULL(c.S_TotalAmount, 0)) AS TotalCosts
FROM TotalData_CTE job
LEFT JOIN CostSheet c ON c.JobId = job.JobNo AND c.DepartmentId = job.DepartmentId
WHERE c.Payment <> 14
GROUP BY JobNo, job.DepartmentId
),
TotalPendingCosts_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
COUNT(cs.Id) AS PendingCosts
FROM TotalData_CTE job
LEFT JOIN CostSheet cs ON cs.JobId = job.JobNo AND cs.DepartmentId = job.DepartmentId
WHERE cs.Paid = 0
AND Payment NOT IN (14, 16)
GROUP BY JobNo, job.DepartmentId
),
TotalDebitNotesInvoices_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
SUM(ISNULL(m.S_Amount, 0)) AS TotalDebitNotesInvoices
FROM TotalData_CTE job
LEFT JOIN MainInvoice m ON m.JobId = job.JobNo AND m.DepartmentId = job.DepartmentId
WHERE m.Paid = 0
AND m.DebitNote = 1
GROUP BY JobNo, job.DepartmentId
),
TotalCustomsCosts_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
SUM(ISNULL(cs.S_TotalAmount, 0)) AS TotalCustomsCosts
FROM TotalData_CTE job
LEFT JOIN CostSheet cs ON cs.JobId = job.JobNo AND cs.DepartmentId = job.DepartmentId
WHERE cs.Paid = 0
AND Payment = 14
GROUP BY JobNo, job.DepartmentId
),
TotalDopInvoices_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
CASE WHEN FullPaid = 0 THEN SUM(ISNULL(m.S_Amount, 0)) - SUM(ISNULL(m.TotalReceived, 0))
ELSE SUM(ISNULL(m.S_Amount, 0)) - SUM(ISNULL(m.TotalReceived, 0)) END AS TotalDopInvoices
FROM TotalData_CTE job
LEFT JOIN MainInvoice m ON m.JobId = job.JobNo AND m.DepartmentId = job.DepartmentId
WHERE m.Paid = 0
GROUP BY JobNo, job.DepartmentId, FullPaid
),
TotalDOPCosts_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
SUM(ISNULL(cs.S_TotalAmount, 0)) AS TotalDOPCosts
FROM TotalData_CTE job
INNER JOIN CostSheet cs ON cs.JobId = job.JobNo AND cs.DepartmentId = job.DepartmentId
WHERE cs.Paid = 0
GROUP BY JobNo, job.DepartmentId
),
TotalDopPartialCollectedNotPaid_CTE
AS
(
SELECT DISTINCT job.JobNo, job.DepartmentId,
ISNULL(inv.TotalReceived, 0) AS TotalPaid
FROM TotalData_CTE job
INNER JOIN MainInvoice inv ON inv.JobID = job.JobNo and inv.DepartmentID = job.DepartmentID
INNER JOIN CostSheet cs ON cs.JobID = job.JobNo and cs.DepartmentID = job.DepartmentID
WHERE (ISNULL(INV.ReferenceId, 0) > 0)
AND (FullPaid = 0
OR (inv.Paid = 0 AND inv.TotalReceived > 0 AND inv.TotalReceived < inv.F_Amount))
GROUP BY job.JobNo, job.DepartmentId, inv.TotalReceived
HAVING SUM(ISNULL(inv.TotalReceived, 0)) > 0
),
TotalCreditNoteDopPaidNotCollected_CTE
AS
(
SELECT DISTINCT job.JobNo, job.DepartmentId,
CASE WHEN JobNo in (0) THEN 0 ELSE SUM(ISNULL(cs.S_TotalAmount, 0)) END AS TotalPaid
FROM TotalData_CTE job
INNER JOIN MainInvoice inv ON inv.JobID = job.JobNo and inv.DepartmentID = job.DepartmentID
INNER JOIN CostSheet cs ON cs.JobID = job.JobNo and cs.DepartmentID = job.DepartmentID
WHERE cs.Paid = 1
AND cs.Payment in (7)
AND inv.Paid = 0
GROUP BY job.JobNo, job.DepartmentId
),
TotalDopCollectedNotPaid_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
SUM(ISNULL(cs.S_TotalAmount, 0)) AS TotalPaid
FROM TotalData_CTE job
INNER JOIN CostSheet cs ON cs.JobId = job.JobNo AND cs.DepartmentId = job.DepartmentId
WHERE cs.Paid = 0
AND cs.Payment not in (14, 16)
AND FullPaid = 1
AND ISNULL(ReferenceId, 0) = 0
GROUP BY JobNo, job.DepartmentId
),
TotalDopPaidNotCollected_CTE
AS
(
SELECT DISTINCT JobNo, job.DepartmentId,
SUM(ISNULL(cs.S_TotalAmount, 0)) AS TotalPaid
FROM TotalData_CTE job
INNER JOIN CostSheet cs ON cs.JobId = job.JobNo AND cs.DepartmentId = job.DepartmentId
WHERE cs.Paid = 1
AND cs.Payment not in (7, 14, 16)
AND FullPaid = 0
AND ISNULL(ReferenceId, 0) = 0
GROUP BY JobNo, job.DepartmentId
)
SELECT job.JobNo, JobDate, job.CustomerId, job.DepartmentId, DepartmentName, CustomerName,
CASE WHEN PendingInvoices > 0 THEN ISNULL(TotalInvoices, 0) ELSE 0 END AS TotalInvoices,
ISNULL(PendingInvoices, 0) AS PendingInvoices,
CASE WHEN PendingInvoices > 0 THEN ISNULL(TotalCosts, 0) ELSE 0 END AS TotalCosts,
ISNULL(PendingCosts, 0) AS PendingCosts,
ISNULL(TotalDebitNotesInvoices, 0) AS TotalDebitNotesNotPaid,
ISNULL(TotalCustomsCosts, 0) AS TotalCustomsNotesNotPaid,
ISNULL(TotalDopInvoices, 0) AS TotalDopInvoices,
ISNULL(TotalDOPCosts, 0) AS TotalDOPCosts,
ISNULL(tpcnp.TotalPaid, 0) AS TotalPartialCollectedNotPaid,
ISNULL(tcnpnc.TotalPaid, 0) AS TotalDopPendingCreditNote,
ISNULL(tcnp.TotalPaid, 0) AS TotalDopCollectedNotPaid,
ISNULL(tpnc.TotalPaid, 0) AS TotalDopPaidNotCollected
--
FROM TotalData_CTE job
INNER JOIN TotalInvocies_CTE inv ON job.JobNo = inv.JobNo AND job.DepartmentId = inv.DepartmentId
INNER JOIN TotalCosts_CTE c ON job.JobNo = c.JobNo AND job.DepartmentId = c.DepartmentId
LEFT JOIN TotalPendingInvoices_CTE tpinv ON job.JobNo = tpinv.JobNo AND job.DepartmentId = tpinv.DepartmentId
LEFT JOIN TotalPendingCosts_CTE tpcs ON job.JobNo = tpcs.JobNo AND job.DepartmentId = tpcs.DepartmentId
LEFT JOIN TotalDebitNotesInvoices_CTE tdni ON job.JobNo = tdni.JobNo AND job.DepartmentId = tdni.DepartmentId
LEFT JOIN TotalCustomsCosts_CTE tcc ON job.JobNo = tcc.JobNo AND job.DepartmentId = tcc.DepartmentId
LEFT JOIN TotalDopInvoices_CTE pInv ON job.JobNo = pInv.JobNo AND job.DepartmentId = pInv.DepartmentId
LEFT JOIN TotalDOPCosts_CTE tdc ON job.JobNo = tdc.JobNo AND job.DepartmentId = tdc.DepartmentId
LEFT JOIN TotalDopPartialCollectedNotPaid_CTE tpcnp ON job.JobNo = tpcnp.JobNo AND job.DepartmentId = tpcnp.DepartmentId
LEFT JOIN TotalCreditNoteDopPaidNotCollected_CTE tcnpnc ON job.JobNo = tcnpnc.JobNo AND job.DepartmentId = tcnpnc.DepartmentId
LEFT JOIN TotalDopCollectedNotPaid_CTE tcnp ON job.JobNo = tcnp.JobNo AND job.DepartmentId = tcnp.DepartmentId
LEFT JOIN TotalDopPaidNotCollected_CTE tpnc ON job.JobNo = tpnc.JobNo AND job.DepartmentId = tpnc.DepartmentId
--
WHERE (@PendingFullPaid = 2 OR (@PendingFullPaid = 0 AND (PendingInvoices > 0 OR PendingCosts > 0)))
AND (@DateFrom IS NULL OR CAST(JobDate AS date) >= @DateFrom)
AND (@DateTo IS NULL OR CAST(JobDate AS date) <= @DateTo)
AND (@MemberId = 0 or CustomerId = @MemberId)
GROUP BY job.JobNo, JobDate, job.CustomerId, job.DepartmentId, DepartmentName, CustomerName,
TotalInvoices, TotalCosts, PendingInvoices, PendingCosts, TotalDebitNotesInvoices, TotalCustomsCosts,
TotalDopInvoices, TotalDOPCosts, tpcnp.TotalPaid, tcnpnc.TotalPaid, tcnp.TotalPaid, tpnc.TotalPaid
ORDER BY JobNo, DepartmentName, JobDate DESC;
END
Выходной отчет, показывающий все разделы указанной выше хранимой процедуры
Здравствуйте, ключевое слово DISTINCT было добавлено для удаления повторяющихся строк из определенных выбранных столбцов (JobNo, JobDate, job.CustomerId, d.Id as DepartmentId, d.DepartmentName, m.CustomerName, FullPaid), поскольку существует больше столбцов, но для этого конкретного критерия могут быть дубликаты. Каждый отдел является самостоятельным отделом со своими собственными атрибутами. Основная цель состоит в том, чтобы иметь все ожидающие задания и получить для каждого задания свои собственные счета и ведомости затрат (платежей), тогда, если результат отрицательный, это означает, что выплаченная сумма не собрана, если она >0 означает, что он собран для оплаты.
Удаление дубликатов — дорогостоящая операция, поэтому необходимость в ней возникает редко. Из ваших таблиц я прочитал следующее: задание состоит из нескольких частей (подзадания: JobSImport, JobSeaExport). Для одного задания таких деталей может быть несколько в одной, двух или всех трех таблицах. И для одной и той же работы может быть несколько дат, клиентов и/или отделов...
Вас не интересуют отдельные подзадания, имеющие одну и ту же дату, клиента и отдел, если только они не отличаются флагом полной оплаты. (То есть, если все подзадания для задания на одну дату, клиента и отдела полностью оплачены или не полностью оплачены, вы выбираете их как одну строку, если некоторые из них полностью оплачены, а другие нет, вы выбираете их как две строки. Разве я все это правильно понял?
На самом деле в заданиях нет дубликатов, каждое задание состоит из более чем 50 столбцов, но для этого запроса мне нужно знать только ожидающие задания, чтобы я мог отфильтровать их дебиторскую и кредиторскую задолженность, таким образом, можно выбрать только те записи с определенными атрибутами. приводят к повторяющимся значениям, поэтому используется «DISTINCT». и между заданиями нет общих или общих данных, поскольку каждое задание отличается от других, но для установки образцов данных я использовал их как копирование/вставку и результат дублирования имен, но мне нужны только JobId, DepartmentId для запроса счетов и смет ( дебиторская и кредиторская задолженность).
На самом деле это не должен быть один запрос. Вероятно, вам следует выбрать данные I во временную таблицу для обработки. Это поможет в обслуживании кода больше, чем что-либо еще. Используйте табличную переменную, если вам нужен формат запроса, а не хранимая процедура.
ДДЛ, пожалуйста. Вы не уточняете имена столбцов. Это затрудняет определение того, из какой таблицы взят каждый столбец. Но кроме того, расположение ваших столбцов невозможно даже определить на основе предоставленного вами кода. Чтобы любой мог вам помочь, необходима модель, достаточно полная для описания проблемы. Я рекомендую включить достаточно кода, чтобы создать что-то, что работало бы на сайте SQL-скриптов, таком как dbfiddle.uk. А вы говорите: «Я не знаю, правильно ли это». Итак, повторюсь, мы ничем не можем помочь. Если вы можете показать разницу между тем, что вы хотите, и тем, что вы получаете, укажите это.
@Chris Schaller: Это плохой совет. Запрос хорошо структурирован (хотя, возможно, с недостатками). Здесь нет смысла создавать временные таблицы. Это может даже замедлить ход событий. Фактически, единственная СУБД, в которой я вижу людей, постоянно использующих временные таблицы, — это Microsoft SQL Server. Возможно, это даже плохая привычка, возникшая из тех времен, когда в СУБД возникали проблемы с большими запросами.
@Adel Merhej: Вы забыли пометить свой запрос своей СУБД. Это SQL-сервер?
Спасибо за вашу помощь, ребята, я исправлю ответ @Thorsten Kettner и улучшу свой запрос, но пока я думаю, что DISTINCT следует удалить, поскольку он бесполезен. Я постараюсь опубликовать более точные данные в упомянутых выше образцах, поскольку они показывают дублирующуюся информацию в разных отделах, где это не так.
@ThorstenKettner, мой совет - быть прагматичным. Я сам не использую многие временные таблицы, но этот тип логики может быть меньшим злом, которое поможет им управлять логикой в плохой схеме. Бесполезно учить кого-то с таким мышлением тому, что подобный запрос можно выполнить за один запрос, но они также вряд ли смогут изменить схему. В любом случае, молодец, что приложили усилия, у меня определенно не было времени.
Ответы 1
На этом этапе я решил написать ответ, потому что иначе вы можете ничего не получить.
Вы попросили нас рассмотреть ваш запрос, потому что вы
- не знаю, оптимально ли это
- даже не знаю, правильно ли это
Чтобы дать ответы, нужно знать данные. В комментариях к запросу я постарался узнать у вас, что представляют собой таблицы, что такое работа и почему существуют отдельные таблицы, которые необходимо объединить. Вы не смогли дать исчерпывающего объяснения, и, возможно, именно поэтому вы до сих пор не получили ответа.
В вашем запросе основная задача, по-видимому, состоит в том, чтобы избавиться от повторяющихся данных. Это очень затратная задача для СУБД. И это задача, которая требуется очень редко. Когда профессиональный разработчик баз данных видит SELECT DISTINCT, звенит тревога. Бывают случаи, когда это уместно, но их немного. Чаще всего SELECT DISTINCT — это просто индикатор плохо написанного запроса или, что еще хуже, плохо спроектированной базы данных. UNION [DISTINCT] вместо UNION ALL тоже очень часто неуместно. Часто его даже используют только из-за незнания того, что он на самом деле делает, и этого UNION ALL было бы достаточно для выполнения той же задачи. Однако в комментариях к запросу вы говорите, что вам нужны все эти DISTINCT и UNION [DISTINCT], поэтому ключевая часть анализа запроса — выяснить, почему.
Ваш запрос буквально усеян DISTINCT и UNION [DISTINCT]. Давайте сначала посмотрим на самую простую часть:
SELECT DISTINCT JobNo, job.DepartmentId,
ISNULL(SUM(m.S_Amount), 0) AS TotalCollected
FROM ...
GROUP BY JobNo, job.DepartmentId
Здесь вы агрегируете данные и с помощью предложения GROUP BY указываете СУБД вернуть одну строку результата для каждого JobNo и DepartmentId. Вы выбираете два столбца (и сумму), а затем применяете DISTINCT. Если в списке по одной строке на JobNo и DepartmentId и на оба столбца, как могут быть дубликаты? Это просто невозможно. DISTINCT является излишним, и если СУБД этого не увидит, она проделает ненужную дополнительную работу.
Теперь давайте посмотрим на часть, о которой не так просто судить:
SELECT DISTINCT jobno, jobdate, customerid, departmentid, fullpaid
FROM JobSImport
Это выглядит очень подозрительно. В комментариях к запросу вы сообщили нам, что вам необходимо подать заявку DISTINCT здесь, чтобы удалить дубликаты. Это означает, что комбинация jobno, jobdate, customerid, Departmentid и Fullpaid не уникальна в таблице. Идентификатор задания может встречаться несколько раз, а его строки могут иметь разные даты и относиться к разным клиентам и отделам. Итак, хотя вы называете таблицу псевдонимом job, она содержит не задания (с одной строкой для номера задания), а что-то еще, что вы до сих пор не смогли объяснить. В комментариях к запросу я предположил, что это может быть таблица подзадач, назначенных разным клиентам и отделам. Это объяснило бы разные даты, клиентов, отделы и статусы оплаты для одной работы.
Но сейчас самое время остановиться на мгновение... Может быть, только может быть, дата задания, идентификатор клиента, идентификатор отдела на самом деле относятся не к подзаданию (или чему-то еще), а ко всей работе. Возможно, вы знаете, что DISTINCT сократит строки до одной строки на идентификатор задания (или идентификатор задания + идентификатор отдела, поскольку это ключ, который вы используете позже для объединения счетов и затрат). Если это так, то ваша модель данных не нормализована, и вместо того, чтобы думать о том, как написать оптимальный запрос, вам следует пересмотреть дизайн базы данных. Нормализация базы данных заключается в правильном хранении данных в базе данных без избыточности (которая несет в себе риск несогласованности данных, а также приводит к необходимости удаления повторяющихся данных практически в каждом втором запросе). Возможно, вам просто нужна основная таблица заданий, чтобы один раз сохранить дату задания. Возможно, вам нужна таблица отделов работ, содержащая информацию о том, кто является единственным клиентом отдела по данному заданию. Я не знаю. Только вы можете знать ответ. Если концепция нормализации базы данных для вас нова, изучите ее сейчас и примените к своей базе данных.
Если база данных нормализована и на самом деле вы просто хотите объединить подзадания (или что бы то ни было), чтобы получить отдельные комбинации jobno, jobdate, customerid, Departmentid и Fullpaid, тогда возникает вопрос: почему эта комбинация? В комментариях к запросу вы говорите, что вам нужны такие UNION [DISTINCT] — опять же для того, чтобы удалить дубли. Это означает, что одну и ту же комбинацию jobno, jobdate, customerid, Departmentid и Fullpaid можно найти в разных таблицах «job». И мы можем еще раз спросить: почему вы хотите, чтобы для этой комбинации в итоге были уникальные строки? Возможный результат может выглядеть примерно так:
Вы вызываете CTE, содержащий эти строки, TotalData_CTE и присоединяете его к своим счетам и затратам. Допустим, ваш MainInvoice содержит следующие строки:
Это ваше присоединение:
SELECT DISTINCT JobNo, job.DepartmentId,
ISNULL(SUM(m.S_Amount), 0) AS TotalCollected
FROM TotalData_CTE job
INNER JOIN MainInvoice m ON m.JobId = job.JobNo AND m.DepartmentId = job.DepartmentId
WHERE Paid = 1
GROUP BY JobNo, job.DepartmentId
Давайте сначала посмотрим на объединенные строки перед агрегацией:
А теперь суммировано:
Вы умножили сумму для отдела dep01 на три, поскольку в TotalData_CTE есть три строки для задания № 1 и идентификатора отдела dep01. Вероятно, это нежелательно.
Итак, как бы мы на это ни смотрели, либо ваша модель данных ошибочна, либо запрос. Я предполагаю: и то, и другое. Исправьте модель данных, если она действительно еще не нормализована, а затем посмотрите, какие столбцы вы действительно хотите выбрать в TotalData_CTE, чтобы получить правильные результаты агрегирования.
Спасибо за подробные объяснения и за ваше драгоценное время, чтобы указать на некоторые критические моменты и недостатки в приведенном выше запросе, достаточно ясные, чтобы сказать, что есть проблема с DISTINCT. Данные хорошо структурированы, использование Distinct в запросе бесполезно, поэтому в каждом отделе есть совершенно разные данные, проблема с рекурсией, я думаю, как упоминал @Chris-Schaller, это должно быть в одном запросе, но, честно говоря, я не знаю. Не зная, с чего начать рефакторинг запроса, его необходимо полностью пересмотреть.
Я не понимаю всех этих
DISTINCT. Почему в таблице две строки с одинаковой информацией? ИUNIONвместоUNION ALLснова предполагает удаление дубликатов. Это специально? Означает ли это, что задание может быть JobSeaExport и JobAirImport одновременно? Или зачем вообще нужны отдельные таблицы вакансий? Возможно, вы сможете объяснить, что такое работа и что представляют собой различные таблицы.