Select statement for last 3 records
-
Hi, I have a table of customers, date, and purchase dollars. What I need is to return one row for each customer with their last 3 purchases dollars and dates (don’t ask why – a primitive reporting package). For example, if the data was this:
Customer Date Purchase Dollars
A 2005/06/12 15.00
A 2005/06/18 4.50
A 2005/06/22 8.00
A 2005/06/29 7.00
B 2005/06/01 10.50
B 2005/06/15 14.50
B 2005/06/27 6.00
B 2005/06/28 97.00
What I would like back is 2 rows:
A 2005/06/29 7.00 2005/06/22 8.00 2005/06/18 4.50
B 2005/06/28 97.00 2005/06/27 6.00 2005/06/15 14.50
Can anyone help me with a SQL statement to accomplish this? Thanks.
-
Hello Carol,
there are several ways to accomplish that. If you will prepare similar reports often, then it would be best to handle them with a reporting tool (like Reporting Services). If it's just an occasional issue, you can do it with SQL query. Well, unfortunately you didn't supply any DDL so I had to assume a lot, but hopefully it will get you started. However, this query works only as long as there is maximum 1 purchase per day for any customer... if there are several, you have to modify the query (the part which is assigning rank) so that each row is identifiable and gets a proper rank... or maybe sum the purchases made on the same day? That depends on what you need...
/*table definition and data*/
create table purchases (customerid int, purchase_date datetime, amount money)
insert into purchases values (1, '20050612', 15.00)
insert into purchases values (1, '20050618', 4.5)
insert into purchases values (1, '20050622', 8.00)
insert into purchases values (1, '20050629', 7.00)
insert into purchases values (2, '20050601', 10.50)
insert into purchases values (2, '20050615', 14.50)
insert into purchases values (2, '20050627', 6.00)
insert into purchases values (2, '20050628', 97.00)
create table customer (customerid int, custname varchar(30))
insert into customer values (1, 'A')
insert into customer values (2, 'B')
/*order the purchases and assign rank (rank 1 = last purchase*/
IF object_id('tempdb..#ordered') IS NOT NULL DROP TABLE #ordered
SELECT customerid, purchase_date, amount,
(select count(*) from purchases where customerid = pu.customerid AND purchase_date > pu.purchase_date)+1 as rank
INTO #ordered
FROM purchases pu
/*select the data for last 3 purchases of every customer*/
SELECT cu.custname, o1.purchase_date, o1.amount,
o2.purchase_date, o2.amount, o3.purchase_date, o3.amount
FROM customer cu
LEFT JOIN #ordered o1 ON o1.customerid = cu.customerid AND o1.rank = 1
LEFT JOIN #ordered o2 ON o2.customerid = cu.customerid AND o2.rank = 2
LEFT JOIN #ordered o3 ON o3.customerid = cu.customerid AND o3.rank = 3
BTW. It would help us greatly in finding a solution, if you could post table definition and sample data (like the beginning of my SQL), when asking some question on the forums. Thanks, Vladan
-
This might be overkill, but here is another option. If you take the -- out of the PRINT @sql line, you will see what is happening... Like Vladan's, this will wreck havoc if two purchases are made on the same day...
CREATE TABLE [dbo].[Purchase] ( Customer char(1),
PurchaseDate smalldatetime,
PurchaseDollars money)
INSERT INTO Purchase VALUES( 'A', '2005/06/12', 15.00)
INSERT INTO Purchase VALUES( 'A', '2005/06/18', 4.50)
INSERT INTO Purchase VALUES( 'A', '2005/06/22', 8.00)
INSERT INTO Purchase VALUES( 'A', '2005/06/29', 7.00)
INSERT INTO Purchase VALUES( 'B', '2005/06/01', 10.50)
INSERT INTO Purchase VALUES( 'B', '2005/06/15', 14.50)
INSERT INTO Purchase VALUES( 'B', '2005/06/27', 6.00)
INSERT INTO Purchase VALUES( 'B', '2005/06/28', 97.00)
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[LastThree]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROC [dbo].[LastThree]
GO
CREATE PROCEDURE [dbo].[LastThree]
AS
SET NOCOUNT ON
DECLARE @CurrentCustomer char(1),
@MaxCurrentCustomer char(1),
@Count integer,
@PurchaseDate smalldatetime,
@PurchaseDollar money,
@sql varchar(2000)
SELECT @CurrentCustomer = (SELECT MIN( Customer) FROM Purchase)
SELECT @MaxCurrentCustomer = (SELECT MAX( Customer) FROM Purchase)
CREATE TABLE #LastThreePurchases(
Customer char(1),
Date1 smalldatetime,
Dollar1 money,
Date2 smalldatetime,
Dollar2 money,
Date3 smalldatetime,
Dollar3 money)
INSERT INTO #LastThreePurchases( Customer)
SELECT DISTINCT Customer FROM Purchase
WHILE @CurrentCustomer <= @MaxCurrentCustomer
BEGIN
SELECT @PurchaseDate = NULL
SELECT @PurchaseDollar = NULL
SELECT @Count = 1
WHILE @Count <= 3
BEGIN
SELECT @PurchaseDate = (SELECT MAX( PurchaseDate) FROM Purchase
WHERE @CurrentCustomer = Customer
AND PurchaseDate < ISNULL( @PurchaseDate, DATEADD( year, 10, GETDATE())))
SELECT @PurchaseDollar = (SELECT PurchaseDollars FROM Purchase
WHERE @CurrentCustomer = Customer
AND PurchaseDate = @PurchaseDate)
SELECT @sql = ' UPDATE #LastThreePurchases SET ' + CHAR(13) +
' Date' + CONVERT( char(5), @Count) + ' = ' +
CHAR(39) + CONVERT( varchar(10), @PurchaseDate, 101) + CHAR(39) + ', ' + CHAR(13) +
' Dollar' + CONVERT( char(5), @Count) + ' = ' +
CONVERT( varchar(25), @PurchaseDollar) + CHAR(13) +
' WHERE Customer = ' + CHAR(39) + @CurrentCustomer + CHAR(39)
-- PRINT @sql
EXECUTE( @sql)
SELECT @Count = @Count + 1
END
SELECT @CurrentCustomer = (SELECT MIN( Customer) FROM Purchase WHERE Customer > @CurrentCustomer)
END
SELECT Customer, CONVERT( varchar, Date1, 111) AS Date1, Dollar1,
CONVERT( varchar, Date2, 111) AS Date2, Dollar2,
CONVERT( varchar, Date3, 111) AS Date2, Dollar3
FROM #LastThreePurchases
GO
EXECUTE LastThree
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[LastThree]')AND OBJECTPROPERTY(id, N'IsProcedure')=1)
DROP PROC [dbo].[LastThree]
GO
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[Purchase]')AND OBJECTPROPERTY(id, N'IsTable')=1)
DROP TABLE [dbo].[Purchase]
GO
/*
What I would like back is 2 rows:
Cust Date1 Dollar1 Date2 Dollar2 Date3 Dollar3
----------------------------------------------------------------------------------------------------------------
A 2005/06/29 7.00 2005/06/22 8.00 2005/06/18 4.50
B 2005/06/28 97.00 2005/06/27 6.00 2005/06/15 14.50
I wasn't born stupid - I had to study.
-
Actually, mine should work even if two orders were made on the same date, as long as your date field, (and the data passed into it) is smalldatetime or datetime.
I added an extra record for Customer A on June 22, 2005 that was a minute later than the first and it worked fine.
CREATE TABLE [dbo].[Purchase] ( Customer char(1),
PurchaseDate smalldatetime,
PurchaseDollars money)
INSERT INTO Purchase VALUES( 'A', '2005/06/12', 15.00)
INSERT INTO Purchase VALUES( 'A', '2005/06/18', 4.50)
INSERT INTO Purchase VALUES( 'A', '2005/06/22', 8.00)
INSERT INTO Purchase VALUES( 'A', DATEADD( minute, 1, CONVERT( smalldatetime, '2005/06/22')), 25.00)
INSERT INTO Purchase VALUES( 'A', '2005/06/29', 7.00)
INSERT INTO Purchase VALUES( 'B', '2005/06/01', 10.50)
INSERT INTO Purchase VALUES( 'B', '2005/06/15', 14.50)
INSERT INTO Purchase VALUES( 'B', '2005/06/27', 6.00)
INSERT INTO Purchase VALUES( 'B', '2005/06/28', 97.00)
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[LastThree]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROC [dbo].[LastThree]
GO
CREATE PROCEDURE [dbo].[LastThree]
AS
SET NOCOUNT ON
DECLARE @CurrentCustomer char(1),
@MaxCurrentCustomer char(1),
@Count integer,
@PurchaseDate smalldatetime,
@PurchaseDollar money,
@sql varchar(2000)
SELECT @CurrentCustomer = (SELECT MIN( Customer) FROM Purchase)
SELECT @MaxCurrentCustomer = (SELECT MAX( Customer) FROM Purchase)
CREATE TABLE #LastThreePurchases(
Customer char(1),
Date1 smalldatetime,
Dollar1 money,
Date2 smalldatetime,
Dollar2 money,
Date3 smalldatetime,
Dollar3 money)
INSERT INTO #LastThreePurchases( Customer)
SELECT DISTINCT Customer FROM Purchase
WHILE @CurrentCustomer <= @MaxCurrentCustomer
BEGIN
SELECT @PurchaseDate = NULL
SELECT @PurchaseDollar = NULL
SELECT @Count = 1
WHILE @Count <= 3
BEGIN
SELECT @PurchaseDate = (SELECT MAX( PurchaseDate) FROM Purchase
WHERE @CurrentCustomer = Customer
AND PurchaseDate < ISNULL( @PurchaseDate, DATEADD( year, 10, GETDATE())))
SELECT @PurchaseDollar = (SELECT PurchaseDollars FROM Purchase
WHERE @CurrentCustomer = Customer
AND PurchaseDate = @PurchaseDate)
SELECT @sql = ' UPDATE #LastThreePurchases SET ' + CHAR(13) +
' Date' + CONVERT( char(5), @Count) + ' = ' +
CHAR(39) + CONVERT( varchar(10), @PurchaseDate, 101) + CHAR(39) + ', ' + CHAR(13) +
' Dollar' + CONVERT( char(5), @Count) + ' = ' +
CONVERT( varchar(25), @PurchaseDollar) + CHAR(13) +
' WHERE Customer = ' + CHAR(39) + @CurrentCustomer + CHAR(39)
-- PRINT @sql
EXECUTE( @sql)
SELECT @Count = @Count + 1
END
SELECT @CurrentCustomer = (SELECT MIN( Customer) FROM Purchase WHERE Customer > @CurrentCustomer)
END
SELECT Customer, CONVERT( varchar, Date1, 111) AS Date1, Dollar1,
CONVERT( varchar, Date2, 111) AS Date2, Dollar2,
CONVERT( varchar, Date3, 111) AS Date2, Dollar3
FROM #LastThreePurchases
GO
EXECUTE LastThree
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[LastThree]')AND OBJECTPROPERTY(id, N'IsProcedure')=1)
DROP PROC [dbo].[LastThree]
GO
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[Purchase]')AND OBJECTPROPERTY(id, N'IsTable')=1)
DROP TABLE [dbo].[Purchase]
GO
I wasn't born stupid - I had to study.
-
Yes, I may have more than one purchase on the same day (and the field is smalldatetime). I was able to take Farrell's post and make it work - thanks everyone for their help.
-
You're welcome! In fact, my method works too, as long as the purchases of each customer can be ordered unambiguously (i.e. ORDER BY criteria are such, that there are no 2 purchases of a customer that would be equal in them). I just oversimplified it, saying that they may not have the same date... because it seemed to me, that you don't store the time portion of purchase date. But, as I mentioned, it is possible to modify the SQL so that it works safely, regardless of multiple purchases with the same time.
I'm just curious, which of the solutions would perform better - mine is set based, so IMHO it should be faster than taking and updating the data row by row - but one never knows.
Vladan
-
Vladan - yours WILL absolutely be faster! Mine is far more a bulldozer approach. I should probably change my screen name to bulldozer...
I wasn't born stupid - I had to study.
-
Ya and you seem to like those approaches more often than not...
just my 2 cents.
-
But its a nice clean bulldozer... Anyway, I still like to play in the dirt...
I wasn't born stupid - I had to study.
-
.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply