Urgent Sql Help

[pythonic]

 

I would write a scalar function if you are using TSql:

 

And, just update the above table,

           

                            Update table_Name  Set JOB_DURATION = dbo.GetDuration(JOB_START_DT)

                                                       

 

Create Scalar function,

 

 CREATE FUNCTION [dbo].[GetDuration]

 ( @JobStartDate Datetime)

 RETURNS Duration Varchar(200)

 AS

BEGIN

DECLARE @Duration = varchar(250)

DECLARE @PastDuration as Datetime

 

SET @PastDuration = (SELECT TOP(1) JOB_START_DT from table_Name Where JOB_START_DT < @JobStartDate ORDER BY JOB_START_DT DESC)

 

IF(@Duration is NULL)

BEGIN

SET @Duration = Convert(Varchar(4), YEAR(@JobStartDate)) + ' - Current Date'  

END

ELSE

BEGIN

SET @Duration =  Convert(Varchar(4), YEAR(@PastDuration)) + ' - ' + Convert(Varchar(4), YEAR(@JobStartDate))  

END

RETURN @Duration

END

 

 

chala rojulu sql queries rasi ORMs vachaka :)....edhi PL/SQL annukunta...Non-Oracle db ki work avvudha?

[Silver_mani]

 

;WITH A AS (

 

SELECT

PERSON_ID,

Company,

Job_Start_Dt,

ROW_NUMBER() OVER (ORDER BY Job_Start_Dt) AS rn

 

FROM     PERSON_JOB_DATA )

 

SELECT

 

 A1.PERSON_ID, 

 A1.Company,

 A1.Job_Start_Dt,

CONVERT(VARCHAR,YEAR(A1.Job_Start_Dt))+ '-'+ CONVERT(VARCHAR,YEAR(A1.Job_Start_Dt)) AS Job_Duration

    

FROM  

A A1 JOIN A A2 ON A1.rn + 1 = A2.rn

 

ORDER BY A1.Company

 

:police:

[pythonic]

just my 2 cents...evaina efficient chesthe better....chala rojulu ayindi so might be BS too :)

 

 

 

select p1.pid, p1.start_date, min(p2.start_date)-1 as end_date

from person_job_data p1, person_job_data p2

where p1.pid = p2.pid and p2.start_date > p1.start_date

group by p1.pid, p1.start_date

 

UNION

 

select pid, max(start_date), 'present' as end_date

from person_job_data

goup by pid

 

[ranjith23]

just my 2 cents...evaina efficient chesthe better....chala rojulu ayindi so might be BS too :)

 

 

 

select p1.pid, p1.start_date, min(p2.start_date)-1 as end_date

from person_job_data p1, person_job_data p2

where p1.pid = p2.pid and p2.start_date > p1.start_date

group by p1.pid, p1.start_date

 

UNION

 

select pid, max(start_date), 'present' as end_date

from person_job_data

goup by pid

Thanks a lot bhaya. you rock

[ranjith23]

Thanks a lot guys for your time