Styjun

Array or vector? Two dimensional array or matrix?

What happens if a short can't be covered?

Why do airports remove/realign runways?

What purpose does mercury dichloride have in fireworks?

3-way switches no longer serving their purpose

What was the nature of the known bugs in the Space Shuttle software?

When is one 'Ready' to make Original Contributions to Mathematics?

How to reclaim personal item I've lent to the office without burning bridges?

Why am I getting unevenly-spread results when using $RANDOM?

Gory anime with pink haired girl escaping an asylum

What is this burst transmission sequence across the entire band?

What exactly is a "murder hobo"?

Is conquering your neighbors to fight a greater enemy a valid strategy?

How can I use my cell phone's light as a reading light?

The Apéry's constant and the Airy function

What does "frozen" mean (e.g. for catcodes)?

What are the consequences for a developed nation to not accept any refugees?

Calculate the largest palindromic number from the product of two 6-digit numbers (100000 to 999999)

Which is a better conductor, a very thick rubber wire or a very thin copper wire?

Name for an item that is out of tolerance or over a threshold

How do resistors generate different heat if we make the current fixed and changed the voltage and resistance? Notice the flow of charge is constant

Possibility to correct pitch from digital versions of records with the hole not centered

Will Jimmy fall off his platform?

Can a USB hub be used to access a drive from two devices?

Lead and partition function

Function vs. Stored Procedure in SQL ServerWhat are the options for storing hierarchical data in a relational database?Null Value Statementrecursive cte with ranking functionsConversion failed when converting date and/or time from character string while inserting datetimeDoes this SQL query create Temporary Tables?T-SQL Conditional Order ByDoes one need to create objects on top of a partition scheme?Select rows from 2 tables where the first 5 rows come from one table then 1 from the second tableGet a partitioned sum of a column in a fixed time window for each record

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

This is my input table

create table #table1 (id int, FN varchar(20), startdate varchar(20), id1 varchar)

insert #table1
 select 1, 'Joe', '2019-01-01', 'A'
 union select 1, 'Joe', '2019-01-01', 'B'
 union select 1, 'Joe', '2019-01-05', 'C'
 union select 1, 'Joe', '2019-01-05', 'D'
 union select 1, 'Joe', '2019-01-06', 'E'
 union select 2, 'john', '2019-01-05', 'F'
 union select 2, 'john', '2019-01-06', 'G'
 union select 2, 'john', '2019-01-06', 'H'
 union select 2, 'john', '2019-01-07', 'I'

I tried the following code

 select *
 , dense_rank() OVER (partition by id, fn order by startdate) 
 , lead(startdate,1) OVER (partition by id, fn order by startdate) 
 from #table1
 order by id

But I require the following output:

sql sql-server tsql

share|improve this question

edited Mar 25 at 22:40

Hadi

27.1k88 gold badges3232 silver badges7676 bronze badges

asked Mar 25 at 21:38

Venkata Jagadish PippallaVenkata Jagadish Pippalla

2066 bronze badges

add a comment | 

1

This is my input table

create table #table1 (id int, FN varchar(20), startdate varchar(20), id1 varchar)

insert #table1
 select 1, 'Joe', '2019-01-01', 'A'
 union select 1, 'Joe', '2019-01-01', 'B'
 union select 1, 'Joe', '2019-01-05', 'C'
 union select 1, 'Joe', '2019-01-05', 'D'
 union select 1, 'Joe', '2019-01-06', 'E'
 union select 2, 'john', '2019-01-05', 'F'
 union select 2, 'john', '2019-01-06', 'G'
 union select 2, 'john', '2019-01-06', 'H'
 union select 2, 'john', '2019-01-07', 'I'

I tried the following code

 select *
 , dense_rank() OVER (partition by id, fn order by startdate) 
 , lead(startdate,1) OVER (partition by id, fn order by startdate) 
 from #table1
 order by id

But I require the following output:

sql sql-server tsql

share|improve this question

edited Mar 25 at 22:40

Hadi

27.1k88 gold badges3232 silver badges7676 bronze badges

asked Mar 25 at 21:38

Venkata Jagadish PippallaVenkata Jagadish Pippalla

2066 bronze badges

add a comment | 

This is my input table

create table #table1 (id int, FN varchar(20), startdate varchar(20), id1 varchar)

insert #table1
 select 1, 'Joe', '2019-01-01', 'A'
 union select 1, 'Joe', '2019-01-01', 'B'
 union select 1, 'Joe', '2019-01-05', 'C'
 union select 1, 'Joe', '2019-01-05', 'D'
 union select 1, 'Joe', '2019-01-06', 'E'
 union select 2, 'john', '2019-01-05', 'F'
 union select 2, 'john', '2019-01-06', 'G'
 union select 2, 'john', '2019-01-06', 'H'
 union select 2, 'john', '2019-01-07', 'I'

I tried the following code

 select *
 , dense_rank() OVER (partition by id, fn order by startdate) 
 , lead(startdate,1) OVER (partition by id, fn order by startdate) 
 from #table1
 order by id

But I require the following output:

sql sql-server tsql

share|improve this question

edited Mar 25 at 22:40

Hadi

