I have the following load script in which I need to have the appropriate MONTHYEAR displayed between each phase_start and phase_end. Notice how PHASE_ID 2 should display MONTH_YEAR value = Jul 2016 and Jun 2016. PHASE_ID 3 should display July 2016 and Aug 2016. PHASE_ID 4 = Aug,Sep, Oct. What am I doing wrong in my load script?
PPJ_ID | UNITS OF WORK | TOTAL MONTHS | TOTAL UNITS | NUMBER_OF_DAYS | PHASE_ID | PHASE_START | PHASE_END | PHASE DURATION | UNIT_IN_PHASE | MONTH_YEAR | |
140056 | 40 | 14 | 40 | 141 | 1 | 24/06/2016 | 24/06/2016 | 0 | 0 | Jun 2016 | - 0.67
|
2 | 24/06/2016 | 03/07/2016 | - 7.05
| 2 | Jul 2016 | - 3.50
|
3 | 03/07/2016 | 12/08/2016 | - 28.2
| 12 | Aug 2016 | - 5.20
|
4 | 12/08/2016 | 11/10/2016 | - 42.3
| 14 | Sep 2016 | - 4.67
|
Oct 2016 | - 4.00
|
5 | 11/10/2016 | 10/12/2016 | - 42.3
| 10 | Nov 2016 | - 3.33
|
Dec 2016 | - 2.40
|
6 | 10/12/2016 | 09/01/2017 | - 21.15
| 2 | Jan 2017 | - 0.67
|
PACKAGING_WORKLOAD_DTL:
LOAD *,
date(PHASE_END,'DD/MM/YYYY') - date(PHASE_START,'DD/MM/YYYY') as Days,
MonthName(date(PHASE_END,'DD/MM/YYYY')) as EndMonthYear,
MonthName(date(PHASE_START,'DD/MM/YYYY')) as StartMonthYear;
LOAD "ACTUAL_DURATION",
"CREATE_DATE",
"DEVELOPER" as [DEVELOPER_DTL] ,
trim(date(date(END_DATE,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD-MMM-YYYY') ) AS [END_DATE_DTL],
"FISCAL_YEAR",
"NUMBER_OF_DAYS",
date(PHASE_END,'DD/MM/YYYY') AS [PHASE_END],
Month(date(PHASE_END,'DD/MM/YYYY')) AS PHASE_END_MONTH,
Year(date(PHASE_END,'DD/MM/YYYY')) AS PHASE_END_YEAR,
"PHASE_ID",
date(PHASE_START,'DD/MM/YYYY') AS [PHASE_START],
//date(PHASE_START,'DD/MM/YYYY') AS [PHASE_START_MR],
Month(date(PHASE_START,'DD/MM/YYYY')) AS PHASE_START_MONTH,
Year(date(PHASE_START,'DD/MM/YYYY')) AS PHASE_START_YEAR,
"PPJ_ID",
"RATING" as [RATING_DTL],
trim(date(date(SHIP_DATE,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD-MMM-YYYY') ) AS [SHIP_DATE_DTL],
date(PHASE_START,'DD/MM/YYYY') AS [START_DATE_DTL],
"UNIT_IN_PHASE",
"UNITS_IN_PHASE_PER_WEEK",
"UNITS_OF_WORK" as [UNITS_OF_WORK_DTL],
(Year([PHASE_END])*12 + Month([PHASE_END]))-(Year([PHASE_START])*12 + Month([PHASE_START])) AS MONTHS_IN_PHASE
FROM
Z:\SCI\GSC\C2M\DEV\Packaging\Management Reports\QVD Generator\PACKAGING_WORKLOAD_DTL_QVD_GENERATOR_DEV.qvd (qvd)
Where FISCAL_YEAR >='FY12' and FISCAL_YEAR < 'FY20';
FinalTable:
LOAD PPJ_ID,
PHASE_ID,
MonthName(AddMonths(StartMonthYear, IterNo() -1 )) as MonthYear
Resident PACKAGING_WORKLOAD_DTL
While AddMonths(StartMonthYear, IterNo() -1 ) <= EndMonthYear;
