Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data like this
Ticket | Task | Created Date | Last modified Date | Created By |
---|---|---|---|---|
1 | 1 | 1-Jan-2017 10:00 | 1-Jan-2017 10:15 | Admin |
1 | 2 | 1-Jan-2017 12:00 | 1-Jan-2017 12:05 | Agent |
1 | 3 | 2-Jan-2017 10:05 | 2-Jan-2017 10:06 | Agent |
2 | 4 | 1-Jan-2017 11:00 | 1-Jan-2017 11:15 | Admin |
2 | 5 | 1-Jan-2017 13:00 | 1:Jan-2017 13:05 | Agent |
2 | 6 | 2-Jan-2017 10:05 | 2-Jan-2017 10:06 | Agent |
3 | 7 | 2-Jan-2017 10:05 | 2-Jan-2017 10:05 | Admin |
3 | 8 | 2-Jan-2017 10:05 | 2-Jan-2017 10:05 | Agent |
Here the data is grouped by Ticket, i have to pick the start date and end date from this data.
Start date will be the Created Date (which is created by Admin)
For example, Ticket = 1, the Start Date is 1-Jan-2017 10:00 (Task created by Admin)
End date will be the Last Modified Date (The Last modified date after the first task (Admin task) should be considered).
For example, Ticket = 1, The end date is 1-Jan-2017 12:05.
My question is, How to pick up the last modified date from the date which is the task created (Task after the admin task, i.e., task created by some agent) after the first task (Which is created by admin)
Try this,
Data:
Load Ticket,Min(CreatedDate) as MinDate
From xyz
Where Createdby='Admin'
Group by Ticket;
Left join
Load Ticket,Max(LastModifiedDate) as MaxDate
From xyz
Where Createdby<>'Admin'
Group by Ticket;
Regards,
Kaushik Solanki
Hi,
If i use the below code, then it will take the last row date in the last modified date field group by Ticket right,
But i want the row after the admin task date to be picked up in the script.
Left join
Load Ticket,Max(LastModifiedDate) as MaxDate
From xyz
Where Createdby<>'Admin'
Group by Ticket;
Thats fine,
In such case you can use the Min function, which will take the immediate next row after Admin row.
Left join
Load Ticket,Min(LastModifiedDate) as NextDate
From xyz
Where Createdby<>'Admin'
Group by Ticket;
Regards,
Kaushik Solanki