How to

Find missing sequence number. Get the numbers where sequence is missing.

2018-12-03T23:10:00.001-08:00

Most of the time the question was asked while having Database interview that we have number sequence present in a table but some numbers are missing. Can you write the query to detect where number is missing from sequence and if possible then also can get the missing number?

OK, it’s not a rocket science so let’s begin. Please give me a paper and a pen to write query Sir.

I am using a Temporary variable table here.

DECLARE @Temp TABLE

(

Id INT IDENTITY(1,1),

Num INT

)

INSERT INTO @Temp (Num) VALUES (1) ,(2), (4), (6), (7), (9)

SELECT * FROM @Temp

SELECT a.Id , a.Num, b.Num, (a.Num - b.Num) as Diff, (b.Num + 1) as MissingNum

FROM @Temp a

LEFT JOIN @Temp b ON b.Id = (a.Id - 1)

WHERE (a.Num - b.Num) > 1

You see it’s just a simple SELECT statement but tricky.

OK, yes it will produce my result but we do not have any Id field in our database, we only have one column Num. Can you write a query for same?

OK, sir give me few seconds to think…..

OK so there are two possible ways I will use. With CTE or without CTE.

Without CTE:

DECLARE @Temp TABLE

(

Num INT

)

INSERT INTO @Temp (Num) VALUES (1) ,(2), (4), (6), (7), (9)

DECLARE @TempFinal TABLE

(

Id INT IDENTITY(1,1),

Num INT

)

INSERT INTO @TempFinal

SELECT Num FROM @Temp

SELECT * FROM @TempFinal

SELECT a.Id , a.Num, b.Num, (a.Num - b.Num) as Diff, (b.Num + 1) as MissingNum

FROM @TempFinal a

LEFT JOIN @TempFinal b ON b.Id = (a.Id - 1)

WHERE (a.Num - b.Num) > 1

Sir, I have used one temporary variable table (@TempFinal) where I have added Id column that will generate the id for each row and rest query is again same as previous. Only I have to change the table name from @Temp to @TempFinal.

With CTE:

Now using CTE I do not have to create extra temporary table.

DECLARE @Temp TABLE

(

Num INT

)

INSERT INTO @Temp (Num) VALUES (1) ,(2), (4), (6), (7), (9)

;WITH CTE

(

SELECT ROW_NUMBER() OVER(ORDER BY Num) AS Id, Num FROM @Temp

)

SELECT a.Id , a.Num, b.Num, (a.Num - b.Num) AS Diff, (b.Num + 1) as MissingNum

FROM CTE a

JOIN CTE b ON b.Id = (a.Id - 1)

WHERE (a.Num - b.Num) > 1

OK, that’s what I am looking for.

Find difference pattern of the given sequence.

2016-07-15T06:48:00.003-07:00

This one is from the interview question I have asked. A sequence was given to me and asked to write a SQL query to find the difference pattern of the sequence and complete the sequence by adding missing numbers.
Give sequence: '1,5,13,21,25,29,41'
Below is the query to find the answer:

SET NOCOUNT ON

DECLARE @temp AS TABLE (Id INT IDENTITY, Nums INT, NextNum INT DEFAULT 0,

Pattern AS (NextNum - Nums)

)

INSERT @temp (Nums)

SELECT * FROM dbo.Split('1,5,13,21,25,29,41', ',')

-- Here assume that Split function you have added already OR please follow the link
-- http://anishrana.blogspot.in/2012/03/split-string-in-sql-server-using-xml.html
-- and create one.

UPDATE a

SET NextNum = b.Nums

FROM @temp a

JOIN @temp b ON a.Id = (b.Id - 1)

DECLARE @pattern INT = NULL,

@firstNum INT = NULL,

@lastNum INT = NULL

SELECT TOP 1 @pattern = Pattern FROM @temp a

GROUP BY Pattern

ORDER BY COUNT(1) DESC

SELECT @firstNum = MIN(Nums), @lastNum = MAX(Nums) FROM @temp

--SELECT @pattern, @firstNum, @lastNum

WHILE (@lastNum >= @firstNum)

BEGIN

PRINT @firstNum

