Archive

Posts Tagged ‘Tsql challenge’

T-SQL : Gaps and Islands Problem – 2

There are lot of ways to find the gaps in a sequential numbers, and some of them are listed below. I have posted some 3 methods to achieve the expected. The goal is to find the gaps between the sequential number. The query should return the Gap start point and Gap end point. Please check these queries and any suggestions/comments are always welcomed.

declare @table table (num int)
insert into @table
select 1 union all
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 11 union all
select 12 union all
select 13 union all
select 15 union all
select 17 union all
select 28

--Query 1
select GapStart,GapEnd from
( select m.num+ 1 as GapStart
,(select min(num) - 1 from @table as x where x.num > m.num) as GapEnd
from @table as m left outer join @table as r on m.num = r.num- 1 where r.num is null
) as x where GapEnd is not null 

--Query 2 
select a.num1 GapStart,b.num2 GapEnd from 
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)a,
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)b
where a.ID+1 = b.ID
and (a.num1<b.num2 or a.num1 = b.num2) 

--Query 3 
select GapStart, GapEnd from 
(select m.num,m.num+1 as GapStart,ROW_NUMBER() over (order by m.num+1) as ser
from @table m
left join (select num-1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num+1<(select MAX(num) from @table ))x
join 
(select m.num,m.num-1 as GapEnd,ROW_NUMBER() over (order by m.num-1) as ser from @table m
left join (select num+1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num-1>(select MIN(num) from @table ))y
on x.ser=y.ser

Output:
Capture

Happy Querying 😉

T-SQL : An Interesting Question – 3

The requirement is as follows,

Filter out records that has three or more words in it

(or)

Filter out records that has two or more spaces in it

There are several ways to perform this and i would like to show some of them,

Declare @table Table(words varchar(50))
insert into @table
select 'Get the query' union
select 'Two words' union
select 'Three' union
select 'Explain' union
select 'See the output' union
select 'Execute the Query' union
select 'Find the Names' union
select 'single' union
select 'see the output below'

select words from @table
where words like '% % %'

select words from @table
where words  LIKE '%| %| %' ESCAPE '|'

select words from @table
where words LIKE '_% _% _%'

select words from @table
where DATALENGTH(words) - DATALENGTH(REPLACE(words,' ',''))>=2

select words from @table
where CHARINDEX(' ',words, CHARINDEX(' ',words, 1) + 1) >  0

select words from @table
where PATINDEX('% % %',words)> 0

Happy Querying 😉

T-SQL : An Interesting Question – 2

Another interesting question that I have read recently, which involves some string operations.

Question:
The whole string given is a alphanumeric (for ex: AP1AA). The requirement is to Stuff ‘0’ before the single digit number (0-9) which is enclosed by Alphabets (AP1AA)).

For Ex:

If you are given a string like AP2JJ then it should be stuffed with a zero before the single digit numerical (2 in this case) and the output should be AP02JJ. When the numeric is of 2 digits, no actions are needed.

Query:

DECLARE @table TABLE (ExString varchar (12))
INSERT INTO @table
SELECT 'AP10AA' UNION
SELECT 'AA10B' UNION
SELECT 'AAA123' UNION
SELECT 'AA1AB1' UNION
SELECT 'AA2B1' UNION
SELECT 'AA03A' UNION
SELECT 'AA1234' UNION
SELECT '2BB' UNION
SELECT 'A9B' UNION
SELECT 'A0B' UNION
SELECT 'A10B'

SELECT
ExString AS ExString_Before ,
CASE WHEN ExString LIKE '%[a-z][0-9][a-z]%'
THEN STUFF (ExString, patindex('%[0-9]%' ,ExString), 0,'0' )
ELSE ExString
END ExString_After
FROM @table

I have shared my query to perform the above stuff operations. Any suggestions and new ideas are welcomed

Happy Querying 😉

TSQL : Parent-Child view to Flattened view using cursors

I recently came across of this requirement from one of my friends, he wanted the following table data to be viewed in a different format. Take a look at the following table

 

Members
Parent
Bikes
Wheel
Steering
Tires
Parent
Clothing
Shirts
Pants
Trousers
Parent
Fruits
Apple
Mango
Orange

 

The above shown table has only one column named members, the records available resembles a parent child notation. With clear ‘report-style’ formatting, we can get enough information from the same table as shown below,

 

Members
Parent
Bikes
  Wheel
  Steering
  Tires
Parent
Clothing
  Shirts
  Pants
  Trousers
Parent
Fruits
  Apple
  Mango
  Orange

Now this table, more or less looks like expand collapse style Parent –  child information,

Requirement:

1. There are records denoting which record is a parent. Here in this example we have record as ‘parent’, it denotes the immediate result after this record as a parent. For example (Bikes, Clothing and Fruits)

 

2. The record which follows ‘parent’ value is the parent like said before, and the successive records till the next ‘parent’ value are its Childs. For example, if ‘Bikes’ is parent then ‘Wheel’, ‘Steering’,’Tires’ are its child values

 

3. This process should be continued till the end of the table and the result should be in the following format,

Parent Child
Bikes Wheel
Bikes Steering
Bikes Tires
Clothing Shirts
Clothing Pants
Clothing Trousers
Fruits Apple
Fruits Mango
Fruits Orange

this is the flattened view of the same parent-child table.

Initially I was trying to bring this result set using SQL query but i ended with cursors. The following snippet is the cursor which brings the required result (flattened view).

Query:

DECLARE @table TABLE
(
members NVARCHAR (max)
)

INSERT INTO @table
SELECT 'Parent'
UNION ALL
SELECT 'Bikes'
UNION ALL
SELECT 'Wheel'
UNION ALL
SELECT 'Steering'
UNION ALL
SELECT 'Tires'
UNION ALL
SELECT 'Parent'
UNION ALL
SELECT 'Clothing'
UNION ALL
SELECT 'Shirts'
UNION ALL
SELECT 'Pants'
UNION ALL
SELECT 'Trousers'
UNION ALL
SELECT 'Parent'
UNION ALL
SELECT 'Fruits'
UNION ALL
SELECT 'Apple'
UNION ALL
SELECT 'Mango'
UNION ALL
SELECT 'Orange'

DECLARE @temp1 VARCHAR (max)
DECLARE @temp2 VARCHAR (max)
DECLARE @Check VARCHAR (max)
DECLARE @Required TABLE
(
parent VARCHAR (max),
child  VARCHAR(max)
)
DECLARE pc_cursor CURSOR FOR
SELECT * FROM   @table

OPEN pc_cursor

FETCH FROM pc_cursor INTO @check

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Check = 'Parent'
BEGIN
FETCH next FROM pc_cursor INTO @temp1
FETCH next FROM pc_cursor INTO @temp2
WHILE ( @temp2 <> 'Parent' )
AND @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Required
(parent,
child)
SELECT @temp1,
@temp2
FETCH next FROM pc_cursor INTO @temp2
IF @temp2 = 'Parent'
BEGIN
FETCH next FROM pc_cursor INTO @temp1
FETCH next FROM pc_cursor INTO @temp2
END
END
END
FETCH next FROM pc_cursor INTO @check
END

SELECT *
FROM   @Required

Output:

 

parent child
Bikes Wheel
Bikes Steering
Bikes Tires
Clothing Shirts
Clothing Pants
Clothing Trousers
Fruits Apple
Fruits Mango
Fruits Orange

I am again going to try the same using tsql query without cursors, if you want to share your queries for bringing this result set, please do post your query as comments.

Happy Querying 😉

How to convert YYYYMMDDHHMMSS to Date/Time/Datetime in sql server

In this thread, i would like to show some SQL queries that converts YYYYMMDDHHMMSS style formatted date-string into Sql Server datetime format

Method 1:
The simplest method to achieve this would be using a stuff() function like it is shown below,

Declare @Table  Table (DateValue varchar(50))
insert into @Table
select 20130428213555 union
select 20130424215856 union
select 20130425205557 union
select 20130427073558 union
select 20120429082559 union
select 20110527091558 union
select 20120523060558 union
select 20110522054558

select
convert(datetime,stuff(stuff(stuff(datevalue, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')) ConvertedDate
from @Table

Output:

1

I have also tried the same conversion without using stuff() function, which are as follows

Method 2:

The query following will convert the “YYYYMMDDHHMMSS” to date and time seperately

--To convert YYYYMMDDHHMMSS formatted string to Date/time
Declare @Table  Table (DateValue varchar(50))
insert into @Table
select 20130428213555 union
select 20130424215856 union
select 20130425205557 union
select 20130427073558 union
select 20120429082559 union
select 20110527091558 union
select 20120523060558 union
select 20110522054558

select
Right(DateValue,6)/10000 Hours,
Right(DateValue,4)/100 Minutes,
Right(DateValue,2) Seconds,
Convert(time,Dateadd(SECOND,
		Right(DateValue,2)/1,
		Dateadd(MINUTE,
				Right(DateValue,4)/100,
				Dateadd(hour,
						Right(DateValue,6)/10000,
						'1900-01-01')))) TimeConverted,
convert(date,LEFT(datevalue,8)) as DateConverted
from @Table

Output:

2

Sql server will throw some conversion error on casting/converting directly from “YYYYMMDDHHMMSS” to “YY-MM-DD HH:MM:SS” To overcome this error i have used an empty date(‘1900-01-01’) which is a default date from sql server and added date and time. See the following query which performs the same,

--To convert the YYYYMMDDHHMMSS formatted string to datetime
Declare @Table  Table (DateValue varchar(50))
insert into @Table
select 20130428213555 union
select 20130424215856 union
select 20130425205557 union
select 20130427073558 union
select 20120429082559 union
select 20110527091558 union
select 20120523060558 union
select 20110522054558

select
Convert(time,Dateadd(SECOND,
		Right(DateValue,2)/1,
		Dateadd(MINUTE,
				Right(DateValue,4)/100,
				Dateadd(hour,
						Right(DateValue,6)/10000,
						'1900-01-01')))) +
convert(datetime,LEFT(datevalue,8)) as DateConverted
from @Table

Output:

3

See below query to understand about the usage of 1900-01-01, a default date in sql server considered as blank on datetime fields


SELECT CONVERT(datetime, '', 112) DefaultDate1,
CONVERT(datetime, 0, 112)DefaultDate2

Output:

4

Happy Querying 😉

%d bloggers like this: