how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID

[srinivaschary]

Hi,

I have given sample data set below. this table have employeeid,doj and updated date. from this table i want start_date and End_date using DOJ and updated date group by employeeID.

SAMPLE DATA
Empid	JobId	DepId	Fin_Loc	Doj	updated Date
1	212	I5	21	25/06/17	25/07/17
1	312	B7	34	25/06/17	25/05/17
1	456	R3	55	25/06/17	25/06/17
1	234	B4	65	25/06/17	25/06/18
2	245	N	78	25/06/16	25/06/18
3	343	N5	56	25/06/15	02/03/14
3	788	K4	56	25/06/15	22/03/15
3	567	T5	44	25/06/15	22/03/15
4	788	K4	56	25/06/15	22/03/15

Expected answer

EXPECTED DATA
Empid	JobId	DepId	Fin_Loc	Doj	updated Date	Start_Date	End_Date
1	312	B7	34	25/01/17	25/05/17	25/01/17	25/05/17
1	456	R3	55	25/01/17	25/06/17	26/05/17	25/06/17
1	212	I5	21	25/01/17	25/07/17	26/06/17	25/07/17
1	234	B4	65	25/01/17	25/06/18	26/07/17	25/06/18
2	245	N	78	25/06/16	25/06/18	25/06/16	25/06/18
3	343	N5	56	25/06/15	02/03/16	25/06/15	02/03/16
3	788	K4	56	25/06/15	22/03/17	03/03/16	22/03/17
3	567	T5	44	25/06/15	22/03/18	23/03/17	22/03/18
4	788	K4	56	25/06/15	22/03/15	25/06/15	22/03/15

[PaigeMiller]

Explain how you derived the value of Start_Date for Empid=1.

[SuryaKiran]

Did you miss something to explain us?

If you want to compare previous row values to current row values then try using LAG() function. eg: Start_Date=LAG(End_Date)+1

[srinivaschary]

Sorry for late reply,

Actually what exatly i need based on employee id i need to create start date and end date using doj and updated date for eg below.

SAMPLE DATA
Empid	JobId	DepId	Fin_Loc	Doj	updated Date
1	212	I5	21	25/06/17	25/07/17
1	312	B7	34	25/06/17	25/05/17
1	456	R3	55	25/06/17	25/06/17
1	234	B4	65	25/06/17	25/06/18

EXPECTED DATA
Empid	JobId	DepId	Fin_Loc	Start_Date	End_Date
1	312	B7	34	25/01/17	25/05/17
1	456	R3	55	26/05/17	25/06/17
1	212	I5	21	26/06/17	25/07/17
1	234	B4	65	26/07/17	25/06/18

[andreas_lds]

So for JobId 456, 212 and 234 start-date is after end-date ... a bit weird.