Week Wise Stored Procedure

I prefer writing where clause as below to take advantage of index if present in date field. All other method would ignore an index if present as search clause is non sargable due to usage of functions like MONTH etc on date field

USE [WH4_2008]
GO
/****** Object:  StoredProcedure [dbo].[Production_WeekSplit]    Script Date: 01/03/2015 17:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Production_WeekSplit]
@monthparam int, @yearparam int
AS
BEGIN
        SET NOCOUNT ON;

select DATEADD(wk,DATEDIFF(wk,0,[Date]),0)  as WeekStart,
DATEADD(wk,DATEDIFF(wk,0,[Date])+1,-1) as WeekEnd,
SUM(Plan1) as Plan1,
SUM(Actual) as Actual,
SUM(Variance) as Variance
From Production
Where [Date] >= DATEADD(mm,(@yearparam - 1900) * 12 + @monthparam - 1 ,0)
AND [Date] < DATEADD(mm,(@yearparam - 1900) * 12 + @monthparam,0)
Group by DATEDIFF(wk,0,[Date])
END

Also see

http://ift.tt/1vdYEBr

---

Please Mark This As Answer if it solved your issue

Please Vote This As Helpful if it helps to solve your issue

Visakh

----------------------------

My Wiki User Page

My MSDN Page

My Personal Blog

My Facebook Page