How to pick up the second date from the data

[udaya_kumar]

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)

[kaushiknsolanki]

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

[udaya_kumar]

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;

[kaushiknsolanki]

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