SELECT DeliverydatePackingSlip,
       RessourceID, 
       COUNT(RessourceID) as Tot_Operations,
       SUM(CASE WHEN DiffPromiseDateFirst > 0 THEN 1 ELSE 0 END) as Tot_LateOnFPD,
       SUM(CASE WHEN DiffDaysToBackward > 0 THEN 1 ELSE 0 END) as Tot_LateOnBWSD,
       SUM(CASE WHEN ResourceDelay > 0 THEN 1 ELSE 0 END) as Tot_LateOnRoute,
       SUM(CASE WHEN DiffDaysToBackward > 0 and ResourceDelay > 0 THEN 1 ELSE 0 END) as LateOnBWSD,
       SUM(CASE WHEN DiffPromiseDateFirst > 0 AND ResourceDelay > 0 THEN 1 ELSE 0 END) as LateOnFPD
from 
(
SELECT Y.DeliverydatePackingSlip, X.ProductionID, X.RessourceID, Y.DiffPromiseDateFirst, P.DiffDaysToBackward, X.ResourceDelay 

FROM iq4bisprocess.FactOTDCustomer Y
	 inner join iq4bisprocess.FactOTDProduction P on Y.Salesid = P.Salesid and Y.LineNum = P.SalesLineNum  
	 inner join 

	(SELECT a.ProductionID, a.FinishedDate, a.RessourceID,
		    CASE WHEN a.ord = 1 OR a.DiffDays <= 0 THEN a.DiffDays ELSE a.DiffDays - b.DiffDays END as ResourceDelay

         FROM
         (SELECT FinishedDate, PRODUCTIONID, RessourceID, OperationID,
	         DiffDays, OperationFinished,
	         row_number() over(partition by PRODUCTIONID ORDER BY OPERATIONID) as ord
          FROM iq4bisprocess.FactOTDRessource)a

          left outer join 

          (SELECT ProductionID, DiffDays,
		  row_number() over(partition by PRODUCTIONID ORDER BY OPERATIONID) as ord 
           FROM iq4bisprocess.FactOTDRessource)b

         on a.ProductionID = b.ProductionID and a.ord = b.ord+1)X

         on X.ProductionID = P.ProductionID *)Z

WHERE DeliverydatePackingSlip < DATEADD(day, DATEDIFF(day, 0, getdate()-1), 0) and 
      DeliverydatePackingSlip > DATEADD(day, DATEDIFF(day, 0, getdate()-366), 0) and 
      RessourceID = '200012'
GROUP BY DeliverydatePackingSlip, RessourceID
ORDER BY DeliverydatePackingSlip desc

with Production as 

(SELECT a.ProductionID, a.FinishedDate, a.RessourceID,
	   CASE WHEN a.ord = 1 OR a.DiffDays <= 0 THEN a.DiffDays ELSE a.DiffDays - b.DiffDays END as ResourceDelay

from
(SELECT FinishedDate, PRODUCTIONID, RessourceID, OperationID,
	   DiffDays, OperationFinished,
	   row_number() over(partition by PRODUCTIONID ORDER BY OPERATIONID) as ord
 from iq4bisprocess.FactOTDRessource)a

 left outer join 

 (SELECT ProductionID, DiffDays, 
         row_number() over(partition by PRODUCTIONID ORDER BY OPERATIONID) as ord 
 FROM iq4bisprocess.FactOTDRessource)b
 on a.ProductionID = b.ProductionID and a.ord = b.ord+1
 WHERE RessourceID = '200012'),

Customers as 
(SELECT ProductionID, DeliverydatePackingSlip, DiffPromiseDateFirst, Y.Salesid, Y.LineNum, DiffDaysToBackward
 FROM iq4bisprocess.FactOTDCustomer Y
        inner join iq4bisprocess.FactOTDProduction P on Y.Salesid = P.Salesid and Y.LineNum = P.SalesLineNum
 WHERE DeliverydatePackingSlip < DATEADD(day, DATEDIFF(day, 0, getdate()-1), 0) and 
       DeliverydatePackingSlip > DATEADD(day, DATEDIFF(day, 0, getdate()-366), 0))


Select DeliverydatePackingSlip, RessourceID, DiffPromiseDateFirst, DiffDaysToBackward, ResourceDelay 
from Customers inner Join production on Customers.ProductionID = Production.ProductionID

quote:

---

The tables don't have Indexes

---

SQL Server parse and compile time: 
   CPU time = 2 ms, elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'table1'. Scan count 10, logical reads 50473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1793 ms,  elapsed time = 450 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.