Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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 |
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?
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;
Thank you dear for your help! all well for now..