ranjith23 Posted September 9, 2014 Report Posted September 9, 2014 Hello Bhaya's, i have a table PERSON_JOB_DATA and below is its output. PERSON_ID COMPANY JOB_START_DT JOB_DURATION E2323 A 2007-02-23 2007- Current date E2323 B 2005-08-09 2005-2007 E2323 C 2004-03-09 2004-2005 E2323 D 1998-09-29 1998-2004 E2323 E 1998-02-09 1998-1998 E2323 F 1992-08-10 1992-1998 I have a column(JOB_DURATION) with no data. I want it to be displayed like highlighted above. Thanks in advance.
ZuniorVentiyar Posted September 9, 2014 Report Posted September 9, 2014 ardam kaleee Hello Bhaya's, i have a table PERSON_JOB_DATA and below is its output. PERSON_ID COMPANY JOB_START_DT JOB_DURATION E2323 A 2007-02-23 2007- Current date E2323 B 2005-08-09 2005-2007 E2323 C 2004-03-09 2004-2005 E2323 D 1998-09-29 1998-2004 E2323 E 1998-02-09 1998-1998 E2323 F 1992-08-10 1992-1996 I have a column(JOB_DURATION) with no data. I want it to be displayed like highlighted above. Thanks in advance.
puli_keka Posted September 9, 2014 Report Posted September 9, 2014 job enddate ekkada nundi vasthundi raja.. current aa leka job ended aa ani
Silver_mani Posted September 9, 2014 Report Posted September 9, 2014 Hello Bhaya's, i have a table PERSON_JOB_DATA and below is its output. PERSON_ID COMPANY JOB_START_DT JOB_DURATION E2323 A 2007-02-23 2007- Current date E2323 B 2005-08-09 2005-2007 E2323 C 2004-03-09 2004-2005 E2323 D 1998-09-29 1998-2004 E2323 E 1998-02-09 1998-1998 E2323 F 1992-08-10 1992-1998 I have a column(JOB_DURATION) with no data. I want it to be displayed like highlighted above. Thanks in advance. hmmm try chestunna bro ..koncham tricky sql lo ... Do u have any ETL tools ??
ranjith23 Posted September 9, 2014 Author Report Posted September 9, 2014 ardam kaleee Bhaya my lead told me to add a new column "JOB_DURATION", which should contain his work experience duration.
150bryant Posted September 9, 2014 Report Posted September 9, 2014 Start dt undhi ok..wer is end dt ?
Bairagi From Bapatla Posted September 9, 2014 Report Posted September 9, 2014 Bhaya my lead told me to add a new column "JOB_DURATION", which should contain his work experience duration. start date ichav end date kooda unte nee kada duration cheppedhi... lekapothe anni current ee avuthai...
ranjith23 Posted September 9, 2014 Author Report Posted September 9, 2014 job enddate ekkada nundi vasthundi raja.. current aa leka job ended aa ani company start Bhaya for this 2 rows E2323 E 1998-02-09 1998-1998 E2323 F 1992-08-10 1992-1998 1992-1998: 1992 IS year(JOB_START_DT) of company F 1998 IS year(JOB_START_DT) of company E..... so his experience for his 1st job is 6 years approx.
ranjith23 Posted September 9, 2014 Author Report Posted September 9, 2014 start date ichav end date kooda unte nee kada duration cheppedhi... lekapothe anni current ee avuthai... Bhyaa we need only YEAR. So, End date is start date of new job
Bairagi From Bapatla Posted September 9, 2014 Report Posted September 9, 2014 Bhaya for this 2 rows E2323 E 1998-02-09 1998-1998 E2323 F 1992-08-10 1992-1998 1992-1998: 1992 IS year(JOB_START_DT) of company F 1996 IS year(JOB_START_DT) of company E..... so his experience for his 1st job is 6 years approx. ohh person ID and start date of new job ni batti cheyala got it man... Will try...
ZuniorVentiyar Posted September 9, 2014 Report Posted September 9, 2014 got it ... I am not a SQL dev ... edo java lo brathukuthaa ... evadina ilanti work isthy oka java programm rastha or PL/SQL rasi avasaram ainappudu dhani call chesthaa :) Bhaya my lead told me to add a new column "JOB_DURATION", which should contain his work experience duration.
150bryant Posted September 9, 2014 Report Posted September 9, 2014 create two queries.. one to have min (dates) another to have Max (dates) then join these two to get your result job duration
ranjith23 Posted September 9, 2014 Author Report Posted September 9, 2014 Bhaya for this 2 rows E2323 E 1998-02-09 1998-1998 E2323 F 1992-08-10 1992-1998 1992-1998: 1992 IS year(JOB_START_DT) of company F 1998 IS year(JOB_START_DT) of company E..... so his experience for his 1st job is 6 years approx. Typo mistake its 1998 (not 1996). REQ ardam ayendi kabati all good.
Silver_mani Posted September 9, 2014 Report Posted September 9, 2014 To start with .......... Looks like ur key is PERSON_ID AND COMPANY . Assign a row no to detect the key break change .. you will assign a row no to each row in that group ... then some may be a self join over the table with row no and row no -1 might work ROW_NUMBER() OVER(PARTITION BY PERSON_ID , COMPANY ) AS Row
ranjith23 Posted September 9, 2014 Author Report Posted September 9, 2014 hmmm try chestunna bro ..koncham tricky sql lo ... Do u have any ETL tools ?? need to build a view bhaya in sql server.... thats my req
Recommended Posts