[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How to get 4 max dates?

SQLFriend

3/29/2007 3:44:00 PM

I would like to get the 4 max dates (largest) per customer, any idea how to
do it?
For exapmle:
Cust Invoice Date
1 1/1/06
1 2/1/06
1 3/1/06
1 4/1/06
1 5/1/06
1 6/1/06
1 7/1/06
2 1/1/06
2 2/1/06
2 3/1/06
2 4/1/06
2 5/1/06
2 6/1/06
2 7/1/06
I would like to run a query to get invoices from Apr to Jul for Cust 1 and 2
and should always give the the last 4 invoices anytime I run it.

Thanks in advance
10 Answers

Roy Harvey

3/29/2007 5:04:00 PM

0

SELECT Cust, InvoiceDate
FROM Invoices as A
WHERE InvoiceDate IN
(SELECT TOP 4 B.InvoiceDate
FROM Invoices as B
WHERE A.Cust = B.Cust
ORDER BY B.InvoiceDate Desc)

The one ambiguity in the above is when there are multiple invoices for
a date. If the desire is to treat all the invoices with the same
customer and date as one, then add DISTINCT to both the outer query
and the subquery. If you want to treat them some other way please
specify.

Roy Harvey
Beacon Falls, CT

On Thu, 29 Mar 2007 08:44:07 -0700, SQLFriend
<SQLFriend@discussions.microsoft.com> wrote:

>I would like to get the 4 max dates (largest) per customer, any idea how to
>do it?
>For exapmle:
>Cust Invoice Date
>1 1/1/06
>1 2/1/06
>1 3/1/06
>1 4/1/06
>1 5/1/06
>1 6/1/06
>1 7/1/06
>2 1/1/06
>2 2/1/06
>2 3/1/06
>2 4/1/06
>2 5/1/06
>2 6/1/06
>2 7/1/06
>I would like to run a query to get invoices from Apr to Jul for Cust 1 and 2
>and should always give the the last 4 invoices anytime I run it.
>
>Thanks in advance

Kent Tegels

3/29/2007 5:14:00 PM

0

Hello SQLFriend,

> I would like to run a query to get invoices from Apr to Jul for Cust 1
> and 2
> and should always give the the last 4 invoices anytime I run it.

This should work in SQL 2005:

use scratch
go
drop table dbo.invoices
go
create table dbo.invoices(
customerID tinyint,
invoiceDate smallDateTime)
go
set nocount on
insert into dbo.invoices values(1,'1/1/06')
insert into dbo.invoices values(1,'2/1/06')
insert into dbo.invoices values(1,'3/1/06')
insert into dbo.invoices values(1,'4/1/06')
insert into dbo.invoices values(1,'5/1/06')
insert into dbo.invoices values(1,'6/1/06')
insert into dbo.invoices values(1,'7/1/06')
insert into dbo.invoices values(2,'1/1/06')
insert into dbo.invoices values(2,'2/1/06')
insert into dbo.invoices values(2,'3/1/06')
insert into dbo.invoices values(2,'4/1/06')
insert into dbo.invoices values(2,'5/1/06')
insert into dbo.invoices values(2,'6/1/06')
insert into dbo.invoices values(2,'7/1/06')
go
with c as (select customerID,invoiceDate,row_number() over (partition by
customerID order by InvoiceDate desc) as rn from dbo.invoices) select customerID,InvoiceDate
from c where rn < 5

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.co...

Plamen Ratchev

3/29/2007 5:26:00 PM

0

Here is for both SQL 2000 & 2005:

SELECT Cust, InvoiceDate
FROM Invoices AS I1
WHERE InvoiceDate IN
(SELECT TOP 4 InvoiceDate
FROM Invoices AS I2
WHERE I2.Cust = I1.Cust
ORDER BY InvoiceDate DESC, Cust)
ORDER BY Cust, InvoiceDate DESC

This one does not handle ties and if you have duplicate invoice dates per
customer in the range they will be listed.


For SQL 2005 only:

SELECT Cust, InvoiceDate
FROM (SELECT Cust, InvoiceDate,
ROW_NUMBER()
OVER(PARTITION BY Cust
ORDER BY InvoiceDate DESC, Cust) As rn
FROM Invoices) AS I
WHERE rn <= 4

This one has no issues with duplicates (still does not treat ties in any
way, but ROW_NUMBER limits the result to 4).

HTH,

Plamen Ratchev
http://www.SQL...

SQLFriend

3/29/2007 5:50:00 PM

0

This worked great, Thanks Roy

"Roy Harvey" wrote:

> SELECT Cust, InvoiceDate
> FROM Invoices as A
> WHERE InvoiceDate IN
> (SELECT TOP 4 B.InvoiceDate
> FROM Invoices as B
> WHERE A.Cust = B.Cust
> ORDER BY B.InvoiceDate Desc)
>
> The one ambiguity in the above is when there are multiple invoices for
> a date. If the desire is to treat all the invoices with the same
> customer and date as one, then add DISTINCT to both the outer query
> and the subquery. If you want to treat them some other way please
> specify.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 29 Mar 2007 08:44:07 -0700, SQLFriend
> <SQLFriend@discussions.microsoft.com> wrote:
>
> >I would like to get the 4 max dates (largest) per customer, any idea how to
> >do it?
> >For exapmle:
> >Cust Invoice Date
> >1 1/1/06
> >1 2/1/06
> >1 3/1/06
> >1 4/1/06
> >1 5/1/06
> >1 6/1/06
> >1 7/1/06
> >2 1/1/06
> >2 2/1/06
> >2 3/1/06
> >2 4/1/06
> >2 5/1/06
> >2 6/1/06
> >2 7/1/06
> >I would like to run a query to get invoices from Apr to Jul for Cust 1 and 2
> >and should always give the the last 4 invoices anytime I run it.
> >
> >Thanks in advance
>

Steve Dassin

3/29/2007 9:15:00 PM

0

Hello Mr. Developer,

S2005 offers a more conceptual like algebra to developers
as an alternative to the sql calculus anathema to most of them.

> I would like to get...per customer

'Per customer' means 'foreach' to a developer.

select distinct a.customerID
from ##invoices as a

What doya want foreach customer:
> invoices

Apply dates for each customer.

select distinct a.customerID,c.invoiceDate
from ##invoices as a
cross apply
(select b.invoiceDate
from ##invoices as b
where b.customerID=a.customerID) as c (invoiceDate)

But which dates?
> get the 4 max dates (largest)....the last 4 invoices

select distinct a.customerID,c.invoiceDate
from ##invoices as a
cross apply
(select b.invoiceDate,
row_number()over(order by b.invoiceDate desc) as rowid
from ##invoices as b
where b.customerID=a.customerID and rowid<=4) as c (invoiceDate)

Developer: help me because this makes sense but doesn't work.
Ok, we have to make an allowance for sql's silliness.

select distinct a.customerID,d.invoiceDate
from ##invoices as a
cross apply
(select invoiceDate
from
(select b.invoiceDate,row_number()over(order by b.invoiceDate desc) as rowid
from ##invoices as b
where b.customerID=a.customerID) as c (invoiceDate,rowid)
where rowid<=4) as d (invoiceDate)

Apply is a best friend to developers. Both conceptually and
usefully. Bury the subquery idea, it is dead.
This was, is and always will be nonsense to developers:

SELECT Cust, InvoiceDate
FROM Invoices as A
WHERE InvoiceDate IN
(SELECT TOP 4 B.InvoiceDate
FROM Invoices as B
WHERE A.Cust = B.Cust
ORDER BY B.InvoiceDate Desc)

The repository from which all data comes is now FROM, with
APPLY just another table like structure to grap data.
Make sense?

best,
steve

I'm an advocate of Dataphor, a relational based system for application
developerment:
www.alphora.com

www.beyondsql.blogspot.com


"Kent Tegels" <ktegels@develop.com> wrote in message
news:18f2bcb1858f8c94012e62c3425@news.microsoft.com...
> Hello SQLFriend,
>
>> I would like to run a query to get invoices from Apr to Jul for Cust 1
>> and 2
>> and should always give the the last 4 invoices anytime I run it.
>
> This should work in SQL 2005:
>
> use scratch
> go
> drop table dbo.invoices
> go
> create table dbo.invoices(
> customerID tinyint,
> invoiceDate smallDateTime)
> go
> set nocount on
> insert into dbo.invoices values(1,'1/1/06')
> insert into dbo.invoices values(1,'2/1/06')
> insert into dbo.invoices values(1,'3/1/06')
> insert into dbo.invoices values(1,'4/1/06')
> insert into dbo.invoices values(1,'5/1/06')
> insert into dbo.invoices values(1,'6/1/06')
> insert into dbo.invoices values(1,'7/1/06')
> insert into dbo.invoices values(2,'1/1/06')
> insert into dbo.invoices values(2,'2/1/06')
> insert into dbo.invoices values(2,'3/1/06')
> insert into dbo.invoices values(2,'4/1/06')
> insert into dbo.invoices values(2,'5/1/06')
> insert into dbo.invoices values(2,'6/1/06')
> insert into dbo.invoices values(2,'7/1/06')
> go
> with c as (select customerID,invoiceDate,row_number() over (partition by
> customerID order by InvoiceDate desc) as rn from dbo.invoices) select
> customerID,InvoiceDate from c where rn < 5
>
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.co...
>
>


Steve Dassin

3/29/2007 11:04:00 PM

0

Hello Mr. Developer,
(I got my 'developers' mixed up :( :) )

S2005 offers a more conceptual like algebra to developers
as an alternative to the sql calculus anathema to most of them.

> I would like to get...per customer

'Per customer' means 'foreach' to a developer.

select distinct a.customerID
from ##invoices as a

What doya want foreach customer:
> invoices

Apply dates for each customer.

select distinct a.customerID,c.invoiceDate
from ##invoices as a
cross apply
(select b.invoiceDate
from ##invoices as b
where b.customerID=a.customerID) as c (invoiceDate)

But which dates?
> get the 4 max dates (largest)....the last 4 invoices

select distinct a.customerID,c.invoiceDate
from ##invoices as a
cross apply
(select b.invoiceDate,
row_number()over(order by b.invoiceDate desc) as rowid
from ##invoices as b
where b.customerID=a.customerID and rowid<=4) as c (invoiceDate)

Developer: help me because this makes sense but doesn't work.
Ok, we have to make an allowance for sql's silliness.

select distinct a.customerID,d.invoiceDate
from ##invoices as a
cross apply
(select invoiceDate
from
(select b.invoiceDate,row_number()over(order by b.invoiceDate desc) as rowid
from ##invoices as b
where b.customerID=a.customerID) as c (invoiceDate,rowid)
where rowid<=4) as d (invoiceDate)

Apply is a best friend to developers. Both conceptually and
usefully. Bury the subquery idea, it is dead.
This was, is and always will be nonsense to developers:

SELECT Cust, InvoiceDate
FROM Invoices as A
WHERE InvoiceDate IN
(SELECT TOP 4 B.InvoiceDate
FROM Invoices as B
WHERE A.Cust = B.Cust
ORDER BY B.InvoiceDate Desc)

The repository from which all data comes is now FROM, with
APPLY just another table like structure to grap data.
Make sense?

best,
steve

I'm an advocate of Dataphor, a relational based system for application
developerment:
www.alphora.com

www.beyondsql.blogspot.com

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:upNrXeicHHA.3408@TK2MSFTNGP03.phx.gbl...
> Here is for both SQL 2000 & 2005:
>
> SELECT Cust, InvoiceDate
> FROM Invoices AS I1
> WHERE InvoiceDate IN
> (SELECT TOP 4 InvoiceDate
> FROM Invoices AS I2
> WHERE I2.Cust = I1.Cust
> ORDER BY InvoiceDate DESC, Cust)
> ORDER BY Cust, InvoiceDate DESC
>
> This one does not handle ties and if you have duplicate invoice dates per
> customer in the range they will be listed.
>
>
> For SQL 2005 only:
>
> SELECT Cust, InvoiceDate
> FROM (SELECT Cust, InvoiceDate,
> ROW_NUMBER()
> OVER(PARTITION BY Cust
> ORDER BY InvoiceDate DESC, Cust) As rn
> FROM Invoices) AS I
> WHERE rn <= 4
>
> This one has no issues with duplicates (still does not treat ties in any
> way, but ROW_NUMBER limits the result to 4).
>
> HTH,
>
> Plamen Ratchev
> http://www.SQL...
>
>
>


Plamen Ratchev

3/29/2007 11:30:00 PM

0

You seem to like CROSS APPLY a lot! :)

