Tricky "most recent sales" query 
Author Message
 Tricky "most recent sales" query

Hi all,
I am trying to create a select statement which returns
the most recent sale price for products purchased by a customer.

The sql may look something like :

SELECT ProductID, SellPrice , Quantity
FROM InvoiceItems
WHERE CustomerID = '1234'
ORDER BY InvoiceID

Are there any SQL gurus out there who know how to do this?

Thanks
Chris A.



Wed, 26 Feb 2003 13:26:07 GMT
 Tricky "most recent sales" query

How are you going to determine time here?
I would have had a DateOfSale column
and then said
SELECT max(DateOfSale)
FROM InvoiceItems
WHERE CustomerID = '1234'
ORDER BY InvoiceID



Quote:

> Hi all,
> I am trying to create a select statement which returns
> the most recent sale price for products purchased by a customer.

> The sql may look something like :

> SELECT ProductID, SellPrice , Quantity
> FROM InvoiceItems
> WHERE CustomerID = '1234'
> ORDER BY InvoiceID

> Are there any SQL gurus out there who know how to do this?

> Thanks
> Chris A.

--

Allan Mitchell
MCSE

Sent via Deja.com http://www.deja.com/
Before you buy.



Wed, 26 Feb 2003 15:35:09 GMT
 Tricky "most recent sales" query

Alan,
thanks for your reply.
The largest value of the InvoiceID is the most recent.
Although there is a Date on the Invoices table.

I need to list distinct products, showing the most recent sales price &
quantity,
for each customer.

So I guesss the select would need to start with
SELECT ProductID, SellPrice , Quantity

Chris A.


Quote:
> How are you going to determine time here?
> I would have had a DateOfSale column
> and then said
> SELECT max(DateOfSale)
> FROM InvoiceItems
> WHERE CustomerID = '1234'
> ORDER BY InvoiceID



> > Hi all,
> > I am trying to create a select statement which returns
> > the most recent sale price for products purchased by a customer.

> > The sql may look something like :

> > SELECT ProductID, SellPrice , Quantity
> > FROM InvoiceItems
> > WHERE CustomerID = '1234'
> > ORDER BY InvoiceID

> > Are there any SQL gurus out there who know how to do this?

> > Thanks
> > Chris A.

> --

> Allan Mitchell
> MCSE

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 26 Feb 2003 16:47:11 GMT
 Tricky "most recent sales" query

Chris,

What the CustomerId column has to do with InvoiceItems table.
It seems your schema is denormalized.
It would really help if you have posted the DDL for the tables structure.

Is this query doing the job?

SELECT
    InvoiceItems.ProductID,
    InvoiceItems.SellPrice,
    InvoiceItems.Quantity
FROM
    InvoiceItems
    INNER JOIN
    (
    SELECT ProductId, MAX( InvoiceId ) AS LastInvoiceID
    FROM InvoiceItems
    GROUP BY ProductId
    ) LastInvoiceIdPerProduct
    ON
    InvoiceItems.ProductId=LastInvoiceIdPerProduct.ProductId
    AND InvoiceItems.InvoiceId=LastInvoiceIdPerProduct.LastInvoiceID

HTH
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------


Quote:
> Alan,
> thanks for your reply.
> The largest value of the InvoiceID is the most recent.
> Although there is a Date on the Invoices table.

> I need to list distinct products, showing the most recent sales price &
> quantity,
> for each customer.

> So I guesss the select would need to start with
> SELECT ProductID, SellPrice , Quantity

> Chris A.



> > How are you going to determine time here?
> > I would have had a DateOfSale column
> > and then said
> > SELECT max(DateOfSale)
> > FROM InvoiceItems
> > WHERE CustomerID = '1234'
> > ORDER BY InvoiceID



> > > Hi all,
> > > I am trying to create a select statement which returns
> > > the most recent sale price for products purchased by a customer.

> > > The sql may look something like :

> > > SELECT ProductID, SellPrice , Quantity
> > > FROM InvoiceItems
> > > WHERE CustomerID = '1234'
> > > ORDER BY InvoiceID

