SET DATEFIRST 1
SELECT WEEK = DATEADD(day, -1 * DATEPART (dw, ProductionDate), ProductionDate), ProductionDate
FROM tbHollowcoreBedSchedule

quote:

---

Originally posted by Srinika

Can u post some sample data and the expected output

Srinika

---

SELECT WEEK = DateAdd(d, -((@@DATEFIRST + DatePart(dw, ProductionDate) -2) % 7), ProductionDate
FROM tbHollowcoreBedSchedule

CREATE PROCEDURE rsp_release_tasks_by_week_number_static
								@SUBJECT varchar(255)=NULL,
								@AUTHOR varchar(255)=NULL,
								@PROJECTNAME varchar(255)=NULL,
								@CATEGORY varchar(255)=NULL,
								@StartDate datetime=NULL,
								@EndDate datetime = NULL
AS

SELECT DatePart(ww, pt.EXPECTED_START_DATE) As WeekNumb,
SUM (CASE WHEN pt.TEXT_29='BOM-E' THEN 1 ELSE 0 END) AS [BOM-E],
SUM (CASE WHEN pt.TEXT_29='BOM-M' THEN 1 ELSE 0 END) AS [BOM-M],
SUM (CASE WHEN pt.TEXT_29='BOM-F' THEN 1 ELSE 0 END) AS [BOM-F],
SUM (CASE WHEN pt.TEXT_29='R-GM' THEN 1 ELSE 0 END) AS [R-GM],
SUM (CASE WHEN pt.TEXT_29='RLL-E' THEN 1 ELSE 0 END) AS [RLL-E],
SUM (CASE WHEN pt.TEXT_29='RLL-F' THEN 1 ELSE 0 END) AS [RLL-F],
SUM (CASE WHEN pt.TEXT_29='RLL-M' THEN 1 ELSE 0 END) AS [RLL-M],
SUM (CASE WHEN pt.TEXT_29='RXLL' THEN 1 ELSE 0 END) AS [RXLL],
SUM (CASE WHEN pt.TEXT_29='REC' THEN 1 ELSE 0 END) AS [REC],
SUM (CASE WHEN pt.TEXT_29='S-BOM-M' THEN 1 ELSE 0 END) AS [S-BOM-M],
SUM (CASE WHEN pt.TEXT_29='S-BOM-E' THEN 1 ELSE 0 END) AS [S-BOM-E],
SUM (CASE WHEN pt.TEXT_29='S-REC' THEN 1 ELSE 0 END) AS [S-REC] 
FROM PROJ_TASK pt INNER JOIN PROJECT p ON 
		p.PROJECTID=pt.PROJECTID
	WHERE 
(pt.EXPECTED_START_DATE BETWEEN isnull(DATEADD(d, DATEDIFF(d, 0, @StartDate), 0), '19000101') AND isnull(DATEADD(d, DATEDIFF(d, 0, @EndDate), 0), '99990101')) 
AND (@SUBJECT IS NULL OR p.SUBJECT = @SUBJECT)
AND (@AUTHOR IS NULL OR p.AUTHOR = @AUTHOR)
AND (@CATEGORY IS NULL OR p.CATEGORY = @CATEGORY)
AND (@PROJECTNAME IS NULL OR pt.PROJECTNAME=@PROJECTNAME)
AND pt.TASK_TYPE = 0 
AND pt.TASK_STATUS <> 2 
AND p.PROJECT_TYPE = 2  
GROUP BY DatePart(ww, pt.EXPECTED_START_DATE)
ORDER BY WeekNumb
Set NOCOUNT OFF
GO