But a bit of a performance killer, isn't it? Just comparing execution plans
on what I posted and your CROSS APPLY solution it is 6% vs. 94%. And seems
your logic needs a bit of extra work too... If there are duplicate invoice
dates in the first 4 row numbers generated the duplicate rows get dropped,
so you end up with less than 4 invoices per customer.
:)

Regards,

Plamen Ratchev
http://www.SQL...

Plamen Ratchev

3/30/2007 12:24:00 AM

0

And yes, it makes sense, when applied correctly.

Plamen Ratchev
http://www.SQL...

Steve Dassin

3/30/2007 12:25:00 AM

0

Hello Plamen,

> ...seems your logic needs a bit of extra work too...If there are
> duplicate invoice dates in the first 4 row numbers generated
> the duplicate rows get dropped, so you end up with less than 4 invoices
> per customer.

Thank you sir, may I have another!

:-0 :-0 :")

RBAY if your that picky over a potential duplicate row: :P -:)

select distinct customerID,c.invoiceDate,rowid -- really didn't want this
--
but you made me do it :(:)
from ##invoices as a
cross apply
(select invoiceDate,row_number()over(order by b.invoiceDate desc) as rowid
from ##invoices as b
where b.customerID=a.customerID) as c (invoiceDate,rowid)
where rowid<=4
order by customerID,rowid

