Write recursive t-sql code to find NTILE / DECILE / Quintile based on Salary descending for employee in the Department.

Write a query which will give below output.

There’s a table with below data

Query:

Declare @v_Employee as table(DeptID int, EmpNo int, Salary decimal(18,2))

Declare @v_Ntile int

Set @v_Ntile = 2

Insert into @v_Employee values(1,1,1000)
Insert into @v_Employee values(1,2,2000
Insert into @v_Employee values(1,3,3000)
Insert into @v_Employee values(2,4,4000)
Insert into @v_Employee values(2,5,5000)
Insert into @v_Employee values(2,6,6000)
Insert into @v_Employee values(2,7,7000)

Select * From @v_Employee

Select 
    DeptID, 
    EmpNo, 
    Ntile(@v_Ntile) Over(Partition By DeptID Order By Salary  Desc)  NtileNo
From 
    @v_Employee

Note:

1) To find Decile value, set Ntile value to 10.
2) To find Quin-tile (Quintile) value, set Ntile value to 5.

Write recursive t-sql code to fill missing Salary for employee using Average for the Department.

There’s a table with below data

Write a query which will give below output.

Query:

Declare

@v_Employee as table(DeptID int, EmpNo int, Salary decimal(18,2))

Insert into @v_Employee values(1,1,1000)

Insert into @v_Employee values(1,2,null)

Insert into @v_Employee values(1,3,3000)

Insert into @v_Employee values(2,4,4000)

Insert into @v_Employee values(2,5,null)

Insert into @v_Employee values(2,6,6000)

Insert into @v_Employee values(2,7,7000)

Select * From @v_Employee

Select

T.DeptID,

EmpNo,

Case when Salary is not null then Salary else AvgSalary end

From

(

 select
 DeptID,
 avg(Salary) as AvgSalary
 from
 @v_Employee
 Group By
 DeptID
) T
inner join @v_Employee E on E.DeptID = T.DeptID

Write recursive t-sql code to generate reverse of given string

Create Function [dbo].[fn_Reverse](@v_Input nvarchar(max))
Returns nvarchar(max)
as
Begin
If (len(@v_Input) <> 1)
Begin
return [dbo].[fn_Reverse](substring(@v_Input, 2, len(@v_Input) - 1)) + substring(@v_Input, 1, 1)
End
return @v_Input
End
GO

select [dbo].[fn_Reverse]('acbdkkdfkdkf')
Go

--> fkdkfdkkdbca

Write recursive t-sql code to get Factorial of integer number

Create Function dbo.fn_Factorial(@v_Number bigint)

Returns bigint

as

BeginIf (@v_Number != 1)
go
Select dbo.fn_Factorial(6)

Begin

return @v_Number * dbo.fn_Factorial(@v_Number - 1)

End

return 1

End

go

Write T-SQL code to take m valid values from User and provide all valid Sudoku possibilities for nXn matrix

Continue to my last post “Write T-SQL code to provide all valid Sudoku possibilities for nXn matrix.

I modified the procedure sc.cp_Create_MetrixPosibilities_nXn which also take m valid values from User for matrix and create table dbo.MetrixPosibilities_nXn with all valid Sudoku possibilities.

Below are parameters for the procedure sc.cp_Create_MetrixPosibilities_nXn.

1. @p_MatrixSize
   1. Size of matrix for which we want to create all the possibilities.
2. @v_PositionMatrixValue
1. Table Value Parameter which is used to pass valid user entries. Below is structure of TVP.
1. PositionX
2. PositionY
3. Value

Suppose, we have 4X4 matrix with below values for which I want to create all valid possibilities

We can execute procedure as below.

Declare @v_PositionMatrixValue as PositionMatrixValue

Insert into @v_PositionMatrixValue values(1,1,1)
Insert into @v_PositionMatrixValue values(2,2,2)
Insert into @v_PositionMatrixValue values(3,3,3)
Insert into @v_PositionMatrixValue values(4,4,4)

EXEC sc.cp_Create_MetrixPosibilities_nXn 4, @v_PositionMatrixValue, 0
Go

Select * From MetrixPosibilities_4X4

Here, we have 2 valid possibilities created as below in table dbo.MetrixPosibilities_4X4.

Below are the scripts to download.

1. sc.cp_Create_MetrixPosibilities_nXn_V2.sql

Write T-SQL code to provide all valid Sudoku possibilities for nXn matrix.

This post specially dedicated to Anjana Ramamoorthy from Microsoft.

Below is the procedure sc.cp_Create_MetrixPosibilities_nXn which will take matrix size as parameter and create the table dbo.MetrixPosibilities_nXn with all valid Sudoku possibilities.

Below are parameters for the procedure sc.cp_Create_MetrixPosibilities_nXn.

1. @p_MatrixSize
1. Size of matrix for which we want to create all the possibilities.

Suppose, we have 3X3 matrix for which I want to create all valid possibilities then we can execute procedure as below.

EXEC sc.cp_Create_MetrixPosibilities_nXn 3
Go
Select count(1) From dbo.MetrixPosibilities_3X3
--> 12

Here, we have 12 valid possibilities created as below in table dbo.MetrixPosibilities_3X3.

Below are the scripts to download.

1. sc.cp_Create_MetrixPosibilities_nXn.sql

Please keep watching for the second post.

Write T-SQL code to take m valid values from User and provide all valid Sudoku possibilities for nXn matrix