How to get no of working day/s between two date use SQL query.

CREATE FUNCTION NoOfWorkingDays

(

      @StartDate  DATE,

      @EndDate    DATE

)

RETURNS int

AS

BEGIN

      DECLARE @days INT

      SELECT @days =  (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)

      RETURN @days

END

SQL query for find the difference between two tables data

USE [ERM_CUSTOM]

GO

/****** Object:  StoredProcedure [dbo].[ERM_MSP_ ResourceCompareAndUpdate]    Script Date: 8/17/2016 7:52:02 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Exec  [dbo].[ERM_MSP_ ResourceCompareAndUpdate]

ALTER PROCEDURE [dbo].[ERM_MSP_ ResourceCompareAndUpdate]  

AS

BEGIN

SELECT  ResourceUID,

        ResourceName,

        ResourceEmailAddress,

        WWID 

INTO #TempResource

FROM [ERM].[MSP_EpmResource_UserView] -- Gets all data from view and Stored on a temp table.

CREATE TABLE #TempDifferenceResource (    ResourceUID nvarchar(max), 

                                        ResourceName nvarchar(max),

                                        ResourceEmialAddrss nvarchar(max),

                                        WWID nvarchar(max)

                                    ) --- create another temp table

  

  INSERT INTO   #TempDifferenceResource

     SELECT * FROM #TempResource 

        EXCEPT

    SELECT * FROM [dbo].[ResouceCompare]  -- insert difference data to the temp table

UPDATE

    base

SET

    base.ResourceName = tempDiff.ResourceName,

    base.ResourceEmialAddrss = tempDiff.ResourceEmialAddrss ,

    base.WWID = tempDiff.WWID

FROM

    [dbo].[ResouceCompare] base

    JOIN

    #TempDifferenceResource tempDiff 

ON    base.ResourceUID=tempDiff.ResourceUID -- update existing date to base table

insert into [dbo].[ResouceCompare]

select tempDiff.ResourceUID,tempDiff.ResourceName,tempDiff.ResourceEmialAddrss,tempDiff.WWID FROM

    [dbo].[ResouceCompare] base

    JOIN

    #TempDifferenceResource tempDiff 

ON    base.ResourceUID<>tempDiff.ResourceUID -- insert new data to base table

select * from  [dbo].[ResouceCompare]

DROP TABLE #TempResource

DROP TABLE #TempDifferenceResource

END

How to reset identity value in a table in sql.

Go

SET IDENTITY_INSERT [dbo].[MasterDocuments] ON

GO

insert into [dbo].[MasterDocuments](DocId,delTypeId,DocName)

values(203,9,'SQA Waterfall Gate Assessment Checklist')

GO

SET IDENTITY_INSERT [dbo].[MasterDocuments] OFF

GO

SQL query for find the difference between two tables data

USE [ERM_CUSTOM]

GO

/****** Object:  StoredProcedure [dbo].[ERM_MSP_ ResourceCompareAndUpdate]    Script Date: 8/17/2016 7:52:02 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Exec  [dbo].[ERM_MSP_ ResourceCompareAndUpdate]

ALTER PROCEDURE [dbo].[ERM_MSP_ ResourceCompareAndUpdate]  

AS

BEGIN

SELECT  ResourceUID,

        ResourceName,

        ResourceEmailAddress,

        WWID 

INTO #TempResource

FROM [ERM].[MSP_EpmResource_UserView] -- Gets all data from view and Stored on a temp table.

CREATE TABLE #TempDifferenceResource (    ResourceUID nvarchar(max), 

                                        ResourceName nvarchar(max),

                                        ResourceEmialAddrss nvarchar(max),

                                        WWID nvarchar(max)

                                    ) --- create another temp table

  

  INSERT INTO   #TempDifferenceResource

     SELECT * FROM #TempResource 

        EXCEPT

    SELECT * FROM [dbo].[ResouceCompare]  -- insert difference data to the temp table

UPDATE

    base

SET

    base.ResourceName = tempDiff.ResourceName,

    base.ResourceEmialAddrss = tempDiff.ResourceEmialAddrss ,

    base.WWID = tempDiff.WWID

FROM

    [dbo].[ResouceCompare] base

    JOIN

    #TempDifferenceResource tempDiff 

ON    base.ResourceUID=tempDiff.ResourceUID -- update existing date to base table

insert into [dbo].[ResouceCompare]

select tempDiff.ResourceUID,tempDiff.ResourceName,tempDiff.ResourceEmialAddrss,tempDiff.WWID FROM

    [dbo].[ResouceCompare] base

    JOIN

    #TempDifferenceResource tempDiff 

ON    base.ResourceUID<>tempDiff.ResourceUID -- insert new data to base table

select * from  [dbo].[ResouceCompare]

DROP TABLE #TempResource

DROP TABLE #TempDifferenceResource

END