I don't want to get rid of distinct as it conveys the whole cursor idea :)

> But a bit of a performance killer, isn't it?

Yes, it's dragging the cart(esian) wherever it goes. But, in all
fairness to myself, I've pointed out there is a tinnie winnie drop in
performance :)

> You seem to like CROSS APPLY a lot!

Yes, because after what sql calls a table, this is the most basic
structure (vector) in sql. The subquery and what is commonly referred to
as a derived table are just special cases. History will show that
sql put the cart before the horse :) But hey, they screw up so much
I have to give credit here. Better late than never. But they should
have consulted me before they wrote it up. Do you think Stevie
Wonder makes sense as an art critic? -:)

best and respectfully,
steve

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:%23X1lpplcHHA.4388@TK2MSFTNGP05.phx.gbl...
> You seem to like CROSS APPLY a lot! :)
>
> But a bit of a performance killer, isn't it? Just comparing execution
plans
> on what I posted and your CROSS APPLY solution it is 6% vs. 94%. And seems
> your logic needs a bit of extra work too... If there are duplicate invoice
> dates in the first 4 row numbers generated the duplicate rows get dropped,
> so you end up with less than 4 invoices per customer.
> :)
>
> Regards,
>
> Plamen Ratchev
> http://www.SQL...
>
>
>

Plamen Ratchev

3/30/2007 3:01:00 AM

0

"Steve Dassin" <rac4sqlnospam@net> wrote in message
news:OTzHBHncHHA.2268@TK2MSFTNGP02.phx.gbl...
>
> select distinct customerID,c.invoiceDate,rowid -- really didn't want this
> --

:)
Hey, hey, no cheating! User requirements state the result set should contain
only customer and invoice, no artificial columns...

> ... But they should
> have consulted me before they wrote it up.

Never too late! You can always make an impact:
http://www.microsoft.co...

> ...Do you think Stevie
> Wonder makes sense as an art critic? -:)

Define art please?

:)

Ok, this is getting not related to the subject and I will cut it off here.
Meet you at another CROSS APPLY intersection!

Cheers,

Plamen Ratchev
http://www.SQL...