Need to update with data from next record 3

[briangriffin]

Dataset looks like this:

Account     OrderDate  
100          5/1/2020
100          5/5/2020
100          6/1/2020
200         6/1/2020
200         6/2/2020
200         6/7/2020

I need the first order only, plus the order date of the next order, and discard the other records:

Account     OrderDate    NextOrderDate
100          5/1/2020      5/5/2020
200          6/1/2020      6/2/2020

I've been all over with this, but can't quite get it. Any help is greatly appreciated.

 

[bborissov]

SELECT Account
     , MIN(OrderDate) AS OrderDate
     , MAX(OrderDate) AS NextDate
FROM YourTable
GROUP BY Account

Borislav Borissov
VFP9 SP2, SQL Server

 

[briangriffin]

declare @v_test table
(Account varchar(6),
OrderDate date)

insert into @v_test values (100,'5/1/2020')
insert into @v_test values (100,'5/5/2020')
insert into @v_test values (100,'6/1/2020')
insert into @v_test values (200,'6/1/2020')
insert into @v_test values (200,'6/2/2020')
insert into @v_test values (200,'6/7/2020')

select * from @v_test

select
Account,
min(OrderDate) as OrderDate,
max(OrderDate) as NextDate

from @v_test

group by Account

This gives 6/1 as the next order for account 100, and 6/7 as the next order for account 200. This is ignoring the second order for each account, which is the order date that I need. Thanks.

 

[fredericofonseca]

this should do it

declare @v_test table
(Account varchar(6),
OrderDate date)

insert into @v_test values (100,'5/1/2020')
insert into @v_test values (100,'5/5/2020')
insert into @v_test values (100,'6/1/2020')
insert into @v_test values (200,'6/1/2020')
insert into @v_test values (200,'6/2/2020')
insert into @v_test values (200,'6/7/2020')

select * from @v_test

select Account
     , max(case when rownum = 1 then OrderDate else null end) as OrderDate
     , max(case when rownum = 2 then OrderDate else null end) as NextDate

from (select *
           , row_number() over(partition by Account
                                   order by OrderDate
                              ) as rownum
      from @v_test
     ) t1
where rownum < 3
group by Account

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bborissov]

OK,
How you know which record is first and which is Next?

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

You should use window functions, see LAG() and LEAD().

Declare @accounts as table (Account int);
Declare @orders as table (Account int, OrderDate date);

insert into @accounts values (100);
insert into @accounts values (200);
insert into @accounts values (300);
insert into @accounts values (400);

insert into @orders values (100,'20200501');
insert into @orders values (100,'20200505');
insert into @orders values (100,'20200601');
insert into @orders values (200,'20200601');
insert into @orders values (200,'20200602');
insert into @orders values (200,'20200607');
insert into @orders values (300,'20200301');

Select Account, FirstOrderDate, NextOrderDate  
from (Select acc.Account, OrderDate as FirstOrderDate
     , Lead(OrderDate) over(partition by acc.Account order by OrderDate)  as NextOrderDate 
     , row_number() over(partition by acc.Account order by OrderDate)  as rownum 
from @accounts as acc 
left join @orders ord on acc.account=ord.account) as orderpairs
where rownum=1
order by Account

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[briangriffin]

Got this working, thanks to all. The LEAD function turned out to be the simplest way to get what I needed.