SET @firstNum += @pattern

END

PRINT 'Sequence generated with difference pattern of: ' + CONVERT(NVARCHAR(10), @pattern)

SET NOCOUNT OFF

Copy and run the script in SQL.

Below is the result:
--------------------------------------------------------------------

Sequence generated with difference pattern of: 4

Print Fibonacci Series in SQL

2016-06-03T02:52:00.001-07:00

Some times you were asked to write a program to print Fibonacci series in interview. So below is the SQL query that will print the Fibonacci series. Copy , Paste and run.

DECLARE @A INT = 1, @B INT = 0 , @C INT = 0

DECLARE @Limit INT = 50

WHILE (@C <= @LIMIT)

BEGIN

PRINT @C

SET @C = @A + @B

SET @A = @B

SET @B = @C

END

Output:

Change Text Color with Javascript at more that one part in single page.

2015-03-28T00:08:00.002-07:00

Below code is the modified version of the topic name Change Text Color with Javascript as one of the reader demands. Copy and paste the code in notepad and save it as any name with .html extension and run in browser.

Logic behind to use the same feature in different parts of the page is by using class instead of id. Id is unique and cannot be used more than once but class can be used more than once to group the feature. The previous code uses ID and this one use Class on div.

<html>

<head>

<title>Text Color Change</title>

elmColor = 0;

function changeColor(elmColor) {

for (i = 0; i < document.getElementsByClassName('colorChange').length; i++) {

switch (elmColor) {

case 0:

document.getElementsByClassName('colorChange')[i].style.color = '#000000';

elmColor++;

break;

case 1:

document.getElementsByClassName('colorChange')[i].style.color = '#ff0000';

elmColor++;

break;

case 2:

document.getElementsByClassName('colorChange')[i].style.color = '#00ff00';

elmColor++;

break;

case 3:

document.getElementsByClassName('colorChange')[i].style.color = '#0000ff';

elmColor++;

break;

case 4:

document.getElementsByClassName('colorChange')[i].style.color = '#0688ad';

elmColor++;

break;

case 5:

document.getElementsByClassName('colorChange')[i].style.color = '#2f6202';

elmColor++;

break;

case 6:

document.getElementsByClassName('colorChange')[i].style.color = '#c26a14';

elmColor++;

break;

case 7:

document.getElementsByClassName('colorChange')[i].style.color = '#cf013b';

elmColor = 0;

break;

default:

break;

}

setTimeout(function () { changeColor(elmColor); }, 700);

}

window.onload = function () { changeColor(elmColor); };

</script>

</head>

<body>

Hello....

<br>

I am changing my text color with the help of Javascript.....

<br>

Thankyou Javascript.......... :)

</div>

<br />

Another div with text changing effect...

Hello....

<br>

I am changing my text color with the help of Javascript.....

<br>

Thankyou Javascript.......... :)

</div>

<br />

Third div with text changing effect...

Hello....

<br>

I am changing my text color with the help of Javascript.....

<br>

Thankyou Javascript.......... :)

</div>

</body>

</html>

Change Css Class of li in ul onclick - Menu Linked Pages

2014-05-16T19:02:00.001-07:00

Below code is posted on this blog on demand. This topic is modification of the topic

Change Css Class of li in ul onclick

Previous code sample is single page code. Now I have three different pages that are linked to three menu items. When Page One is clicked then menu Page One is highlighted and menupageone.html is navigated, when Page Two is clicked then menu Page Two is highlighted and menupagetwo.html and soon.

menupageone.html – code , copy paste the below code and save with name menupageone.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>List Item Menu - Query String</title>

{

font-family: Calibri;

font-size: 14px;

}

{

list-style-type: none;

float: left;

border-bottom: solid 2px Red;

padding-right: 40px;

}

ul li

{

float: left;

width: auto;

cursor: pointer;

}

ul li a

{

text-decoration: none;

color: #000000;

padding: 8px;

display: block;

}

</style>

function checklist()

