Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

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')
0
zadeveloper
Asked:
zadeveloper
  • 2
1 Solution
 
Nico BontenbalCommented:
Try:
Select
	Productid,
	ProductPrice,
	EffectiveFromDate
from
	(
	select 
		ProductID, 
		ProductPrice,
		EffectiveFromDate,
		row_number() over (Partition by ProductID order by EffectiveFromDate desc) as row
	from
		@ExampleTable
	where
		EffectiveFromDate <='20110813'
	) as sub
where
	row = 1

Open in new window

0
 
Patrick MatthewsCommented:
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

0
 
Nico BontenbalCommented:
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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now