Add serial field to date range (day_1, day_2....etc.)

[mahmed63]

Hi,

I have SQL data as following lets name it Execution Plan data

Item Name represents the Items to be executed throughout the execution plan

Planned Date is the date we plan to execute particular item(s)

Planned Day is the day number where we plan to execute item(s)

Item Name	Planned Date	Planned Day
Item 1	10/01/2018	Day_1
Item 2	10/01/2018	Day_1
Item 3	11/01/2018	Day_2
Item 4	12/01/2018	Day_3
Item 5	13/01/2018	Day_4
Item 6	14/01/2018	Day_5

Below is the Actual Execution data where the Actual Date will be captured and populated by the SQL when the item(s) being executed

In some cases the Actual Date will be same/greater than the Planned Date which is OK.

Item Name	Planned Date	Planned Day	Actual Date
Item 1	10/01/2018	Day_1	10/01/2018
Item 2	10/01/2018	Day_1	11/01/2018
Item 3	11/01/2018	Day_2	11/01/2018
Item 4	12/01/2018	Day_3	11/01/2018
Item 5	13/01/2018	Day_4
Item 6	14/01/2018	Day_5	12/01/2018

Until now I have no issues/challenges...

My challenge is how to add Actual Day as day_1,day_2 ...etc. based on the Actual Date as expected below

Item Name	Planned Date	Planned Day	Actual Date	Actual Day
Item 1	10/01/2018	Day_1	10/01/2018	Day_1
Item 2	10/01/2018	Day_1	11/01/2018	Day_2
Item 3	11/01/2018	Day_2
Item 4	12/01/2018	Day_3	11/01/2018	Day_2
Item 6	14/01/2018	Day_5	12/01/2018	Day_3
Item 5	13/01/2018	Day_4	15/01/2018	Day_6

Expected final output:

To calculate the Execution Day State by comparing the Planned Day vs Actual Day

Item Name	Planned Date	Planned Day	Actual Date	Actual Day	Execution Day State
Item 1	10/01/2018	Day_1	10/01/2018	Day_1	As Planned
Item 2	10/01/2018	Day_1	11/01/2018	Day_2	Delayed by 1
Item 3	11/01/2018	Day_2			Missed (if today date greater than Planned Day (Day_2)
Item 4	12/01/2018	Day_3	11/01/2018	Day_2	Earlier by 1
Item 6	14/01/2018	Day_5	12/01/2018	Day_3	Earlier by 2
Item 5	13/01/2018	Day_4	15/01/2018	Day_6	Delayed by 2

Any idea?

[Nicole-Smith]

This load script:

Data:
LOAD [Item Name],
[Planned Date],
[Planned Day],
[Actual Date],
IF([Actual Date] > 0, 'Day_' & TEXT(SUBFIELD([Planned Day], '_', 2) + INTERVAL([Actual Date] - [Planned Date], 'D'))) AS [Actual Day],
IF([Actual Date] - [Planned Date] = 0, 'As Planned',
IF([Actual Date] - [Planned Date] > 0, 'Delayed by ' & INTERVAL([Actual Date] - [Planned Date], 'D'),
IF([Actual Date] - [Planned Date] < 0, 'Earlier by ' & FABS(INTERVAL([Actual Date] - [Planned Date], 'D')), 'Missed'))) AS [Execution Day State]
INLINE [
    Item Name, Planned Date, Planned Day, Actual Date
    Item 1, 10/01/2018, Day_1, 10/01/2018
    Item 2, 10/01/2018, Day_1, 11/01/2018
    Item 3, 11/01/2018, Day_2,
    Item 4, 12/01/2018, Day_3, 11/01/2018
    Item 6, 14/01/2018, Day_5, 12/01/2018
    Item 5, 13/01/2018, Day_4, 15/01/2018
];

Will return this output:

Item Name	Planned Date	Planned Day	Actual Date	Actual Day	Execution Day State
Item 1	10/01/2018	Day_1	10/01/2018	Day_1	As Planned
Item 2	10/01/2018	Day_1	11/01/2018	Day_2	Delayed by 1
Item 3	11/01/2018	Day_2			Missed
Item 4	12/01/2018	Day_3	11/01/2018	Day_2	Earlier by 1
Item 5	13/01/2018	Day_4	15/01/2018	Day_6	Delayed by 2
Item 6	14/01/2018	Day_5	12/01/2018	Day_3	Earlier by 2

[mahmed63]

Thanks Nicole ! it works well...However, I might missed a slide piece of info.

Planned data and the Actual Date are not in the same db/table... can we alter the script?

[Nicole-Smith · Accepted solution]

As long as item name is in both tables, you should be able to use a left join.  Something like:

Data:
SELECT [Item Name], [Planned Date], [Planned Day]
FROM Table1;

LEFT JOIN (Data)
SELECT [Item Name], [Actual Date]
FROM Table2;

LEFT JOIN (Data)
LOAD [Item Name],
     IF([Actual Date] > 0, 'Day_' & TEXT(SUBFIELD([Planned Day], '_', 2) + INTERVAL([Actual Date] - [Planned Date], 'D'))) AS [Actual Day], 
     IF([Actual Date] - [Planned Date] = 0, 'As Planned', 
          IF([Actual Date] - [Planned Date] > 0, 'Delayed by ' & INTERVAL([Actual Date] - [Planned Date], 'D'), 
          IF([Actual Date] - [Planned Date] < 0, 'Earlier by ' & FABS(INTERVAL([Actual Date] - [Planned Date], 'D')), 'Missed'))) AS [Execution Day State] 
RESIDENT Data;

[mahmed63]

Thank you dear for your help! all well for now..