{

var list = document.getElementById("menu").getElementsByTagName('li');

for (var i = 0; i < list.length; i++)

{

list[i].style.background = '#ffffff';

}

window.onload = function ()

{

checklist();

document.getElementById("one").style.background = '#ff0000';

};

</script>

</head>

<body>

<li id="three"><a href="menupagethree.html">Page Three</a></li>

</ul>

</div>

<br />

</body>

</html>

menupagetwo.html – code , copy paste the below code and save with name menupagetwo.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>List Item Menu - Query String</title>

{

font-family: Calibri;

font-size: 14px;

}

{

list-style-type: none;

float: left;

border-bottom: solid 2px Red;

padding-right: 40px;

}

ul li

{

float: left;

width: auto;

cursor: pointer;

}

ul li a

{

text-decoration: none;

color: #000000;

padding: 8px;

display: block;

}

</style>

function checklist()

{

var list = document.getElementById("menu").getElementsByTagName('li');

for (var i = 0; i < list.length; i++)

{

list[i].style.background = '#ffffff';

}

window.onload = function ()

{

checklist();

document.getElementById("two").style.background = '#ff0000';

};

</script>

</head>

<body>

<li id="three"><a href="menupagethree.html">Page Three</a></li>

</ul>

</div>

<br />

</body>

</html>

menupagethree.html – code , copy paste the below code and save with name menupagethree.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>List Item Menu - Query String</title>

{

font-family: Calibri;

font-size: 14px;

}

{

list-style-type: none;

float: left;

border-bottom: solid 2px Red;

padding-right: 40px;

}

ul li

{

float: left;

width: auto;

cursor: pointer;

}

ul li a

{

text-decoration: none;

color: #000000;

padding: 8px;

display: block;

}

</style>

function checklist()

{

var list = document.getElementById("menu").getElementsByTagName('li');

for (var i = 0; i < list.length; i++)

{

list[i].style.background = '#ffffff';

}

window.onload = function ()

{

checklist();

document.getElementById("three").style.background = '#ff0000';

};

</script>

</head>

<body>

<li id="three"><a href="menupagethree.html">Page Three</a></li>

</ul>

</div>

<br />

</body>

</html>

Code for all pages is same, only change is in the script

window.onload = function ()

{

checklist();

document.getElementById("three").style.background = '#ff0000';

};

Change Css Class of li in ul onclick with Query String

2014-05-02T08:00:00.003-07:00

Copy and paste and run the code.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>List Item Menu - Query String</title>

{

list-style-type:none;

float:left;

border-bottom: solid 2px Red;

padding-right:40px;

}

ul li

{

float:left;

width:auto;

cursor:pointer;

}

ul li a

{

text-decoration:none;

color:#000000;

padding:8px;

display:block;

}

</style>

function checklist(ctrl)

{

var list = document.getElementById("menu").getElementsByTagName('li');

for(i=0; i<list.length; i++)

{

list[i].style.background = '#ffffff';

}

window.onload = function()

{

var qstr =location.search.substring(1).split('=')[1];

document.getElementById(qstr).style.background = '#ff0000';

}

</script>

</head>

<body>

</ul>

<br />

</body>

</html>

Change Css Class of li in ul onclick with Anchor Tag

2014-05-01T09:02:00.002-07:00

Below code is some modification as on demand of one of the reader for topic
Change Css Class of li in ul onclick

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>List Item</title>
<style type="text/css">
ul
{
list-style-type:none;
float:left;
border-bottom: solid 2px Red;
padding-right:40px;
}
ul li
{
float:left;
width:auto;
cursor:pointer;
}
ul li a
{
text-decoration:none;
color:#000000;
padding:8px;
display:block;
}
</style>

<script type="text/javascript" language="javascript">

function checklist(ctrl)
{
var list = document.getElementById("menu").getElementsByTagName('li');
for(i=0; i<list.length; i++)
{
list[i].style.background = '#ffffff';
var hidePage = list[i].getElementsByTagName('a')[0].href.split('#')[1];
document.getElementById('#' + hidePage).style.display = 'none';
}

ctrl.style.background = '#ff0000';
var gotoPage = ctrl.getElementsByTagName('a')[0].href.split('#')[1];
document.getElementById('#' + gotoPage).style.display = 'block';
}

</script>
</head>

<body>
<ul id="menu">
<li onclick="checklist(this);"><a href="#pageOne"> A Item</a></li>
<li onclick="checklist(this);"><a href="#pageTwo">B Item</a></li>
<li onclick="checklist(this);"><a href="#pageThree">C Item</a></li>
</ul>
<div style="clear:both"></div>
<br />
<br />
<div id="#pageOne" style="display:none">
Page One content
</div>
<div id="#pageTwo" style="display:none">
Page Two content
</div>
<div id="#pageThree" style="display:none">
Page Three content
</div>
</body>
</html>

Select duplicate records without count or group by

2014-03-19T05:29:00.001-07:00

Below is the simple query of selecting duplicate records without using count or group by.

/* Create temp variable table */

DECLARE @tempRecords AS TABLE (RecordId INT IDENTITY(1,1), FName VARCHAR(10), LName VARCHAR(10))

/* Insert Data to temp tabel */

INSERT INTO @tempRecords(FName, LName) VALUES('A', 'B'), ('A','B'), ('C','D'),('C','E'),('A','E'),('C','D'),('A','B')

/* Select All records */

SELECT * FROM @tempRecords

/* Select Duplicate records only with all columns */

SELECT DISTINCT a.* FROM @tempRecords a INNER JOIN @tempRecords b

ON a.FName = b.FName

AND a.LName = b.LName

AND a.RecordId <> b.RecordId

ORDER BY a.FName, a.LName

Here we select duplicate records by self joining.

Distinct Record with MAX Value

2014-02-25T08:44:00.004-08:00

Some time you require to get record from parent and child table on basis of maximum of on some column base. So below is the code with two tables one parent and other is child. Here we need to get the single record having max start and end date.

Copy and paste the code in SQL Query window and execute

/* Parent Table */

DECLARE @Property AS TABLE (Id INT IDENTITY(1, 1) PRIMARY KEY, Refs VARCHAR(100))

/* Child Table */

DECLARE @PropertyInfo AS TABLE (

Id INT IDENTITY(1, 1) PRIMARY KEY,

PId INT, /* Asume it as Foreign Key to Id column of Property Table as its not possible to create refrence is not allowed */

StartDate DATE,

EndDate DATE

)

INSERT INTO @Property VALUES ('Abc'), ('Def'), ('Ghi'), ('Jkl')

INSERT INTO @PropertyInfo(PId, StartDate, EndDate)

VALUES (1, '2013/10/10', '2013/10/15'), (1, '2013/11/10', '2013/11/15'), (1, '2013/12/10', '2013/12/15'),

(2, '2013/10/10', '2013/10/15'), (2, '2013/12/10', '2013/12/16'),

(3, '2013/12/10', '2013/12/15'), (3, '2013/12/15', '2014/01/16'),

(4, '2013/11/10', '2013/11/15'), (4, '2014/01/15', '2014/03/16')

/* Select all records */

SELECT a.*, b.StartDate, b.EndDate FROM @Property a, @PropertyInfo b

WHERE a.Id = b.PId

/* Select with max date only, single record for each PId */

SELECT b.Id, b.Refs, a.StartDate, a.EndDate FROM

(

SELECT ROW_NUMBER() OVER (PARTITION BY(PId) ORDER BY(EndDate) DESC) R, PId, StartDate, EndDate

FROM @PropertyInfo

) a, @Property b

WHERE a.R = 1

AND a.Pid = b.Id

Paging in Database

2014-01-20T23:17:00.001-08:00

Paging in records from database. Some time you need to get number or recording at a time from large number of records. Just as .NET has gridview control with paging utility. So some time we need to create own logic for paging in records at database leve. So below is the code to accomplish that task.

* Below code is simple to understand..

* */

DECLARE @employees AS TABLE (EmpId INT IDENTITY(1,1), EmpName VARCHAR(20), Salary INT)

INSERT INTO @employees(EmpName, Salary)

VALUES ('Sohan', 2000), ('Rohan', 4000), ('Mohan', 3000), ('Ram', 2000), ('Seema', 3000),('Micky', 2500), ('Rinky', 4000),

('Ronin', 5000), ('Robin', 2000), ('Sam', 4000), ('Sohil', 3000), ('Raven', 2000),

('Gery', 5000), ('Bob', 2000), ('Denis', 4000), ('Steph', 3000), ('Jhon', 2000),

('Kaleb', 3000), ('Rahul', 2000), ('Reena', 3000),('Kamal', 2500), ('Vimal', 4000)

DECLARE @records INT = @@ROWCOUNT, @pager INT = 10

/* Below code gives you total number of pages based on pager size here @pager = 10 */

SELECT CASE WHEN (@records/@pager = @records%@pager) THEN (@records/@pager) + 1

ELSE (@records/10) END Pages

DECLARE @index INT = 1

DECLARE @start INT , @end INT

IF @index = 1 /* For first time pass index as 1 */

BEGIN

SELECT @start = 1, @end = @pager

END

ELSE

BEGIN /* This works for all the time accept first time i.e. index <> 1 */

SET @end = (@index * @pager)

SET @start = (@end - 9)

END

SELECT * FROM

(

SELECT *, ROW_NUMBER() OVER (ORDER BY EmpId ASC) AS Pager FROM @employees

) a

WHERE a.Pager BETWEEN @start AND @end

Nth Salary by DENSE_RANK()

2013-12-19T01:58:00.001-08:00

Find nth highest salary by DENSE_RANK()

DECLARE @employees AS TABLE (EmpId INT IDENTITY(1,1), EmpName VARCHAR(20), Salary INT)

DECLARE @nthSalary INT = 3

INSERT INTO @employees(EmpName, Salary)

VALUES ('Sohan', 2000), ('Rohan', 4000), ('Mohan', 3000), ('Ram', 2000), ('Seema', 3000)

/* List all with salary rank */

SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM @employees

/* List those having required highest salary */

SELECT * FROM

(

SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM @employees

WHERE a.SalaryRank = @nthSalary

/* Highest required salary */

SELECT DISTINCT Salary FROM

(

SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM @employees

WHERE a.SalaryRank = @nthSalary

Find special characters from the column of the table

2013-06-12T04:56:00.001-07:00

Below is the simple SQL query to find those records from the table that contain special characters.

Simply create a table, insert some data into it containing special characters or copy and paste the below code in to query window and execute.

DECLARE @data AS TABLE (RecordId INT IDENTITY, TextData VARCHAR(100))

INSERT INTO @data(TextData)

VALUES ('hello abc'), ('hello $hyam'), ('testing % char'), ('hello ram'),

('hello.'), ('testing # now'), ('testing @ now and ! also'), ('why lefting ^me and & me too')

SELECT *

FROM @data

WHERE TextData LIKE '%[^a-zA-Z0-9,.()* ]%'

Get table names used in Stored Procedure

2013-04-10T02:52:00.000-07:00

Get all tables used in Stored Procedure.

Below is the simple query to get name of all the tables used in the stored procedure.

DECLARE @spName VARCHAR(200) = 'storedprocedure_name'

DECLARE @tbl AS TABLE (AutoId INT IDENTITY(1,1), SpText TEXT)

INSERT INTO @tbl

exec sp_helptext @spName

SELECT DISTINCT b.TABLE_NAME FROM @tbl a, INFORMATION_SCHEMA.TABLES b

WHERE a.SpText LIKE '%' + b.TABLE_NAME + '%'

Separate camel case String

2013-02-18T02:09:00.003-08:00

Below is the query to separate camel case string and removing first lower part from the string on condition IF @skipFirstLowerPart = 0 means skip first lower part from the string and IF @skipFirstLowerPart = 1 then do not skip first lower part from the string

DECLARE @str VARCHAR(100) = 'txtFirstName'

DECLARE @skipFirstLowerPart INT = 0 -- 0 yes 1 no

DECLARE @len INT = LEN(@str), @ptr INT = 1, @chk INT = 0

DECLARE @newstr VARCHAR(100) = ''

WHILE @len > 0

BEGIN

DECLARE @char CHAR(1) = SUBSTRING(@str, @ptr, @ptr+1)

IF @skipFirstLowerPart = 0

BEGIN

IF (CONVERT(VARBINARY(128), @char) = CONVERT(VARBINARY(128), LOWER(@char))) AND (@chk = 0)

BEGIN

SET @char = ''

END

IF CONVERT(VARBINARY(128), @char) <> CONVERT(VARBINARY(128), LOWER(@char))

BEGIN

SELECT @newstr += ' ', @chk = 1

END

SET @newstr += @char

SELECT @ptr += 1, @len -= 1

END

SELECT LTRIM(RTRIM(@newstr))

RESULT :

First Name

Extract Numbers OR Characters From String

2013-02-14T02:32:00.001-08:00

Below is the query to extract numbers or integers from an alphanumeric string.

PATINDEX () and STUFF() and a WHILE loop is used to get the result.

DECLARE @str VARCHAR(100) = 'hello123testing456'

DECLARE @hasPat INT = PATINDEX('%[a-z]%', @str)

WHILE @hasPat > 0

BEGIN

SET @str = STUFF(@str, @hasPat, 1, '')

SET @hasPat = PATINDEX('%[a-z]%', @str)

END

SELECT @str AS NumOnly

Result:

NumOnly

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

123456

The above code can be modified to extract characters from the alphanumeric string by just changing the pattern inside PATINDEX() function

DECLARE @str VARCHAR(100) = 'hello123testing456'

DECLARE @hasPat INT = PATINDEX('%[0-9]%', @str)

WHILE @hasPat > 0

BEGIN

SET @str = STUFF(@str, @hasPat, 1, '')

SET @hasPat = PATINDEX('%[0-9]%', @str)

END

SELECT @str AS CharsOnly

Result:

CharsOnly

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

hellotesting

Search those records that contain % character in there value

2013-01-10T04:33:00.001-08:00

The code below is to search those records where first name contains % character. This question also important for interview

/* Search those records where first name contains % */

CREATE TABLE #userdata (RecordId INT IDENTITY, FirstName VARCHAR(50), LastName VARCHAR(50))

INSERT INTO #userdata (FirstName, LastName) VALUES ('Dave%', 'Hanks'), ('Simon', 'Deja'), ('Peter%', 'Hans')

SELECT * FROM #userdata

/* Query 1 */

SELECT * FROM #userdata WHERE CHARINDEX('%', FirstName, 0) > 0

/* Query 2 */

SELECT * FROM #userdata WHERE FirstName LIKE '%[%]%'

DROP TABLE #userdata

Sequencing records by year and there Running Total for records in the same year

2013-01-10T04:12:00.006-08:00

Below is the code to generate arranging records in the same year and the consequent sum.

Query is simple and understandable, no group or any aggregate function is used. Just simple insert and update statements can do the task.

/* Creating temporary table */

CREATE TABLE #entries (RecordId INT IDENTITY(1,1), FinancialYear INT, Amount INT, Total INT DEFAULT 0)

/* Inserting dummy data to table */

INSERT INTO #entries (FinancialYear, Amount) VALUES (2011, 5),(2011, 10),(2011, 15),(2012, 10), (2012, 20), (2012, 30),

(2012, 50),(2012, 50), (2013, 100), (2013, 150), (2013, 200)

/* Get all data of entry table to temp table with two aditional columns*/

SELECT *, CAST(0 AS INT) MatchYear, CAST(0 AS INT) EntryId, CAST(0 AS INT) OverAllTotal INTO #temp FROM #entries

DECLARE @id INT = 0, @total INT = 0, @nextAmt INT = 0, @firstYear INT, @overAllTotal INT = 0

UPDATE #temp

SET #temp.MatchYear = a.FinancialYear

FROM #entries a

WHERE #temp.RecordId = a.RecordId + 1

SELECT TOP 1 @firstYear = FinancialYear FROM #temp

UPDATE #temp SET MatchYear = @firstYear WHERE MatchYear = 0

UPDATE #temp

SET Total = CASE WHEN FinancialYear = MatchYear THEN 0 ELSE Amount END

UPDATE #temp

SET EntryId = @id,

Total = @total,

OverAllTotal = @overAllTotal,

@id = @id + CASE WHEN FinancialYear = MatchYear THEN 1 ELSE -(@id-1) END,

@total = CASE WHEN FinancialYear = MatchYear

THEN @total + Amount ELSE Amount END,

@overAllTotal += Amount

SELECT RecordId, FinancialYear, MatchYear, EntryId, Amount, Total, OverAllTotal FROM #temp

DROP TABLE #entries, #temp

Below is the screen shot :-

Insert Data In To Two Tables In Single Insert Statement

2013-01-08T02:54:00.002-08:00

Below is the simple code to insert data in to two tables in single Insert statement

CREATE TABLE #registration (RegistrationId INT IDENTITY(1,1), FirstName NVARCHAR(50), LastName NVARCHAR(50))

CREATE TABLE #logindetails (LoginId INT IDENTITY(100,1), RegistrationId INT, UserName NVARCHAR(50), Pwd NVARCHAR(50))

Here RegistrationId is related in both tables.

Inserting two records in to both tables

INSERT INTO #registration(FirstName, LastName)

OUTPUT INSERTED.RegistrationId, 'admin', 'admin@123' INTO #logindetails

SELECT 'Jawala', 'Parsad'

INSERT INTO #registration(FirstName, LastName)

OUTPUT INSERTED.RegistrationId, 'mylogin', '123@testone' INTO #logindetails

SELECT 'Happy', 'Singh'

SELECT * FROM #registration

SELECT * FROM #logindetails

DROP TABLE #registration, #logindetails

After selecting data from both tables you will fin that RegistrationId is same in both the tables. This solves the problem of selecting RegistrationId from #registration table and the inserting into #logindetails for maintaining relationship among both tables.

Copy the above code and execute..!

Screenshot :-

Sequencing records by year

2012-10-30T00:49:00.001-07:00

Below is the simple query to detect how many time record is entered in database within one year. Sequence number is generated for the records for the same year from 1,2... and for next year again from 1,2.... and soon.

/* Creating table for entries */

CREATE TABLE #entries (RecordId INT IDENTITY(1,1), FinancialYear INT)

INSERT INTO #entries (FinancialYear) VALUES (2011),(2011),(2011),(2012), (2012), (2012), (2012),(2012), (2013), (2013), (2013)

/*Get all data of entry table to temp table with two aditional columns*/

SELECT *, CAST(0 AS INT) MatchYear, CAST(0 AS INT) EntryId INTO #temp FROM #entries

DECLARE @id INT = 1

UPDATE #temp

SET #temp.MatchYear = a.FinancialYear

FROM #entries a

WHERE #temp.RecordId = a.RecordId + 1

UPDATE #temp

SET EntryId = @id,

@id = @id + CASE WHEN FinancialYear = MatchYear THEN 1 ELSE -(@id-1) END

SELECT RecordId, FinancialYear, EntryId FROM #temp

DROP TABLE #entries, #temp

OUTPUT:-

RecordId FinancialYear EntryId

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

1 2011 1

2 2011 2

3 2011 3

4 2012 1

5 2012 2

6 2012 3

7 2012 4

8 2012 5

9 2013 1

10 2013 2

11 2013 3

Nth MAX Salary from Table

2012-10-08T02:49:00.002-07:00

Here below is the simplest logic to find nth MAX salary from Salary Table

DECLARE @Salary TABLE (EmployeeId INT IDENTITY(1,1), Salary INT) /* Temporary Salary Table using Table variable*/

DECLARE @n INT = 3 /* nth MAX Salary, here 3 means 3rd MAX salary */

INSERT INTO @Salary(Salary) VALUES (1000), (2000), (3000), (4000), (2000), (1000), (3000)

Method 1:-

SELECT Salary

FROM (

SELECT ROW_NUMBER() OVER(ORDER BY(Salary) DESC) Sno,

Salary

FROM @Salary

GROUP BY

Salary

) a

WHERE a.Sno = @n

Method 2:-

SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP (@n) Salary FROM @Salary ORDER BY Salary DESC) a

ORDER BY Salary ASC

Count Number Of Spaces in a String

2012-05-08T00:10:00.001-07:00

Hello readers.

Below is a simple query to count number of spaces from a string.

DECLARE @string VARCHAR(200) = 'hello how are you'

SELECT (LEN(@string) - LEN(REPLACE(@string, ' ', ''))) Occurence

Get Current IE Version

2012-04-11T04:13:00.003-07:00

Hello readers..

IE(Internet Explorer) is the most hectic browser when implementing javascript. Some times we need to write browser specific javascript code, so we need to detect its Version first because different versions of IE run javascript differently. So below is the small piece of code to find current version of IE. The code is for IE only not for others browsers.

<html>

<head><title>IE Version Test</title></head>

<body>

var IEVer = navigator.appVersion.split(';')[1].split(' ')[2];

document.write(IEVer);

</script>

</body>

</html>

Split String in SQL SERVER Using XML

2012-03-22T06:06:00.000-07:00

Split delimited string using XML.

Hello readers....

Below is a simple example to split string by using XML. Create a function that does the task. This function accepts two parameters. First is the string with delimiters e.g.(a,b,c,d – here , are the delimiters) and second parameter is separator or delimiter (in example , is delimiter). You can use any character for delimiter instead of a comma. I am using coma as a delimiter in the current example.

First we convert the string in to XML by using CAST and then by querying over the generated XML we generate the columns that hold the split output of the string.

Below is the code.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION fnSplit

(

@string VARCHAR(MAX),

@seprator VARCHAR(1)

)

RETURNS @chunks TABLE (Chunks VARCHAR(200))

BEGIN

SET @string = LTRIM(RTRIM(REPLACE(@string, @seprator, ' ')))

DECLARE @xml XML = (SELECT CAST('<x>'+ REPLACE(@string, ' ', '</x><x>') + '</x>' AS XML))

INSERT INTO @chunks

SELECT x.value('.','VARCHAR(100)') AS a FROM @xml.nodes('x') f(x) WHERE LEN(LTRIM(RTRIM(x.value('.','VARCHAR(100)')))) > 0

RETURN

END

Execute the function and close the query window.

Now open new query window and execute the function like below

SELECT * FROM dbo.fnSplit('aa|bb|cc|dd|ee|ff', '|') -- here | is delimiter

SELECT * FROM dbo.fnSplit('aa,bb,cc,dd,ee,ff', ',') -- here , is delimiter

SELECT * FROM dbo.fnSplit('aa:bb:cc:dd:ee:ff', ':') -- here : is delimiter

SELECT * FROM dbo.fnSplit('aa;bb;cc;dd;ee;ff', ';') -- here ; is delimiter

Calculate Running Total

2012-03-15T03:39:00.000-07:00

Hello readers..!

Some time you need to calculate running total of the item. Many people do extra calculation and long methods to accomplish that. Below is the simple logic to calculate running total.

First create a table that holds item with there price. I m using temporary table.

CREATE TABLE #amount

(

RecordId INT IDENTITY(1,1),

ItemName VARCHAR(20),

Cost INT

)

Insert some data into the table.

INSERT INTO #amount VALUES ('Pen', 10),('Pencil', 5), ('Rubber', 2), ('Scale', 2), ('Paper', 10)

Now creating another temporary table (#TotalAmount) from the above table and with extra field named RunningTotal that holds the Running Total of the items.

SELECT *, 0 AS RuningTotal INTO #TotalAmount FROM #amount

Declaring a variable that calculates the total of item.

DECLARE @amountSum INT = 0;

Update table like below

UPDATE #TotalAmount

SET RuningTotal = @amountSum, @amountSum = @amountSum + Cost

Now select both the tables to view changes.

SELECT * FROM #amount

SELECT * FROM #TotalAmount

DROP TABLE #amount, #TotalAmount

Remove Extra Commas From Beginning or Ending of a String

2012-03-15T02:27:00.000-07:00

A simple and very short code to replace extra commas from the beginning or ending of a string. No need to explain the code.

DECLARE @str VARCHAR(50) = ',a,n,i,s,h,'

SELECT @str

SET @str = REPLACE(@str, ',', ' ')

SET @str = LTRIM(RTRIM(@str))

SELECT REPLACE(@str,' ', ',')