Hi Friends, I was getting the result for selected week... at the same time I would like to show the field for last year same week like this this is the code ALTER PROCEDURE [dbo].[KPI_WORK_IN_PROGESS] @RetailWeek varchar(6), @reportType AS char(1) AS BEGIN SET NOCOUNT ON; DECLARE @MinRetailWeek AS Varchar(6), @qtr AS varchar(7), @monthStartDate AS datetime SELECT @qtr=qtr FROM tRetailWeeks WHERE AgrWeek=@RetailWeek SELECT @monthStartDate=Month_Start_Date FROM tRetailWeeks WHERE AgrWeek=@RetailWeek SELECT @MinRetailWeek = CASE WHEN @reportType='Y' THEN LEFT(@RetailWeek,4)+'01' WHEN @reportType='Q' THEN (SELECT MIN(AgrWeek) FROM tRetailWeeks WHERE qtr=@qtr GROUP BY qtr) WHEN @reportType='M' THEN (SELECT AgrWeek FROM tRetailWeeks WHERE Date=@monthStartDate) WHEN @reportType='W' THEN @RetailWeek END SELECT Stores.Trading_Region_ID , CASE WHEN Stores.Trading_Region_ID='0010' AND LEFT(RIGHT(Stores.Location_Type_ID,4),2)='20' THEN 'UK Concessions' WHEN Stores.Trading_Region_ID='0010' AND (LEFT(RIGHT(Stores.Location_Type_ID,4),2)='30' OR LEFT(RIGHT(Stores.Location_Type_ID,4),2)='40') THEN 'UK Other' WHEN Stores.Trading_Region_ID='0010' THEN 'UK Main Stores' WHEN Stores.Trading_Region_ID='0020' THEN 'USA' WHEN Stores.Trading_Region_ID='0030' THEN 'Europe' WHEN Stores.Trading_Region_ID='0080' THEN 'Canada' END AS Trading_Region , RIGHT(Stores.Location_Type_ID,4) AS Location_Type_ID , CASE WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='20' THEN 'Concession' WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='30' THEN 'Outlet' WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='40' THEN 'Other' WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='50' THEN 'Airport' WHEN Stores.Trading_Region_ID='0020' THEN 'USA' WHEN RIGHT(Stores.Location_Type_ID,2)='10' THEN 'Financial District' WHEN RIGHT(Stores.Location_Type_ID,2)='20' THEN 'Central' WHEN RIGHT(Stores.Location_Type_ID,2)='30' THEN 'Region' END AS Location_Type , Trading.Store_ID , Stores.Store_Name , SUM(Net_Amount+Tax_Amount) AS Amount , SUM(Transactions) AS Transactions , SUM(Quantity) AS Quantity FROM Sales.Stores_Weekly_Trading AS Trading INNER JOIN Stores AS Stores ON Trading.Store_ID=RIGHT('000' + RTRIM(Stores.Store_ID),5) WHERE Retail_Week BETWEEN @MinRetailWeek AND @RetailWeek GROUP BY Stores.Trading_Region_ID, Trading.Store_ID, Stores.Location_Type_ID, Stores.Store_Name ORDER By Store_ID END Could any one help me out Pleaseeeeeeeeee....Thank you

Hi bandlamudi123, Thanks for your post. According to your description, you would like to show the specified week data of both the current year and last year, if so I would suggest you to use the union keyword to combine them together, and then show them on you report, you can refer to the T-SQL code below: select Region_ID, Location_Type_ID,Amount,Quatity from tbl where Year(Datetime_Col)=2011 and Retail_Week BETWEEN @MinRetailWeek AND @RetailWeek union select Region_ID, Location_Type_ID,Amount,Quatity from tbl where Year(Datetime_Col)=2010 and Retail_Week BETWEEN @MinRetailWeek AND @RetailWeek Datetime_Col is the column in your table which stored the datatime. If I misunderstand your issue, please do not hesitate to let me know. Thanks, Bill LuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.

hi badlamudi did you solved ur issue.indu

Read Peter's great article to get an idea http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspxBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/