Hi, Let say we have given some dates in our table.
the structure is as below

CREATE TABLE [dbo].[datetest](
[test_date] [datetime] NULL
)

insert into datetest
select '2012-04-25'
union
select '2011-12-31'
union
select '2011-12-27'
union
select '2011-12-23'
union
select '2011-12-23'
union
select '2011-12-20'

Now here the case is we have the latest date 25-APR-2012. I want the latest previous week dates from the max date. In this case the max date is 25-APR-2012. so the latest previous week dates are 31-DEC-2011 and 27-DEC-2011. I have written the code like
select Distinct CONVERT(VARCHAR(10),test_date,126) AS test_date
from datetest
where datepart(ww, test_date) in(select top 1 datepart(ww, test_date)as weeknumber
from datetest
where datepart(ww, test_date)<= datepart(ww,'2012-01-02')
order by datepart(yyyy, test_date) desc,
datepart(ww, test_date) desc)
AND datepart(YYYY, test_date) in(select top 1 datepart(yyyy, test_date)as weeknumber
from datetest
where datepart(ww, test_date)<= datepart(ww,'2012-01-02')
order by datepart(yyyy, test_date) desc,
datepart(ww, test_date) desc)

But I am not getting any output. Any idea please.

Thanks