Assuming you need to retrieve 35 days ago of records since last Sunday.
====================================================
DECLARE @day INT
DECLARE @reqdate INT
DECLARE @status int
DECLARE @today SMALLDATETIME
--This SQL is to retrieve 35 days ago data from the last Sunday of the current date.
--Replace 35 with the X no of days that you wish to retrieve
SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
set @reqdate = (select DATEPART(dw, getdate()))
set @status = CASE
when @reqdate=1 then '0'
WHEN @reqdate=2 then '1'
WHEN @reqdate=3 then '2'
WHEN @reqdate=4 then '3'
WHEN @reqdate=5 then '4'
WHEN @reqdate=6 then '5'
WHEN @reqdate=7 then '6'
END
select col1, col2, col3, etc...
FROM tableABC
where (submit_date between dateadd(dd, datediff(dd,35, getdate()-@status),0) and getdate()-@status )
ORDER BY 1
