Jump to content

Urgent Sql Help

ranjith23

By ranjith23
in Old Discussions

 Share

Recommended Posts

ramu Sr. Hero

ramu

Posted
Posted

Lead lag function use cheyyali bhayya... to get next record value...
example ki 1992 - 1998.. ee 1998 value lead function use chesi tevali...try it

  • Replies 34
  • Created
  • Last Reply

Top Posters In This Topic

  • ranjith23

    11

  • Silver_mani

    7

  • ramu

    3

  • pythonic

    3

Popular Days

Top Posters In This Topic

Popular Days

Silver_mani LEGEND

Silver_mani

Posted
Posted

create two queries..

one to have min (dates)

 

another to have Max (dates)

 

then join these two to get your result job duration

work avvadu raaja 

kranthi111 Jr Artist

kranthi111

Posted
Posted 
create table #temp
( person_id nvarchar(max),
	company varchar(2),
	job_start_dt date,
	job_duration nvarchar(max))
	
	insert into #temp values('e2323','A',Cast(getdate()-365 as date),'')
	
	insert into #temp values('e2323','B',Cast(getdate()-750 as date),'')
	
	insert into #temp values('e2323','C',Cast(getdate()-1300 as date),'')
	insert into #temp values('e2323','D',Cast(getdate()-1800 as date),'')
	
	insert into #temp values('e2323','E',Cast(getdate()-2300 as date),'')
	insert into #temp values('e2323','F',Cast(getdate()-2700 as date),'')

	SELECT * FROM #TEMP
	select *,
		Case when cast(Year(lead(job_Start_dt,1) OVER (ORDER BY job_Start_dt)) as varchar) is null 
			then cast(YEAR(job_start_dt) as varchar) + '-' + 'Current Date' else
		cast(YEAR(job_start_dt) as varchar) + '-' +cast(Year(lead(job_Start_dt,1) OVER (ORDER BY job_Start_dt)) as varchar)  end as Duration from #temp
	order by job_start_dt desc
Silver_mani LEGEND

Silver_mani

Posted
Posted

Lead lag function use cheyyali bhayya... to get next record value...
example ki 1992 - 1998.. ee 1998 value lead function use chesi tevali...try it

idendi bhayya ??

ranjith23 Side Hero

ranjith23

Posted
  • Author
Posted

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

Bhaya PERSON_ID,COMPANY and START_DATE all 3 are key fields. since an employee can switch to an different department in same company. 

 

koncham motham sql chepava. i tried  a lot. was not able to execute

ramu Sr. Hero

ramu

Posted
Posted

idendi bhayya ??

lead function bhayya... google lo oracle lead function kotti chudu....example kuda ichadu
Silver_mani LEGEND

Silver_mani

Posted
Posted 
create table #temp
( person_id nvarchar(max),
	company varchar(2),
	job_start_dt date,
	job_duration nvarchar(max))
	
	insert into #temp values('e2323','A',Cast(getdate()-365 as date),'')
	
	insert into #temp values('e2323','B',Cast(getdate()-750 as date),'')
	
	insert into #temp values('e2323','C',Cast(getdate()-1300 as date),'')
	insert into #temp values('e2323','D',Cast(getdate()-1800 as date),'')
	
	insert into #temp values('e2323','E',Cast(getdate()-2300 as date),'')
	insert into #temp values('e2323','F',Cast(getdate()-2700 as date),'')

	SELECT * FROM #TEMP
	select *,
		Case when cast(Year(lead(job_Start_dt,1) OVER (ORDER BY job_Start_dt)) as varchar) is null 
			then cast(YEAR(job_start_dt) as varchar) + '-' + 'Current Date' else
		cast(YEAR(job_start_dt) as varchar) + '-' +cast(Year(lead(job_Start_dt,1) OVER (ORDER BY job_Start_dt)) as varchar)  end as Duration from #temp
	order by job_start_dt desc

gp mama 

Silver_mani LEGEND

Silver_mani

Posted
Posted

Bhaya PERSON_ID,COMPANY and START_DATE all 3 are key fields. since an employee can switch to an different department in same company. 

 

koncham motham sql chepava. i tried  a lot. was not able to execute

 

 

kranthi post chesadu chudu mama  .. . direct functions vunnayi anta ..no nead for joins and all

ramu Sr. Hero

ramu

Posted
Posted

Select id, jobdate,lead(jobdate,1) over(order by jobdate) as nxtjobdate from yourtable;

Idi use chesi...aa dates lo year teesi job duration column ready cheyyi

ranjith23 Side Hero

ranjith23

Posted
  • Author
Posted 
create table #temp
( person_id nvarchar(max),
	company varchar(2),
	job_start_dt date,
	job_duration nvarchar(max))
	
	insert into #temp values('e2323','A',Cast(getdate()-365 as date),'')
	
	insert into #temp values('e2323','B',Cast(getdate()-750 as date),'')
	
	insert into #temp values('e2323','C',Cast(getdate()-1300 as date),'')
	insert into #temp values('e2323','D',Cast(getdate()-1800 as date),'')
	
	insert into #temp values('e2323','E',Cast(getdate()-2300 as date),'')
	insert into #temp values('e2323','F',Cast(getdate()-2700 as date),'')

	SELECT * FROM #TEMP
	select *,
		Case when cast(Year(lead(job_Start_dt,1) OVER (ORDER BY job_Start_dt)) as varchar) is null 
			then cast(YEAR(job_start_dt) as varchar) + '-' + 'Current Date' else
		cast(YEAR(job_start_dt) as varchar) + '-' +cast(Year(lead(job_Start_dt,1) OVER (ORDER BY job_Start_dt)) as varchar)  end as Duration from #temp
	order by job_start_dt desc

Thanks bhaya for reply.

 

receiving error saying: 
 
Msg 195, Level 15, State 10, Line 2
'lead' is not a recognized built-in function name.
 

 

cavaliers Side Hero

cavaliers

Posted
Posted

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.

 

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
pythonic Celebrity

pythonic

Posted
Posted

 

 

I think lead function specific for oacle db annukunta....emi database?

kranthi111 Jr Artist

kranthi111

Posted
Posted

2012 SQL lo introduce chesar anukunta lead and lag.. nuv old sql server use chesthunte these functions are not available..

 

 

Suhaas Sr. Hero

Suhaas

Posted
Posted
;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
    
ranjith23 Side Hero

ranjith23

Posted
  • Author
Posted

I think lead function specific for oacle db annukunta....emi database?

yep. SQL server bhaya. Not Oracle. Lead dont work

 Share
Go to topic listing
×
×
  • Create New...