1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
|
SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [% Work], 'NEW'
AS previousPlanDate, IIf(Not IsNull([Reference Date])
AND IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed]),[Reference Date],
IIf( (IsNull([Reference Date])
AND Not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed]))
OR (not IsNull([Reference Date])
AND Not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed])),[Postponed & Agreed],[Forecast not agreed])) AS PostOrNewDate,[Deliverable Status],
IIf( (Not IsNull([Reference Date])
AND IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed])) OR (IsNull([Reference Date]) AND not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed]))
OR (not IsNull([Reference Date])
AND Not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed])) ,'Yes','No') AS customerAgreed,deliv_table.[Reporting Comment]
FROM Deliv_Table
WHERE cstr([Deliverable reference])
NOT IN (SELECT cstr([Output Data Reference])
FROM output_data_Table)
AND isNull(Deliv_Table.[Achieved])
ORDER BY WP , [Deliverable reference]
UNION SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [% Work],
IIf(Not IsNull(output_data_Table.[Postponed and Agreed date]), output_data_Table.[Postponed and Agreed date], output_data_Table.[Reference Date])
AS previousPlanDate, Deliv_Table.[Reference date]
AS PostOrNewDate, Deliv_Table.[Deliverable Status], 'Yes'
AS customerAgeed, deliv_table.[Reporting Comment]
FROM Deliv_Table, output_data_Table
WHERE cstr(output_data_Table.[Output Data Reference]) = cstr(Deliv_Table.[Deliverable reference])
AND ((Format(cdate(Deliv_Table.[Reference Date]),'dd-mmm-yy') <> Format(cdate(output_data_Table.[Reference Date]),'dd-mmm-yy')
OR (Not isNull(Deliv_Table.[Reference Date])
AND IsNull(output_data_Table.[Reference Date])))
And ((Format(cdate(Deliv_Table.[Postponed & Agreed]),'dd-mmm-yy') = Format(cdate(output_data_Table.[Postponed and Agreed date]),'dd-mmm-yy'))
OR (isNull(Deliv_Table.[Postponed & Agreed])
AND IsNull(output_data_Table.[Postponed and Agreed date])))
And ((Format(cdate(Deliv_Table.[Forecast not agreed]),'dd-mmm-yy') = Format(cdate(output_data_Table.[Forecast not agreed date]),'dd-mmm-yy'))
OR (isNull(Deliv_Table.[Forecast not agreed])
AND IsNull(output_data_Table.[Forecast not agreed date]))))
AND isNull(Deliv_Table.[Achieved])
ORDER BY WP , [Deliverable reference]
SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [Achieved], [Domain QI], [Customer QI],
[Deliverable Status], deliv_table.[Reporting Comment]
FROM deliv_table WHERE Not isnull([Achieved])
AND [Achieved] >= #04/01/2006# AND [Achieved] <= #04/30/2006# ORDER BY WP , [Deliverable reference]
SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [Achieved], [Domain QI], [Customer QI],
[Deliverable Status], deliv_table.[Reporting Comment]
FROM deliv_table WHERE Not isnull([Achieved])
AND [Achieved] >= #04/01/2006# AND [Achieved]<= #04/30/2006# ORDER BY WP , [Deliverable reference] |
Partager