RE: SQL - Current and Previous Status

Saw your note about wanting all orders. This should work.

with curr as (select ORDNUM currnum, max(ORDSEQ) currseq
from ORDERFIL
group by ORDNUM),
prev as (select ORDNUM prevnum, max(ORDSEQ) prevseq
from ORDERFIL
join curr on ORDNUM=currnum
where ORDSEQ<currseq
group by ORDNUM)
select x.ORDNUM, c.ORDSTAT, ifnull(p.ORDSTAT,' ')
from ORDERFIL x
join curr on x.ORDNUM=currnum
join ORDERFIL c on c.ORDNUM=x.ORDNUM and c.ORDSEQ=currseq
left outer join prev on x.ORDNUM=prevnum

left outer join ORDERFIL p on p.ORDNUM=x.ORDNUM and p.ORDSEQ=prevseq
where x.ORDSEQ=currseq

Richard

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, July 17, 2012 12:12 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL - Current and Previous Status

Thanks Richard...I'll check that out!

On Tue, Jul 17, 2012 at 12:01 PM, Richard Casey <casey_r@xxxxxxxxxxxxxxxx>
wrote:

No idea how efficient this is, but it appears to work!

with curr as (select ORDSTAT currstat, ORDSEQ currseq
from ORDERFIL
where ORDNUM=xxxxx
order by ORDSEQ desc
fetch first 1 rows only),
prev as (select ORDSTAT prevstat, ORDSEQ prevseq
from ORDERFIL
where ORDNUM=xxxxx and
ORDSEQ<(select currseq from curr)
order by ORDSEQ desc
fetch first 1 rows only) select ORDNUM, currstat,
ifnull(prevstat,' ') from ORDERFIL join curr on 1=1 left outer join
prev on 1=1 where ORDNUM=xxxxx and ORDSEQ=currseq

Replace xxxxx with your desired order number.

Is there a better way to get info from a single record table than
"join xxx on 1=1"?

Have fun!

Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, July 17, 2012 11:07 AM
To: Midrange Systems Technical Discussion
Subject: SQL - Current and Previous Status

Ok...I have a file (ORDERFIL) that has an order number (ORDNUM), a
status (ORDSTAT), and a sequence number (ORDSEQ). The sequence number
is just a one up number - it's not the row number, and it's not related to

an order.

However, it will be in order by status for an order. For a given order
number, I want to get the current status and the previous status. I
was thinking along the lines of a select work order, and then another
select (temporary table?) where I select for that order number and
order by sequence number descending. Does this make sense? Any ideas?

ORDNUM ORDSTAT ORDSEQ
12345 Ship 15
23456 Build 18
12345 Build 13
34567 Process 12
23456 Process 11
12345 Design 05

So I would want to retrieve 12345, Ship, Build; 23456, Build, Process;
34567, Process.

TIA!
--

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.




****************************************************************************
********
This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals & computer
viruses.
****************************************************************************
********



-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2197 / Virus Database: 2437/5136 - Release Date: 07/16/12