How to Calculate Working Days using Store procedure (SP)

By:Mohammad Tuaha 
 

Store procedure SQL server

Today I like to share working days calculation using store procedure T-SQL. Working days calculation need for salary sheet, Employee leave and attendance report.

Only work Days Calculation

      DECLARE @StartDate DATETIME = '2015/08/01'

      DECLARE @EndDate DATETIME = '2015/08/31'

SELECT

(DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -

   (

            CASE

                  WHEN DATENAME(dw, @StartDate) = 'Sunday'

                  THEN 1

                  ELSE 0

            END

      )  -

      (

            CASE

                  WHEN DATENAME(dw, @EndDate) = 'Saturday'

                  THEN 1

                  ELSE 0

            END

      )

As [WorkDays]

Only Holidays Calculation

First create holiday’s table

CREATE TABLE [dbo].[tbl_lms_holidayList](

      [ID] [bigint] IDENTITY(1,1) NOT NULL,

      [HolidayName] [varchar](150) NULL,

      [Holiday] [date] NOT NULL,

      [HoliOfYear] [varchar](50) NULL,

      [Remark] [varchar](150) NULL,

      [Status] [int] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[tbl_lms_holidayList] ADD  CONSTRAINT [DF_tbl_lms_holidayList_Status]  DEFAULT ((1)) FOR [Status]

GO

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

DECLARE @StartDate DATETIME = '2015/01/01'

DECLARE @EndDate DATETIME = '2015/08/31'

(Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate

        and (DATENAME(dw, @StartDate) != 'Saturday' and     DATENAME(dw, @StartDate) != 'Sunday')

        and (DATENAME(dw, @EndDate) != 'Saturday' and       DATENAME(dw, @EndDate) != 'Sunday')

        )    

     

OR

DECLARE @StartDate DATETIME = '2015/01/01'

DECLARE @EndDate DATETIME = '2015/08/31'

SELECT

  (Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate and Status = 1)

Work Days Without Holidays [Total days – holidays]

DECLARE @StartDate DATETIME = '2015/08/01'

DECLARE @EndDate DATETIME = '2015/08/31'

SELECT

   (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -

   (

         CASE

                  WHEN DATENAME(dw, @StartDate) = 'Sunday'

                  THEN 1

                  ELSE 0

            END

      )  -

      (

            CASE

                  WHEN DATENAME(dw, @EndDate) = 'Saturday'

                  THEN 1

                  ELSE 0

            END

      )    

      -  (Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate

        and (DATENAME(dw, @StartDate) != 'Saturday' and     DATENAME(dw, @StartDate) != 'Sunday')

        and (DATENAME(dw, @EndDate) != 'Saturday' and       DATENAME(dw, @EndDate) != 'Sunday'))   

       as [WorkDays]

 

Full Store Procedure

Create  PROCEDURE [dbo].[SP_Calculating_workdays]

            @StartDate DATETIME ,

            @EndDate DATETIME

       

AS

BEGIN            

      --    DECLARE @StartDate DATETIME = '2019/09/01'

      --    DECLARE @EndDate DATETIME = '2019/09/30'

SELECT

   (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -

   (

         CASE

                  WHEN DATENAME(dw, @StartDate) = 'Sunday'

                  THEN 1

                  ELSE 0

            END

      )  -

      (

            CASE

                  WHEN DATENAME(dw, @EndDate) = 'Saturday'

                  THEN 1

                  ELSE 0

            END

      )    

      -  (Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate

        and (DATENAME(dw, @StartDate) != 'Saturday' and DATENAME(dw, @EndDate) != 'Sunday')

        and (DATENAME(dw, @EndDate) != 'Saturday' and DATENAME(dw, @StartDate) != 'Sunday'))

       

       as [WorkDays]

                               

END

     

File Upload is not working with updatepanel - Asp.net C#

By: Mohammad Tuaha

Problem:
File upload is not working with updatepanel + asp.net, C#. or
i can't upload image for ajax updatepanel.

Solution:
 Add this line is not properly work in every time in Page load
    Page.Form.Attributes.Add("enctype", "multipart/form-data");

    protected void Page_Load(object sender, EventArgs e)
    {
       Page.Form.Attributes.Add("enctype", "multipart/form-data");
    }

Best Solution:
If you are using updatepanel in  master page no problem u have to use another updatepanel with different ID. Just u have to use
Add the trigger for the UpdatePanel and give the ControlID.  
       Triggers  asp:PostBackTrigger   ControlID="Button1"   /Triggers

 Please follow the code below
Default.aspx page
=====================================
 

Default.aspx.cs page
==========================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class AddImage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       // Page.Form.Attributes.Add("enctype", "multipart/form-data");
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

        if (FileUpload1.HasFile)
        {
            if (extension == ".png")
            {
                string strPath = MapPath("User_Photo/") + fileName + extension;
                FileUpload1.SaveAs(strPath);
                 lblmessage.Text = "Successfully Saved";
            }
            else
            {
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Only .Png Format can be support')", true);
            }
        }
      
    }
}

Download Project