> > > Are there any SQL gurus out there who know how to do this?

> > > Thanks
> > > Chris A.

> > --

> > Allan Mitchell
> > MCSE

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.



Wed, 26 Feb 2003 18:19:59 GMT
 Tricky "most recent sales" query
Yes
Use MAX() of whatever column it is to determine the latest and group by
the other columns

Allan



Quote:

> Alan,
> thanks for your reply.
> The largest value of the InvoiceID is the most recent.
> Although there is a Date on the Invoices table.

> I need to list distinct products, showing the most recent sales price
&
> quantity,
> for each customer.

> So I guesss the select would need to start with
> SELECT ProductID, SellPrice , Quantity

> Chris A.



> > How are you going to determine time here?
> > I would have had a DateOfSale column
> > and then said
> > SELECT max(DateOfSale)
> > FROM InvoiceItems
> > WHERE CustomerID = '1234'
> > ORDER BY InvoiceID



> > > Hi all,
> > > I am trying to create a select statement which returns
> > > the most recent sale price for products purchased by a customer.

> > > The sql may look something like :

> > > SELECT ProductID, SellPrice , Quantity
> > > FROM InvoiceItems
> > > WHERE CustomerID = '1234'
> > > ORDER BY InvoiceID

> > > Are there any SQL gurus out there who know how to do this?

> > > Thanks
> > > Chris A.

> > --

> > Allan Mitchell
> > MCSE

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

--

Allan Mitchell
MCSE

Sent via Deja.com http://www.deja.com/
Before you buy.



Wed, 26 Feb 2003 18:15:07 GMT
 Tricky "most recent sales" query

Do:

SELECT i1.CustomerID , i1.ProductID, i1.SellPrice , i1.Quantity
FROM InvoiceItems AS i1
WHERE i1.InvoiceID = ( SELECT MAX( i2.InvoiceID )
                       FROM InvoiceItems As i2
                       WHERE i1.CustomerID = i2.CustomerID )
ORDER BY i1.CustomerID

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)



Thu, 27 Feb 2003 00:22:56 GMT
 Tricky "most recent sales" query
select t1.* from InvoiceItems inner join
(
select CustomerID, MAX(InvoiceID) as InvoiceID
from InvoiceItems
group by CustomerID
) t2
on t1.CustomerID=t2.CustomerID and t1.InvoiceID=t2.InvoiceID

This is much more efficient

-Nils


Quote:

> Do:

> SELECT i1.CustomerID , i1.ProductID, i1.SellPrice , i1.Quantity
> FROM InvoiceItems AS i1
> WHERE i1.InvoiceID = ( SELECT MAX( i2.InvoiceID )
>                        FROM InvoiceItems As i2
>                        WHERE i1.CustomerID = i2.CustomerID )
> ORDER BY i1.CustomerID

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> (Please reply only to the newsgroup)



Thu, 27 Feb 2003 02:13:10 GMT
 Tricky "most recent sales" query
Umachandar,

this query wouldn't do the job! :)

What about the following data:

INSERT INTO InvoiceItems(InvoiceID,CustomerID,ProductID,SellPrice,Quantity)
VALUES(1,1,1,10,10)
VALUES(1,1,2,10,10)
VALUES(2,1,2,15,10)
VALUES(2,1,3,15,10)

We will get:
CustomerID    ProductID    SellPrice    Quantity
-------------------------------------------------
1    2    15    10
1    3    15    10

but the row:
1    1    10    10

will not be included, because your query is taking only the last invoice for
each customer, instead of the last invoice for each customer, for each
product!

If Chris needs a query for all customers (which was not the original
request - it was for one customer), my query also wouldn't do the job. To do
so it also needs correction.

The multi-customer version of my query should be:

