Wednesday, August 26, 2015

Get working weekdays rage from given date DAY,5,@fromdate means 6 days working week


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