SELECT
MW_PSR_ID,
[Issue Technical Package Planned],
[Issue Technical Package Planned] AS [Issue Technical Package Forecast],
[Issue Technical Package Actual],
DATEADD(day, - 14, [Quotation Received From Vendor Planned]) AS [RFQ Issued to Vendor Planned],
CASE WHEN [RFQ Issued to Vendor Actual] IS NULL THEN DATEADD(day, 7, [Issue Technical Package Forecast]) ELSE [RFQ Issued to Vendor Actual] END AS [RFQ Issued to Vendor Forecast],
[RFQ Issued to Vendor Actual],
DATEADD(day, - 7, [Issue for Technical Analysis Planned]) AS [Quotation Received From Vendor Planned],
CASE WHEN [Quotation Received From Vendor Actual] IS NULL THEN DATEADD(day, 14, [RFQ Issued to Vendor Forecast]) ELSE [Quotation Received From Vendor Actual] END AS [Quotation Received From Vendor Forecast], [Quotation Received From Vendor Actual], DATEADD(day, - 7,
[Technical Analysis Completed Planned]) AS [Issue for Technical Analysis Planned],
[Issue for Technical Analysis Forecast],
[Issue for Technical Analysis Actual],
DATEADD(day, - 4, [CBA Completed Planned]) AS [Technical Analysis Completed Planned],
CASE WHEN [Technical Analysis Completed Actual] IS NULL THEN DATEADD(day, 7, [Issue for Technical Analysis Forecast]) ELSE [Technical Analysis Completed Actual] END AS [Technical Analysis Completed Forecast],
[Technical Analysis Completed Actual],
DATEADD(day, - 3, [Entitlement Submitted Planned]) AS [CBA Completed Planned],
CASE WHEN [CBA Completed Actual] IS NULL THEN DATEADD(day, 4, [Technical Analysis Completed Forecast]) ELSE [CBA Completed Actual] END AS [CBA Completed Forecast],
[CBA Completed Actual],
DATEADD(day, - 2, [Entitlement Approved Planned]) AS [Entitlement Submitted Planned],
CASE WHEN [Entitlement Submitted Actual] IS NULL THEN DATEADD(day, 3, [CBA Completed Forecast]) ELSE [Entitlement Submitted Actual] END AS [Entitlement Submitted Forecast],
[Entitlement Submitted Actual],
DATEADD(day, - 3, [Change Proposal Submitted Planned]) AS [Entitlement Approved Planned],
CASE WHEN [Entitlement Approved Actual] IS NULL THEN DATEADD(day, 2, [Entitlement Submitted Forecast]) ELSE [Entitlement Approved Actual] END AS [Entitlement Approved Forecast],
[Entitlement Approved Actual], DATEADD(day, - 3, [Change Proposal Approved Planned]) AS [Change Proposal Submitted Planned],
CASE WHEN [Change Proposal Submitted Actual] IS NULL THEN DATEADD(day, 3, [Entitlement Approved Forecast]) ELSE [Change Proposal Submitted Actual] END AS [Change Proposal Submitted Forecast],
[Change Proposal Submitted Actual],
DATEADD(day, - 3, [Change Order Issued Planned]) AS [Change Proposal Approved Planned],
CASE WHEN [Change Proposal Approved Actual] IS NULL THEN DATEADD(day, 3, [Change Proposal Submitted Forecast]) ELSE [Change Proposal Approved Actual] END AS [Change Proposal Approved Forecast],
[Change Proposal Approved Actual],
DATEADD(day, - 7, [PO Issued Planned]) AS [Change Order Issued Planned],
CASE WHEN [Change Order Issued Actual] IS NULL THEN DATEADD(day, 3, [Change Proposal Approved Forecast]) ELSE [Change Order Issued Actual] END AS [Change Order Issued Forecast],
[Change Order Issued Actual],
DATEADD(day, - 7, [Submittal Issued Planned]) AS [PO Issued Planned],
CASE WHEN [PO Issued Actual] IS NULL THEN DATEADD(day, 3, [Change Order Issued Forecast]) ELSE [PO Issued Actual] END AS [PO Issued Forecast],
[PO Issued Actual],
DATEADD(day, - 14, [Submittal Approved Planned]) AS [Submittal Issued Planned],
CASE WHEN [Submittal Issued Actual] IS NULL THEN DATEADD(day, 7, [PO Issued Forecast]) ELSE [Submittal Issued Actual] END AS [Submittal Issued Forecast],
[Submittal Issued Actual],
DATEADD(day, [Lead Time] * 7, FND) AS [Submittal Approved Planned],
CASE WHEN [Submittal Approved Actual] IS NULL THEN DATEADD(day, 14, [Submittal Issued Forecast]) ELSE [Submittal Approved Actual] END AS [Submittal Approved Forecast],
[Submittal Approved Actual],
[Lead Time],
[Lead Time] * 7 + [Submittal Approved Forecast] AS FORECAST_DELIVERY_DATE,
FND,
CASE WHEN [FND] IS NULL THEN '0' ELSE datediff(day, [FND],
[FORECAST_DELIVERY_DATE]) / 7 END AS FLOAT
FROM
dbo.Q_PSR_P6_FND