Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to update with data from next record 3

Status
Not open for further replies.

briangriffin

Programmer
Nov 6, 2008
878
US
Dataset looks like this:

Code:
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:

Code:
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.
 
Code:
SELECT Account
     , MIN(OrderDate) AS OrderDate
     , MAX(OrderDate) AS NextDate
FROM YourTable
GROUP BY Account

Borislav Borissov
VFP9 SP2, SQL Server
 
Code:
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.
 
this should do it

Code:
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

FAQ219-2884
FAQ181-2886
 
OK,
How you know which record is first and which is Next?

Borislav Borissov
VFP9 SP2, SQL Server
 
You should use window functions, see LAG() and LEAD().

Code:
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
 
Got this working, thanks to all. The LEAD function turned out to be the simplest way to get what I needed.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top