GO
/****** Object: StoredProcedure [dbo].[KPI_SplitStartEndDateInTimePeriod] Script Date: 08/26/2015 21:06:35 ******/--[dbo].[KPI_SplitStartEndDateInTimePeriod_Backup20150826]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--KPI_SplitStartEndDateInTimePeriod_Backup20150826
alter PROC [dbo].[KPI_SplitStartEndDateInTimePeriod]
(
@pfromdate DATETIME,
@ptodate DATETIME,
@pTimePeriod int,
@pOpCode int =0,
@pExtraParameter varchar(100) = ''
)
AS
BEGIN
BEGIN TRY
/*****************Testing*************************
Exec KPI_SplitStartEndDateInTimePeriod '2015-08-03 00:00:00.000','2015-08-31 00:00:00.000',0
******************Testing End*********************/
DECLARE
@fromdate DATETIME = @pfromdate,
@todate DATETIME = @ptodate;
DECLARE @Result TABLE (
Applicable_starting DATETIME
,Applicable_ending DATETIME
)
SET @fromdate = DATEADD(WEEK,DATEDIFF(WEEK,0,@fromdate),0) --start of week from a year ago
SET @todate = DATEADD(WEEK,DATEDIFF(WEEK,0,@todate),0) --start of current partial week;
DECLARE @weeks TABLE (
weekID INT IDENTITY(1,1)
,week_starting DATETIME
,week_ending DATETIME
)
WHILE DATEPART(wk, @fromdate) <= DATEPART(wk,@todate)
BEGIN
INSERT INTO @weeks (week_starting,week_ending) VALUES (@fromdate,DATEADD(DAY,5,@fromdate))
SET @fromdate = DATEADD(DAY,7,@fromdate)
END
DECLARE @vMaxWeekID INT = (SELECT MAX(weekID) FROM @weeks)
DECLARE @vFirstWeek INT = ISNULL(
(SELECT weekID FROM @weeks WHERE CONVERT(VARCHAR(11),@pfromdate,111) = CONVERT(VARCHAR(11),week_starting,111)AND weekID =1),2)
DECLARE @vLastWeek INT = ISNULL(
(SELECT weekID FROM @weeks WHERE CONVERT(VARCHAR(11),@ptodate,111) = CONVERT(VARCHAR(11),week_ending,111)AND weekID = @vMaxWeekID),(@vMaxWeekID-1))
INSERT INTO @Result(Applicable_starting,Applicable_ending)
SELECT week_starting,week_ending FROM @weeks WHERE weekID BETWEEN @vFirstWeek AND @vLastWeek
SELECT Applicable_starting,Applicable_ending FROM @Result
END TRY
BEGIN CATCH
END CATCH
END