SELECT ProductID, SellPrice, Quantity
FROM
    InvoiceItems i1 INNER JOIN
    (SELECT ProductId AS A_ProductID, CustomerId AS A_Customer,
        MAX( InvoiceId ) AS LastInvoiceID
    FROM InvoiceItems
    GROUP BY ProductId) AS LPP
    ON  i1.ProductId=LPP.A_ProductId
    AND i1.CustomerId=LPP.A_CustomerId
    AND i1.InvoiceId=LPP.LastInvoiceID

Umachandars query - correct version:

SELECT i1.CustomerID , i1.ProductID, i1.SellPrice , i1.Quantity
FROM InvoiceItems AS i1
WHERE i1.InvoiceID = ( SELECT MAX( i2.InvoiceID )
                       FROM InvoiceItems As i2
                       WHERE i1.CustomerID = i2.CustomerID
                        AND i1.ProductID = i2.ProductID)
ORDER BY i1.CustomerID

I bet these two queries yield the same query plan, but I don't have time to
check.
In essence they are the same query. Both will be internally converted to
nested loops/inner join.

HTH
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------


Quote:
> Do:

> SELECT i1.CustomerID , i1.ProductID, i1.SellPrice , i1.Quantity
> FROM InvoiceItems AS i1
> WHERE i1.InvoiceID = ( SELECT MAX( i2.InvoiceID )
>                        FROM InvoiceItems As i2
>                        WHERE i1.CustomerID = i2.CustomerID )
> ORDER BY i1.CustomerID

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> (Please reply only to the newsgroup)



Thu, 27 Feb 2003 01:39:14 GMT
 Tricky "most recent sales" query

Quote:
> select t1.* from InvoiceItems inner join
> (
> select CustomerID, MAX(InvoiceID) as InvoiceID
> from InvoiceItems
> group by CustomerID
> ) t2
> on t1.CustomerID=t2.CustomerID and t1.InvoiceID=t2.InvoiceID

This is still wrong. See my other post.

Quote:
> This is much more efficient

I bet (without trying) it is making the same plan - so it is the same.
BTW, see my query on the previous level.
Again, see my other post. You are posting almost the same, but incorrect
version.

--
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------



Thu, 27 Feb 2003 03:07:30 GMT
 Tricky "most recent sales" query

Quote:
>> I am trying to create a select statement which returns the most

recent sale price for products purchased by a customer. ,,

Please post the DDL for your table with some sample data, so that
people can quit guessing about it, and wasting their time.  I have the
feeling that there is a header for all these purchases details and that
the customer_id should there and not here ...

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.



Thu, 27 Feb 2003 06:23:34 GMT
 Tricky "most recent sales" query
Thanks to all who have replyed!!
I could not belive the interest and help from so many!
And I should have posted these scripts first up as some have suggested.

CREATE TABLE [dbo].[Invoices] (
 [InvoiceID] [int] NOT NULL ,
 [Date] [datetime] NULL ,
 [CustomerID] [varchar] (8) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceItems] (
 [InvoiceID] [int] NOT NULL ,
 [InvoiceItemID] [int] NOT NULL ,
 [ProductID] [varchar] (9) NULL ,
 [Quantity] [int] NULL ,
 [SellPrice] [smallmoney] NULL
) ON [PRIMARY]
GO

-- Insert 2 invoices each for 2  customers
INSERT INTO Invoices VALUES( 1, '01 JAN 2000', 'C1')
INSERT INTO Invoices VALUES( 2, '01 JAN 2000', 'C2')
INSERT INTO Invoices VALUES( 3, '02 JAN 2000', 'C1')
INSERT INTO Invoices VALUES( 4, '02 JAN 2000', 'C2')

-- Insert 2 invoice Items per customer per day
INSERT INTO InvoiceItems VALUES( 1, 1, 'P1', 24, 10.00 )
INSERT INTO InvoiceItems VALUES( 1, 2, 'P2', 36, 20.00 )
INSERT INTO InvoiceItems VALUES( 2, 1, 'P1', 12, 11.00 )
INSERT INTO InvoiceItems VALUES( 2, 2, 'P2', 26, 21.00 )
INSERT INTO InvoiceItems VALUES( 3, 1, 'P1', 72, 12.00 )
INSERT INTO InvoiceItems VALUES( 3, 2, 'P2', 56, 22.00 )
INSERT INTO InvoiceItems VALUES( 4, 1, 'P1', 48, 13.00 )
INSERT INTO InvoiceItems VALUES( 4, 2, 'P2', 44, 23.00 )

