A simple and another way to find rows on n’th maximum number from a table.

Introduction
It’s a very simple trick to get to get rows on n’th maximum number from a table. In this tip i used common table expression (CTE) and DENSE_RANK() function. To know more about CTE click this link and for DENSE_RANK() click this link.

Using the code
A simple code snippet is given below.
–START TO GET N’TH HIGHEST NUMBER/SALARY

DECLARE @g_query AS VARCHAR(256)
DECLARE @g_nth_highest AS INT
DECLARE @g_nth_row AS INT

SET @g_nth_height = 7;
SET @g_nth_row = 3;

SET @g_query =
‘WITH CTE_RESULT AS
(
SELECT ct_salary, DENSE_RANK() OVER
(
ORDER BY ct_salary DESC
) AS DENSERANK_RESULT FROM T_EMPLOYEE
)
SELECT TOP ‘+ CONVERT(VARCHAR, @g_nth_row) + ‘ ct_salary AS SALARY FROM CTE_RESULT WHERE CTE_RESULT.DENSERANK_RESULT =’ + CONVERT(VARCHAR, @g_nth_highest);

EXEC(@g_query)
— END
Conclusion
I hope you guys get the scenario and this might be helpful to you. Enjoy!

History
Saturday, December 22th, 2015: Initial post

A simple example of MS SQL Server Pad String

Introduction
An easy way to Pad String using MS SQL Server.

Using the code
A sample code snippet is given below:
//
CREATE FUNCTION [dbo].[FUN_pad_string]

(
@p_seq VARCHAR(16),
@p_pad_with CHAR(1),
@p_pad_length INT
)

RETURNS VARCHAR(16) AS

BEGIN

DECLARE @g_current_seq varchar(16)

SELECT @g_current_seq = ISNULL(REPLICATE(@p_pad_with, @p_pad_length – LEN(ISNULL(@p_seq ,0))), ”) + @p_seq

RETURN @g_current_seq

END
//
Conclusion

I hope you guys get the scenario and this might be helpful to you, enjoy.

History
Saturday, December 19, 2015: Initial post.

How to split MS SQL Server table row data

Introduction

An easy way to split MS SQL Server table row data.

Using the code

A sample code snippet is given below:

-- SELECT @tableColumnID, Data  FROM dbo.[FUN_split_row_data](@p_row_string_data,',')
CREATE FUNCTION [dbo].[FUN_split_row_data]
(
	@p_row_data NVARCHAR(2000),
	@p_split_on NVARCHAR(256)
)  
RETURNS @g_ret_value TABLE 
(
	id INT idENTITY(1,1),
	data nvarchar(100)
) 
AS  
BEGIN 
	DECLARE @g_counter INT
	SET @g_counter = 1

	WHILE (CHARINDEX(@p_split_on,@p_row_data)>0)
	BEGIN
		INSERT INTO @g_ret_value (data)
		SELECT data = LTRIM(RTRIM(SUBSTRING(@p_row_data,1,CHARINDEX(@p_split_on,@p_row_data)-1)))

		SET @p_row_data = SUBSTRING(@p_row_data,CHARINDEX(@p_split_on,@p_row_data)+1,LEN(@p_row_data))
		SET @g_counter = @g_counter + 1
	END
	
	INSERT INTO @g_ret_value (data)
	SELECT data = LTRIM(RTRIM(@p_row_data))

	RETURN

END

Input

SELECT  1234 AS id, data  FROM dbo.[FUN_split_row_data]('Hello,world,!!!,you are, most, welcome, at, codeproject.com',',')

Output

id    data

——————
314    Hello
314    world
314    !!!
314    you are
314    most
314    welcome
314    at
314    codeproject.com

Conclusion

I hope you guys get the scenario and this might be helpful to you, enjoy.

History

  • Saturday, December 19, 2015: Initial post.

Bio-metrics devices integration with school/collage/university students attend system (Whatever the platform names are) to provide students activities over SMS.

We will use 1. HTTP over TCP/IP Or 2. SMPP over TCP/IP for the messaging platform.
Few features of that services:
1. Send an acknowledgement SMS to the parents that your kids is in school and safe now.
2. Send an acknowledgement SMS to the parents that your kids is going to somewhere else (could be home, tuitions, etc.) from school.
3. Also a lot’s of services could be configured for example, result, exam, announcement etc.
4. Reporting.
5. Etc.
I know already few companies working on that in our country and this type of system are using UK, USA, CA, AU, JP etc., over 8/10 years ago . I developed a prototype of that system… What I am thinking now is to change the design from product base to service base.
What do you think guys?

Thanks,
Md. Marufuzzaman