RE: SQL: how to farm journal: select only oldest and newest record for each cust #

I think this does the trick!


select * from jrnfl
where joseqn in(
select min(joseqn)
from jrnfl
group by joctrr
union
select max(joseqn)
from jrnfl
group by joctrr)
order by joctrr

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Tuesday, October 09, 2012 2:54 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL: how to farm journal: select only oldest and newest record for each cust #

This returns a MIN column value and a MAX column value for each cust.

Is it possible to return the ENTIRE two RECORDs for each cust -both the first record and the last record?


Is this getting close to the right idea? (it has syntax errors, WHERE not expected).

select joctrr, min(joseqn), max(joseqn)
from jrnfl
group by joctrr
where min(joseqn) = joseqn
or max(joseqn) = joseqn



I have records such as:

Joseqn joctrr JOESD
101 5 Walmart Cust NYC, AA
102 5 Walmart Cust NYC, BB
103 5 Walmart Cust NYC, CC
104 5 Walmart Cust NYC, NY


I would like to have SQL return the entire record for JOSEQN = 101 (the min=the before image) and 105 (the max = the after image) for each unique joctrr.

Is this doable?

Thanks!







-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Friday, September 28, 2012 2:26 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: how to farm journal: select only oldest and newest record for each cust #

sure...

select custno, min(joseqn), max(joseqn)
from outfile
group by custno
where joobj = 'MYFILE'

gives you a min & max for every cust...

Note: only issue I see with this is if the joseqn gets reset...

You might consider using jotime instead of joseqn....but jotime has
it's own cavet: "The system cannot assure that the date stamp is
always in ascending order for sequential journal entries because you
can change the value of the system date." Also, jotime isn't
guarantee to be unique...but joseq is.

Charles

On Fri, Sep 28, 2012 at 2:54 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

Thanks Vern

Your response sounds like I could select one cust change pair of journal records.

Can the first select get ALL the cust recs with the lowest time-stamp?

For example, cust A has 10 journal records and cust B has 2 journal records.

Can I build an SQL stmt that will return a min for cust A and also a min for cust B - in one SQL stmt?



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Friday, September 28, 2012 12:09 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: how to farm journal: select only oldest and newest record for each cust #

Hi Joel

A quick thought - use a UNION SELECT. This assumes you have some
criteria for the particular set of records - the first SELECT would get
the record with some minimum timestamp, the second SELECT would get the
record with some maximum timestamp.

I'm just flying off the cuff with this - not sure it'd work at all -
depends on how the data comes out. It'll work in a general sense, though.

Vern

On 9/28/2012 10:32 AM, Stone, Joel wrote:

Is it possible (and simple) to select only two records from the journal - for each file-RRN combination?


Example:

Users make 20 changes throughout the day to CUST #123 record.

I want to select only the first "before" image and the last "after" image for that particular cust (and all other custs).

Can SQL select these with a simple statement?

Or is this best done in an HLL pgm?


Thanks



______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

--
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 inbound email has been scanned for all viruses by the MessageLabs SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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.