Retrieve the Max Date and 2nd Max Date per ID
I have the need to retrieve the Max(invoice date) per customer. I also would
like to retrieve the second Max(invoice date) per customer. Following is the
SQL for the Max(invoice date) per customer...works well.
select a.stn_sld_cus_id, Max(a.stn_inv_dt) max_invoice_date
from sahstn_rec a
where stn_ord_pfx = 'SO' and a.stn_inv_dt < '04/01/2015'
group by stn_sld_cus_id;
The next SQL is what I have come up with for the second Max(invoice date) per
customer. This is not working consistently.
select a.stn_sld_cus_id, max(a.stn_inv_dt) max_inv_date
from sahstn_rec a
where stn_inv_dt not in
(select max(b.stn_inv_dt)
from sahstn_rec b
where stn_ord_pfx = 'SO' and
a.stn_sld_cus_id = b.stn_sld_cus_id and
a.stn_inv_dt < '04/01/2015'
group by stn_sld_cus_id)
and a.stn_inv_dt < '04/01/2015'
group by a.stn_sld_cus_id;
Any ideas?
Thank you
John
John,
Try this one:
select a.stn_sld_cus_id, Max(a.stn_inv_dt) max_invoice_date
from sahstn_rec a, (
select a.stn_sld_cus_id, Max(a.stn_inv_dt) max_invoice_date
from sahstn_rec c
where stn_ord_pfx = 'SO' and c.stn_inv_dt < '04/01/2015'
group by stn_sld_cus_id;
) b
where stn_ord_pfx = 'SO' and a.stn_inv_dt < '04/01/2015'
and a.stn_sld_cus_id = b.stn_sld_cus_id
and a.stn_inv_dt < b.max_invoice_date
group by stn_sld_cus_id;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, Apr 9, 2015 at 4:32 PM, JOHN KRUPA <jkrupa@turretsteel.com> wrote:
> I have the need to retrieve the Max(invoice date) per customer. I also
> would
> like to retrieve the second Max(invoice date) per customer. Following is
> the
> SQL for the Max(invoice date) per customer...works well.
>
> select a.stn_sld_cus_id, Max(a.stn_inv_dt) max_invoice_date>
> from sahstn_rec a
>
> where stn_ord_pfx = 'SO' and a.stn_inv_dt < '04/01/2015'
>
> group by stn_sld_cus_id;
>
> The next SQL is what I have come up with for the second Max(invoice date)
> per
> customer. This is not working consistently.
>
> select a.stn_sld_cus_id, max(a.stn_inv_dt) max_inv_date>
> from sahstn_rec a
>
> where stn_inv_dt not in
>
> (select max(b.stn_inv_dt)
>
> from sahstn_rec b
>
> where stn_ord_pfx = 'SO' and
>
> a.stn_sld_cus_id = b.stn_sld_cus_id and
>
> a.stn_inv_dt < '04/01/2015'
>
> group by stn_sld_cus_id)
>
> and a.stn_inv_dt < '04/01/2015'
>
> group by a.stn_sld_cus_id;
>
> Any ideas?
>
> Thank you
> John
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0111b7447c303b051350a943
Version?
On Thu, Apr 9, 2015 at 9:32 PM, JOHN KRUPA <jkrupa@turretsteel.com> wrote:
> I have the need to retrieve the Max(invoice date) per customer. I also
> would
> like to retrieve the second Max(invoice date) per customer. Following is
> the
> SQL for the Max(invoice date) per customer...works well.
>
> select a.stn_sld_cus_id, Max(a.stn_inv_dt) max_invoice_date>
> from sahstn_rec a
>
> where stn_ord_pfx = 'SO' and a.stn_inv_dt < '04/01/2015'
>
> group by stn_sld_cus_id;
>
> The next SQL is what I have come up with for the second Max(invoice date)
> per
> customer. This is not working consistently.
>
> select a.stn_sld_cus_id, max(a.stn_inv_dt) max_inv_date>
> from sahstn_rec a
>
> where stn_inv_dt not in
>
> (select max(b.stn_inv_dt)
>
> from sahstn_rec b
>
> where stn_ord_pfx = 'SO' and
>
> a.stn_sld_cus_id = b.stn_sld_cus_id and
>
> a.stn_inv_dt < '04/01/2015'
>
> group by stn_sld_cus_id)
>
> and a.stn_inv_dt < '04/01/2015'
>
> group by a.stn_sld_cus_id;
>
> Any ideas?
>
> Thank you
> John
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--089e0115f6028f29ec0513515502
Your query was thisclose, but remember: your outer where clause should be at
least as inclusive as your subquery where clause.
select a.stn_sld_cus_id, max(a.stn_inv_dt) max_inv_date
from sahstn_rec a
where stn_inv_dt not in** OR where stn_inv_dt < ***
(select max(b.stn_inv_dt)
from sahstn_rec b
where stn_ord_pfx = 'SO' and
a.stn_sld_cus_id = b.stn_sld_cus_id and
a.stn_inv_dt < '04/01/2015'
group by stn_sld_cus_id)
** and a.stn_ord_pfx = 'SO' **
and a.stn_inv_dt < '04/01/2015'
group by a.stn_sld_cus_id
;
the 'not in' versus '<' shouldn't make a difference since you are passing the
MAX date.
But not including the stn_ord_pfx in the driving Where clause means you'll be
returned the MAX date for *any* type of invoice, which isn't the max date for
the SO order invoice.