Date Range for Previous Week

How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!

Code Snippet

CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]

(@.Classification varchar(50))

AS

BEGIN

SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,

RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],

RC_STAT.dbo.Brand_Dimension.Item_Merged,

Qry_Sales_Group.Region_Key,

Qry_Sales_Group.Region,

Qry_Out_Of_Stock.product_structure_level,

Qry_Out_Of_Stock.product_entity_code,

Qry_Out_Of_Stock.cycle_day,

Qry_Out_Of_Stock.customer_code,

Qry_Out_Of_Stock.description,

Qry_Sales_Group.Code,

Qry_Sales_Group.SR_Name,

Qry_Sales_Group.Name AS Territory_Name,

Qry_Out_Of_Stock.Store_Name,

Qry_Out_Of_Stock.time_log,

Qry_Out_Of_Stock.out_of_stock,

Period_Code

FROM RC_STAT.dbo.Brand_Dimension INNER JOIN

dbo.Qry_Out_Of_Stock ON

RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN

dbo.Qry_Sales_Group ON

dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS

Where Classification=@.Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =

case

when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3

else datediff(dd,0,GetDate()) - 1

end

END

SET NOCOUNT OFF

Here it is,

Code Snippet

CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]

(@.Classification varchar(50))

AS

BEGIN

SELECT

RC_STAT.dbo.Brand_Dimension.Report_Level,

RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],

RC_STAT.dbo.Brand_Dimension.Item_Merged,

Qry_Sales_Group.Region_Key,

Qry_Sales_Group.Region,

Qry_Out_Of_Stock.product_structure_level,

Qry_Out_Of_Stock.product_entity_code,

Qry_Out_Of_Stock.cycle_day,

Qry_Out_Of_Stock.customer_code,

Qry_Out_Of_Stock.description,

Qry_Sales_Group.Code,

Qry_Sales_Group.SR_Name,

Qry_Sales_Group.Name AS Territory_Name,

Qry_Out_Of_Stock.Store_Name,

Qry_Out_Of_Stock.time_log,

Qry_Out_Of_Stock.out_of_stock,

Period_Code

FROM

RC_STAT.dbo.Brand_Dimension

INNER JOIN dbo.Qry_Out_Of_Stock

ON RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS

Where

Classification=@.Classification

And dbo.Qry_Out_Of_Stock.time_log >= dateadd(dd,-6,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))), --Monday

And dbo.Qry_Out_Of_Stock.time_log < dateadd(dd,-1,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))) -- Less than Saturday

END

SET NOCOUNT OFF

|||Thanks!! now how would i do this for periods. I do have a period field, but its a Char so i cant really do the (-1) . What would be a good aproach for this, is there a way i can have the timelog=period, each period has a date.|||What do you meant "Period", can you provide some sample data (both input & expected output)

|||

I have a "Period_code" Field in the stored procedure. The field is a char. And looks like this 200708 (for period 8). Should i convert this number to an INT and then do a -1? for the where clause. And do a trim, to just grab the last 2 digits.

I want to get all the records for the previous period. So if today is period 200709 , i want period 200708 etc etc. I hope this helps.

Also how would i get it to realized that today=period 200709 or whatever period it is, on the date the report is run. Im trying to find a way to link these.