i have a database with several departments, i have a query to select paid, collected and pending payments from invoices and costs, thus they need to have a report showing Collected not paid if > 0, and Paid not collected if < 0 hence i wrote this query but i don't know if it's correct or it can be written otherwise to optimize large data
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
;
i don't know how to optimize CTE or if this is the best approach at first if someone kindly have different point of view, knowing this query did the job but it's too slow
thanks
that's the original query to display the following report:
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
The output report showing all sections for the above Stored Procedure
At this point I have decided to write an answer, because otherwise you may get none.
You have asked us to look at your query, because you
In order to give the answers one must know the data. In the request comments I have tried to learn from you what the tables represent, what a job is and why there are separate tables that you must union. You have not been able to give an exhaustive explanation, and this may be the reason you have got no answer, yet.
In your query the main task seems to be to get rid of duplicate data. This is a very costly task for a DBMS. And it is a task very rarely needed. When a professional database developer sees SELECT DISTINCT, alarm bells ring. There are occasions where this is appropriate, but those are few. Most often SELECT DISTINCT is just an indicator for a badly written query, or worse, a badly designed database. UNION [DISTINCT] instead of UNION ALL, too, is very often inappropriate. Often it is even only used because of obliviance to what it actually does and that UNION ALL would suffice to do the same task. In the request comments, however, you say that you do need all those DISTINCT and UNION [DISTINCT], so the key part in analyzing the query is to find out why.
Your query is literally plastered with DISTINCT and UNION [DISTINCT]. Let's look at the easiest part first:
SELECT DISTINCT JobNo, job.DepartmentId,
ISNULL(SUM(m.S_Amount), 0) AS TotalCollected
FROM ...
GROUP BY JobNo, job.DepartmentId
You are aggregating data here, and with the GROUP BY clause, you tell the DBMS to return one result row per JobNo and DepartmentId. You select the two columns (and a sum), and then you apply DISTINCT. With one row per JobNo and DepartmentId and both columns in the list, how can there be possibly be any duplicates? It is just not possible. The DISTINCT is superfluous, and if the DBMS does not see through this, it will do unnecessary extra work.
Now let's look at a part that is not as easy to judge:
SELECT DISTINCT jobno, jobdate, customerid, departmentid, fullpaid
FROM JobSImport
This looks very suspicious. You have told us in the request comments, that you must apply DISTINCT here in order to remove duplicates. That means that the combination of jobno, jobdate, customerid, departmentid and fullpaid is not unique in the table. A jobid can occur multiple times, and its rows can have different dates and refer to different customers and departments. So, while you alias the table job, it is not containing jobs (with one row for a job number), but something else that you have failed to explain so far. In the request comments I guessed this may be a table of subjobs assigned to different customers and departments. That would explain the different dates, customers, departments and pay statuses for a single job.
But this is a moment to pause for a moment ... Maybe, just maybe, the jobdate, customerid, departmentid don't really refer to a subjob (or whatever this is), but to the whole job. Maybe you know that DISTINCT will boil the rows down to one row per jobid (or jobid + departmentid, as this is the key you use later to join invoices and costs). If this is the case, then your data model is not normalized, and rather than thinking about how to write an optimal query, you should reconsider the database design. Database normalization is about properly storing the data in a database without redundancies (that bare the risk of data inconsistencies and also make it necessary to remove duplicate data in about every other query). Maybe you just need a main job table here to hold the job's jobdate once. Maybe you need a job-department table, containing information on who the department's single customer is for the job. I don't know. Only you can know the answer. If the concept of database normalization is new to you, learn it now and apply it to your database.
If the database is normalized, and it is really just that you want to bundle the subjobs (or whatever they are) to get distinct combinations of jobno, jobdate, customerid, departmentid and fullpaid, then the question is: why this combination? In the request comments you say you need those UNION [DISTINCT] - again in order to remove duplicates. That means that the same combination of jobno, jobdate, customerid, departmentid and fullpaid can be found in different "job" tables. And we can ask again: why do you want to end up with unique rows for this combination? A possible result may look something like this:
| jobno | jobdate | customerid | departmentid | fullpaid |
|---|---|---|---|---|
| 1 | 2024-01-01 | cus01 | dep01 | yes |
| 1 | 2024-01-02 | cus01 | dep01 | no |
| 1 | 2024-01-01 | cus02 | dep01 | no |
| 1 | 2024-01-03 | cus02 | dep02 | no |
You call the CTE containing these rows TotalData_CTE and join it with your invoices and costs. Let's say your MainInvoice contains these rows:
| jobno | departmentid | s_amount | paid |
|---|---|---|---|
| 1 | dep01 | 1000 | 1 |
| 1 | dep02 | 2000 | 1 |
This is your join:
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
Let's first look at the joined rows before aggregation:
| jobno | jobdate | customerid | departmentid | fullpaid | jobno | departmentid | s_amount | paid |
|---|---|---|---|---|---|---|---|---|
| 1 | 2024-01-01 | cus01 | dep01 | yes | 1 | dep01 | 1000 | 1 |
| 1 | 2024-01-02 | cus01 | dep01 | no | 1 | dep01 | 1000 | 1 |
| 1 | 2024-01-01 | cus02 | dep01 | no | 1 | dep01 | 1000 | 1 |
| 1 | 2024-01-03 | cus02 | dep02 | no | 1 | dep02 | 2000 | 1 |
And now aggregated:
| jobno | departmentid | totalcollected |
|---|---|---|
| 1 | dep01 | 3000 |
| 1 | dep02 | 2000 |
You have multiplied the amount for departement dep01 by three, because there are three rows in the TotalData_CTE for jobno 1 and departementid dep01. This is probably not desired.
So, however we look at this, either your data model is flawed or the query. My guess is: both. Get the data model fixed if it is really not normalized yet, then see which columns you really want to select in the TotalData_CTE to get proper aggregation results.