split string to TABLE

 

ALTER FUNCTION [pksf].[fn_SplitString]

(    

      @Input NVARCHAR(MAX),

      @Character CHAR(1)

)

RETURNS @Output TABLE (

      Item NVARCHAR(1000)

)

AS

BEGIN

      DECLARE @StartIndex INT, @EndIndex INT

 

      SET @StartIndex = 1

      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character

      BEGIN

            SET @Input = @Input + @Character

      END

 

      WHILE CHARINDEX(@Character, @Input) > 0

      BEGIN

            SET @EndIndex = CHARINDEX(@Character, @Input)

           

            INSERT INTO @Output(Item)

            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

           

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

      END

 

      RETURN

END

Salary Second Highest

Second Highest Salary Department Wise. Group by 

select * from

(

select EmpId, EmpName, Salary, Department,

ROW_NUMBER() Over (Partition by Department Order by Salary Desc) AS SalaryRank

from Emp

) dd

where SalaryRank = 2 

Next Report: Update Table

 

step 1: 

declare @sql varchar(max);

DECLARE @DateName nvarchar(100)

SET @DateName=  '[NextReport].[dbo].NXTRPTEmployeeProfile_' + CAST( GetDate()  as NVARCHAR(12) );

SELECT @DateName = REPLACE(@DateName, ' ', '')

 --SELECT @DateName

 Set @sql = 'SELECT * INTO ' +  @DateName +' FROM [NextReport].[dbo].NXTRPTEmployeeProfile ';

 EXECUTE(@sql)

 

 

 step 2: 

 

 Drop Table [NextReport].[dbo].[NXTRPTEmployeeProfile]

 

 

 Step: 

 if (SELECT count(1) FROM NXTActiveEmployee) > 0

BEGIN

SELECT 1

END

ELSE

BEGIN

TRUNCATE TABLE NXTActiveEmployee

INSERT INTO NXTActiveEmployee

SELECT EmployeeCode FROM Employee WHERE EmployeeStatus = 'A'

   

   END

All Steps For Next Report Table Update

 

--Step : 1

declare @sql varchar(max);

DECLARE @DateName nvarchar(100)

SET @DateName=  'NXTRPTEmployeeProfile' + CAST( GetDate()  as NVARCHAR(12) );

SELECT @DateName = REPLACE(@DateName, ' ', '')

 

 Set @sql = 'SELECT * INTO ' +  @DateName +' FROM NXTRPTEmployeeProfile ';

 EXECUTE(@sql)

 --step 2: 

 Drop Table NXTRPTEmployeeProfile

 --step 3:

 SELECT * INTO NXTRPTEmployeeProfile FROM EmpProfile

 

NextReport Update Table

 declare @sql varchar(max);

DECLARE @DateName nvarchar(100)

SET @DateName=  '[NextReport].[dbo].NXTRPTEmployeeProfile' + CAST( GetDate()  as NVARCHAR(12) );

SELECT @DateName = REPLACE(@DateName, ' ', '')

 --SELECT @DateName

 Set @sql = 'SELECT * INTO ' +  @DateName +' FROM [NextReport].[dbo].NXTRPTEmployeeProfile ';

 EXECUTE(@sql)

 

Backup Tables by Date

 

declare @sql varchar(max);

DECLARE @DateName nvarchar(100)

SET @DateName=  'NXTRPTEmployeeProfile' + CAST( GetDate()  as NVARCHAR(12) );

SELECT @DateName = REPLACE(@DateName, ' ', '')

 

 Set @sql = 'SELECT * INTO ' +  @DateName +' FROM NXTRPTEmployeeProfile ';

 EXECUTE(@sql)

httpclient request with Access Token and payloads:

 httpclient request with Access Token and payloads:

 var param = new

            {

                documentNumber = "1317915213594",

                motherName = "Amena",

                dateOfBirth = "01/23/1985"

            };

var dataString = JsonConvert.SerializeObject(param);

            string accessToken_ = userDatas.accessToken;

            var client = new HttpClient();

            var request = new HttpRequestMessage(HttpMethod.Post, "http://104.198.53.36:8080/UW/app/erp/duplicationCheck");

            request.Headers.Add("Authorization", accessToken_);

            var content_ = new StringContent(dataString, null, "application/json");

            request.Content = content_;

            var response_ = await client.SendAsync(request);

            response_.EnsureSuccessStatusCode();