Create new table with start and end date columns from one column

[Pick]

Hi,

I have a table below also with a time column. I want to create a new table which will check every ID WITHIN every Order_No with two new columns showing the earliest Start data/time of status change for every ID WITHIN each Order_No and a column for the latest End date/time of status change for every ID WITHIN each order.

I also want the table to have Order_no, Phase, Control strategy columns. Need to ensure ORder_No is used as the ID can be the same within each Order_No Any help greatly appreciated.

Thanks

[Anonymous]

Hi. 
I dont understand very well your question. When u say "table which will check every ID WITHIN every Order_No" u want to check every ROW ?  Take a look at my first table

OR u want to get for every ID WITHIN every Order_No a date of the first status date and the last one - take a look at the table below

[Pick]

Hi, 

Thanks for the reply.  I want the table to look like the below.  So each Order_no has lots of different IDs with multiple rows for each status change for that ID so I need a new table (like below from the intiial one) which shows for each ID the first status date/time (Column A) and last Status date/time (Column B).

Note: table below didnt work as it returned the earliest and latest status change for the ID regardless of Order_No (Columns A and B are same for all orders)

  Thanks

[Anonymous]

I am sorry but i still cant see what u want to get. 
Could you please look at this table. 
A    12/12/2018 08:00:10
B     12/12/2018 08:00:16
C     12/12/2018  08:00:12
As i understand u want to get as result the next one table : 
A   (12/12/2018 08:00:10)  (12/12/2018 08:00:16)
B   (12/12/2018 08:00:16)   (12/12/2018 08:00:12)
So as we see and as i understand the starting date of the next Phase is the lasting date for the previous Phase
I join u a table with my results for your example as well

[Pick]

Hi,

From the table in your message I would like the following result:

ID       Order_No    Phase        Control Strategy          (fearliest Status Date/Time)     (Latest Status Date/Time) 

456    12401685    CIPXferIn  LevelControlHot          27/11/2019 08:03:15                 27/11/2019 09:25:45

1100  12401685    CIPXferIn  LevelControlHot          27/11/2019 09:24:00                27/11/2019 09:25:45

and so on for all IDs with same order_No

In you table the Phase is the status, the new table does not need the status just the earliest and latest status datetimw for each phase.

  Thanks for your help its driving me nuts

[Anonymous]

Hope somebody will join us. as i am not sure to understand completely your issue
I join u two tables. A right one i just copied from your first screenshot. The left one is the result that i found accordingly to what that i understand u want. There is no Phase and Control Strategy as i think it can be added later if first step with dates is validated
In my table the logic is the next : starting date is the min date for each group of ID
Latest date : latest date is the max date for next group of ID => so for 456.00 i will take the max date of 1,100.00 ID group and for 1,105.00 the max date is the max date of 1,105.00 ID group
P.S. if it is ok what i have done can u please write what Phase and Control strategy there should be.

[Pick]

Hi

  Thanks think thst nearly it, only change required it the latest date should be the latest status update i.e. for ID 456 start should be the earliest status datetime for ID 456 and the latest date should be the lastt status datetime for ID 456 (not 1100).  Starting date should always be before latest date, your ID 1100 seems to be other way round.

  Each ID has same Phase and control strategy for every status change or row.

  Thanks again

   Ian

[Anonymous]

That seems to be a bit more clear for me. So i join u two tables
On the right side u get a table that i copied from your screenshot ( your data source)
On the left my result 
So the logic is the next : for starting date i take a min date of each group of ID and for the last date i take the max date for each group of ID. SO : as for 456 there are 3 rows i will look through these rows to find max and min date. As for 1000 you have 5 rows i will look through these 5 rows to find its min and max date.

[Pick]

Yes that seems to be correct now great stuff

[Anonymous]

For that u can use this function : 
NOTE : 4ek is my table, you need replace it by your source table

ADDCOLUMNS(
SUMMARIZE('4ek';'4ek'[ID];'4ek'[Order_No]);
"mindate";CALCULATE(MINX('4ek';'4ek'[Update_At]);ALLEXCEPT('4ek';'4ek'[ID]));
"maxdate";CALCULATE(MAXX('4ek';'4ek'[Update_At]);ALLEXCEPT('4ek';'4ek'[ID]))
)

[Pick]

Hi,

  Thanks.  Just added it to my report.

Im probably not making my requirements clear.  It is very close what it is doing now is for an ID it is getting the earliest datetime and latest datetime for that ID in all orders.  It needs to return a row for each ID within an order and use the Order_no in the query so it doesnt get the earliest & latest datetime for the ID in all orders.

  Thanks for your help

[Anonymous]

If i understood you, we can have many order_no for one ID
Like this : 

ID  Order_no

456  123

456  123

456  345

456  345

If it's the cas  u can use this one : 

ADDCOLUMNS(SUMMARIZE('4ek';'4ek'[ID];'4ek'[Order_No]);"mindate";CALCULATE(MINX('4ek';'4ek'[Update_At]);ALLEXCEPT('4ek';'4ek'[ID];'4ek'[Order_No]));"maxdate";CALCULATE(MAXX('4ek';'4ek'[Update_At]);ALLEXCEPT('4ek';'4ek'[ID];'4ek'[Order_No])))

[Pick]

Hi,

  Sorry for late reply had a long weekend.

  There are many ID for one order.  All order_no are unique and have many IDs.  Different order_no will have similar IDs also which is the complicated but.  So for every ID within an order we need to return the earliest status and latest status date and time.

  Thanks for you help