27.1k88 gold badges3232 silver badges7676 bronze badges

asked Mar 25 at 21:38

Venkata Jagadish PippallaVenkata Jagadish Pippalla

2066 bronze badges

create table #table1 (id int, FN varchar(20), startdate varchar(20), id1 varchar)

insert #table1
 select 1, 'Joe', '2019-01-01', 'A'
 union select 1, 'Joe', '2019-01-01', 'B'
 union select 1, 'Joe', '2019-01-05', 'C'
 union select 1, 'Joe', '2019-01-05', 'D'
 union select 1, 'Joe', '2019-01-06', 'E'
 union select 2, 'john', '2019-01-05', 'F'
 union select 2, 'john', '2019-01-06', 'G'
 union select 2, 'john', '2019-01-06', 'H'
 union select 2, 'john', '2019-01-07', 'I'

 select *
 , dense_rank() OVER (partition by id, fn order by startdate) 
 , lead(startdate,1) OVER (partition by id, fn order by startdate) 
 from #table1
 order by id

sql sql-server tsql

sql sql-server tsql

share|improve this question

edited Mar 25 at 22:40

Hadi

27.1k88 gold badges3232 silver badges7676 bronze badges

asked Mar 25 at 21:38

Venkata Jagadish PippallaVenkata Jagadish Pippalla

2066 bronze badges

share|improve this question

edited Mar 25 at 22:40

Hadi

27.1k88 gold badges3232 silver badges7676 bronze badges

asked Mar 25 at 21:38

Venkata Jagadish PippallaVenkata Jagadish Pippalla

2066 bronze badges

edited Mar 25 at 22:40

Hadi

27.1k88 gold badges3232 silver badges7676 bronze badges

edited Mar 25 at 22:40

Hadi

27.1k88 gold badges3232 silver badges7676 bronze badges

asked Mar 25 at 21:38

Venkata Jagadish PippallaVenkata Jagadish Pippalla

2066 bronze badges

asked Mar 25 at 21:38

Venkata Jagadish PippallaVenkata Jagadish Pippalla

2066 bronze badges

1 Answer
1

active

oldest

votes

1

I know that there might be a better approach but a least this is a working solution:

select *, 
 (select MIN(startdate) 
 from #table1 t1 
 where t1.id = #table1.id and 
 t1.fn = #table1.fn and 
 t1.startdate > #table1.startdate) enddate
from #table1

Result

share|improve this answer

answered Mar 25 at 22:39

HadiHadi

27.1k88 gold badges3232 silver badges7676 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you @hadi
  
  – Venkata Jagadish Pippalla
  Mar 25 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VenkataJagadishPippalla if this answer solved the issue you have to accept it by clicking on the mark below the voting arrows
  
  – Hadi
  Mar 26 at 5:25
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55346803%2flead-and-partition-function%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

I know that there might be a better approach but a least this is a working solution:

select *, 
 (select MIN(startdate) 
 from #table1 t1 
 where t1.id = #table1.id and 
 t1.fn = #table1.fn and 
 t1.startdate > #table1.startdate) enddate
from #table1

Result

share|improve this answer

answered Mar 25 at 22:39

HadiHadi

27.1k88 gold badges3232 silver badges7676 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you @hadi
  
  – Venkata Jagadish Pippalla
  Mar 25 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VenkataJagadishPippalla if this answer solved the issue you have to accept it by clicking on the mark below the voting arrows
  
  – Hadi
  Mar 26 at 5:25
  

  
  
  
  

add a comment | 

1

I know that there might be a better approach but a least this is a working solution:

select *, 
 (select MIN(startdate) 
 from #table1 t1 
 where t1.id = #table1.id and 
 t1.fn = #table1.fn and 
 t1.startdate > #table1.startdate) enddate
from #table1

Result

share|improve this answer

answered Mar 25 at 22:39

HadiHadi

27.1k88 gold badges3232 silver badges7676 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you @hadi
  
  – Venkata Jagadish Pippalla
  Mar 25 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @VenkataJagadishPippalla if this answer solved the issue you have to accept it by clicking on the mark below the voting arrows
  
  – Hadi
  Mar 26 at 5:25
  

  
  
  
  

add a comment | 

I know that there might be a better approach but a least this is a working solution:

select *, 
 (select MIN(startdate) 
 from #table1 t1 
 where t1.id = #table1.id and 
 t1.fn = #table1.fn and 
 t1.startdate > #table1.startdate) enddate
from #table1

Result

share|improve this answer

answered Mar 25 at 22:39

HadiHadi

27.1k88 gold badges3232 silver badges7676 bronze badges

select *, 
 (select MIN(startdate) 
 from #table1 t1 
 where t1.id = #table1.id and 
 t1.fn = #table1.fn and 
 t1.startdate > #table1.startdate) enddate
from #table1

share|improve this answer

answered Mar 25 at 22:39

HadiHadi

27.1k88 gold badges3232 silver badges7676 bronze badges

answered Mar 25 at 22:39

HadiHadi

27.1k88 gold badges3232 silver badges7676 bronze badges

answered Mar 25 at 22:39

HadiHadi

27.1k88 gold badges3232 silver badges7676 bronze badges