I would like the query which returns the most recent sales quantity & price.
with the following columns:
CustomerId,  ProductID, SellPrice, Quantity

I am looking at all the replyes to see if I can work out the SQL, but still
any help is very much appreciated.
Thanks again,
Chris A.



Thu, 27 Feb 2003 17:26:11 GMT
 Tricky "most recent sales" query
Chris,

As I understand it, you want the price and quantity for the most
recently issued invoice for each customer and product combination.

SELECT CustomerID, ProductID, SellPrice, Quantity
        FROM Invoices i1, InvoiceItems ii1
        WHERE ii1.InvoiceID =
        (SELECT MAX(InvoiceID) FROM Invoice
                GROUP BY CustomerID)  

Jon

On Sun, 10 Sep 2000 18:56:11 +0930, "Chris Anthony"

Quote:

>Thanks to all who have replyed!!
>I could not belive the interest and help from so many!
>And I should have posted these scripts first up as some have suggested.

>CREATE TABLE [dbo].[Invoices] (
> [InvoiceID] [int] NOT NULL ,
> [Date] [datetime] NULL ,
>) ON [PRIMARY]
> [CustomerID] [varchar] (8) NULL
>GO

>CREATE TABLE [dbo].[InvoiceItems] (
> [InvoiceID] [int] NOT NULL ,
> [InvoiceItemID] [int] NOT NULL ,
> [ProductID] [varchar] (9) NULL ,
> [Quantity] [int] NULL ,
> [SellPrice] [smallmoney] NULL
>) ON [PRIMARY]
>GO

>-- Insert 2 invoices each for 2  customers
>INSERT INTO Invoices VALUES( 1, '01 JAN 2000', 'C1')
>INSERT INTO Invoices VALUES( 2, '01 JAN 2000', 'C2')
>INSERT INTO Invoices VALUES( 3, '02 JAN 2000', 'C1')
>INSERT INTO Invoices VALUES( 4, '02 JAN 2000', 'C2')

>-- Insert 2 invoice Items per customer per day
>INSERT INTO InvoiceItems VALUES( 1, 1, 'P1', 24, 10.00 )
>INSERT INTO InvoiceItems VALUES( 1, 2, 'P2', 36, 20.00 )
>INSERT INTO InvoiceItems VALUES( 2, 1, 'P1', 12, 11.00 )
>INSERT INTO InvoiceItems VALUES( 2, 2, 'P2', 26, 21.00 )
>INSERT INTO InvoiceItems VALUES( 3, 1, 'P1', 72, 12.00 )
>INSERT INTO InvoiceItems VALUES( 3, 2, 'P2', 56, 22.00 )
>INSERT INTO InvoiceItems VALUES( 4, 1, 'P1', 48, 13.00 )
>INSERT INTO InvoiceItems VALUES( 4, 2, 'P2', 44, 23.00 )

>I would like the query which returns the most recent sales quantity & price.
>with the following columns:
>CustomerId,  ProductID, SellPrice, Quantity

>I am looking at all the replyes to see if I can work out the SQL, but still
>any help is very much appreciated.
>Thanks again,
>Chris A.



Fri, 28 Feb 2003 08:01:36 GMT
 
 [ 12 post ] 

 Relevant Pages 

1. max of ("...","...","..")

2. "Most recent items" list

3. Files listed in "Recent" folder

4. recent operation "DESCRIBE" in application snapshot

5. How to make a "decimal"-field to an "integer"-field

6. "."and ","

7. "."and ","

8. The ""string""

9. aReport."Field".SetFilter("this")?


 
Powered by phpBB® Forum Software