×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need to update with data from next record
3

thread183-1803403

Need to update with data from next record

Need to update with data from next record

(OP)
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.

RE: Need to update with data from next record

CODE

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

Borislav Borissov
VFP9 SP2, SQL Server

RE: Need to update with data from next record

(OP)

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.

RE: Need to update with data from next record

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Need to update with data from next record

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

Borislav Borissov
VFP9 SP2, SQL Server

RE: Need to update with data from next record

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
https://www.doschke.name

RE: Need to update with data from next record

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close