How To Calculate Working Minutes in Without Week Off and Holidays SQL Server

-

       In this Section See about We to Calculate Working Times in Without WeekOffs and Non-Working Days. In this SQL Query, I mentioned in holiday Table as Leave Days.  You  Can Set the Work Start time and End Time In your Code. 

 Declare @StartDate as datetime
 set @StartDate ='2017-11-01 09:59:50.753'

 Declare @EndDate as datetime
 set @EndDate='2017-11-03 09:59:50.753'


       DECLARE @WorkStartTime TIME
       SET @WorkStartTime ='09:30'

    DECLARE @WorkEndTime TIME
    SET @WorkEndTime = '18:00'

 DECLARE @WorkingSecs BIGINT
    SET @WorkingSecs=0

    DECLARE @FirstDay DATE
    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

    DECLARE @LastDay DATE
    SET @LastDay = CONVERT(DATE, @EndDate, 112)

    DECLARE @StartTime TIME
    SET @StartTime = CONVERT(TIME, @StartDate)

    DECLARE @FinishTime TIME
    SET @FinishTime = CONVERT(TIME, @EndDate)

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStartTime, @WorkEndTime)

    IF (@StartTime<@WorkStartTime)
    BEGIN
        SET @StartTime = @WorkStartTime
    END
    IF (@FinishTime>@WorkEndTime)
    BEGIN
        SET @FinishTime=@WorkEndTime
    END
    IF (@FinishTime<@WorkStartTime)
    BEGIN
        SET @FinishTime=@WorkStartTime
    END
    IF (@StartTime>@WorkEndTime)
    BEGIN
        SET @StartTime = @WorkEndTime
    END

    DECLARE @CurrentDate DATE
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATE
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN  
           IF (DATEPART(dw, @CurrentDate)!=7 AND DATEPART(dw, @CurrentDate)!=1 and @CurrentDate
              not in (select mHolyDay.FestivalDate from mHolyDay))
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @WorkingSecs = @WorkingSecs + @DailyWorkTime
            END
  
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @WorkingSecs = @WorkingSecs + DATEDIFF(SECOND, @StartTime, @WorkEndTime)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @WorkingSecs = @WorkingSecs + DATEDIFF(SECOND, @WorkStartTime, @FinishTime)
            END
          
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @WorkingSecs = DATEDIFF(SECOND, @StartTime, @FinishTime)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    IF @WorkingSecs<0
    BEGIN
        SET @WorkingSecs=0
    END

       select @WorkingSecs
       SELECT CONVERT(varchar, DATEADD(ms, @WorkingSecs * 1000, 0), 114)



Comments

Post a Comment

Thank You for your Comment

Popular posts from this blog

Unable to perform operation on the item is locked in workspace

-
Image
Hi,  I am getting this issue last week. so I Fixed. I Will share the Solution. Some files struggle with another person's systems workspace. then you can't able to check-in in your TFS. The particular file was blocked or Locked in another person system so easily remove that person Workspace issue will be solved you can check-in your file into the TFS. tf workspace /delete /server:http://192.168.1.50:8080/tfs/projectname NCS-P01-001_2;R****h
Read more

Maximum Stored Procedure Function Trigger or View Nesting Level Exceeded (limit 32) in SQL Server

-
Image
                  In SQL Server I am facing some error on when it Runs Procedure. That as Maximum Stored Procedure Function Trigger or View Nesting Level Exceeded (limit 32) in SQL Server. Reason:                       user-defined function begins execution and decreases by one when the called stored procedure, managed code reference, or user-defined function completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail and for this error message to be raised.   Solution: To Run this code in Your SQL SERVER Avoid In this Error. SP_CONFIGURE 'nested_triggers' ,0 GO RECONFIGURE GO
Read more

Insecure cookie setting: missing Secure flag

-
  Issue Description: Insecure Cookie Setting - Missing Secure Flag Explanation: The "Secure" flag is an attribute that can be set for cookies in web applications. When the Secure flag is enabled for a cookie, the cookie is only transmitted over HTTPS connections, ensuring that it is sent securely over an encrypted channel. This is crucial for security because it helps prevent the exposure of sensitive information contained in cookies, such as session tokens or authentication credentials, to potential attackers. When the Secure flag is missing from a cookie, it means that the cookie can be sent over both secure (HTTPS) and non-secure (HTTP) connections. This can pose a significant security risk, as sensitive information could be exposed if the cookie is transmitted over an insecure connection. Impact: The impact of missing the Secure flag on a cookie depends on the context in which the cookie is used. Here are some potential risks: Session Hijacking: If an attacker can inter...
Read more