asked on

complex sql select statement - Microsoft SQL Server 2008R2

I am looking for a sql select statement that will return to me all the relevant data from the table as explained below.

The test data you may use is attached.

I want only 1 record per product based on the current date (use getdate()).

Valid records are any record where the effectivefromdate is less than or equal to the currect date.

Of all the records that are valid (all the records where the effective fromdate is less than the current date) I only want the 1 record per product with the highest effective from date.

I am looking for a single seclect statement (not cursors, stored procs etc... I can do it that way already).

The results I require from the select statement are: (if date is 13 May 2011):

1      11.00      2011-02-11
2      11.00      2011-02-21
3      11.00      2011-02-03
4      11.00      2011-02-09
5      11.00      2011-02-28

The results I require from the select statement are: (if date is 13 Aug 2011):

1      15.00      2011-06-04
2      11.00      2011-02-27
3      15.00      2011-06-07
4      15.00      2011-06-11
5      15.00      2011-06-19

--------------------------------------------
Test Data
--------------------------------------------
declare @ExampleTable table
(
      PKID uniqueidentifier,
      ProductID int,
      ProductPrice decimal(18,2),
      EffectiveFromDate date
)

insert into @ExampleTable values (newid(), 1, 10.00, '1 Jan 2011')
insert into @ExampleTable values (newid(), 2, 10.00, '12 Jan 2011')
insert into @ExampleTable values (newid(), 3, 10.00, '13 Jan 2011')
insert into @ExampleTable values (newid(), 4, 10.00, '8 Jan 2011')
insert into @ExampleTable values (newid(), 5, 10.00, '10 Jan 2011')
insert into @ExampleTable values (newid(), 1, 11.00, '11 Feb 2011')
insert into @ExampleTable values (newid(), 2, 11.00, '21 Feb 2011')
insert into @ExampleTable values (newid(), 2, 11.00, '27 Feb 2011')
insert into @ExampleTable values (newid(), 3, 11.00, '3 Feb 2011')
insert into @ExampleTable values (newid(), 4, 11.00, '9 Feb 2011')
insert into @ExampleTable values (newid(), 5, 11.00, '28 Feb 2011')
insert into @ExampleTable values (newid(), 1, 15.00, '4 June 2011')
insert into @ExampleTable values (newid(), 3, 15.00, '7 June 2011')
insert into @ExampleTable values (newid(), 4, 15.00, '11 June 2011')
insert into @ExampleTable values (newid(), 5, 15.00, '19 June 2011')

ASKER CERTIFIED SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

SELECT ProductID, ProductPrice, EffectiveFromDate
FROM @ExampleTable INNER JOIN
    (SELECT ProductID, MAX(EffectiveFromDate) EffDate
    FROM @ExampleTable
    GROUP BY ProductID
    WHERE EffectiveFromDate <= GETDATE()) x ON ProductID = x.ProductID AND EffectiveFromDate = x.EffDate

Open in new window

Nico Bontenbal

or us a cte:

;WITH maxdat (ProductID, EffectiveFromDate)
AS
(
	select 
		ProductID, 
		MAX(EffectiveFromDate) as EffectiveFromDate
	from
		@ExampleTable
	where
		EffectiveFromDate <='20110513'
	group by
		ProductID
 )
Select
	prod.Productid,
	prod.ProductPrice,
	prod.EffectiveFromDate
from
	@ExampleTable prod
	inner join maxdat on  maxdat.EffectiveFromDate = prod.EffectiveFromDate 
order by
	productid

Open in new window

For explanation of CTE see:
http://msdn.microsoft.com/en-us/library/ms190766.aspx
For explanation of rownumber see:
http://msdn.microsoft.com/en-us/library/ms186734.aspx