Advertisements

SQL Puzzle | Find max except negative values

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Find Max except negative values

In this puzzle the requirement is to get the max value for each Id. Only exception is that if the maximum value in that group is 0 and negative values are present in that group then get sum of the negative values.

Please check out the sample input values and sample expected output below.

Sample Input

Id Vals
1 -1
1 0
2 1
2 0
2 4
3 -1
4 1
4 -1
5 -1
5 -12
6 -1
6 -2
6 0

Expected Output

Id Vals
1 -1
2 4
3 -1
4 1
5 -1
6 -3

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE WhatMaxy
(
	 Id INT
	,Vals INT
)
GO

INSERT INTO WhatMaxy VALUES
(1,-1),
(1,0),
(2,1),
(2,0),
(2,4),
(3,-1)
GO
INSERT INTO WhatMaxy VALUES(4,1),(4,-1)
INSERT INTO WhatMaxy VALUES(5,-1),(5,-12)
INSERT INTO WhatMaxy VALUES(6,-1),(6,-2),(6,0)
		 
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--
 
SELECT  k.Id, CASE WHEN k1.Vals < 0 AND k.Vals = 0 THEN k1.Vals ELSE k.Vals End Vals FROM 
(
	SELECT MAX(Vals) Vals, Id FROM WhatMaxy
	GROUP BY Id
)k
INNER JOIN 
(
	SELECT SUM(Vals) Vals, Id FROM WhatMaxy
	GROUP BY Id
)k1
ON k.Id = k1.Id
GO

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements

SQL Puzzle | Remove Words from a string(column) present in a table

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Remove Words from a string in a table

In this puzzle the requirement is you have to remove words from a string which are of length 7. The data is present in a column of a table.
Please check out the sample input values and sample expected output below.

Sample Input

Id Vals
1 Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# pneumonoultramicroscopicsilicovolcanoconiosis
2 Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# Mayank

Expected Output

Id Vals
1 Hello World 123 @#$!@#
2 Hello World 123 @#$!@# Mayank

Script

Use below script to create table and insert sample data into it.

--


CREATE TABLE RemoveWordsFromString
(
	 Id INT
	,Vals NVARCHAR(MAX)
)
GO

INSERT INTO RemoveWordsFromString VALUES
(1,'Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# pneumonoultramicroscopicsilicovolcanoconiosis'),
(2,'Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# Mayank')
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--
 
;WITH CTE AS
(
	SELECT * FROM
	(
		SELECT Id, CAST('<A>'+ REPLACE(Vals,' ','</A><A>')+ '</A>' AS XML) Vals FROM RemoveWordsFromString 
	)r
	CROSS APPLY
	(
		SELECT t.value('.', 'NVARCHAR(MAX)') Value 
		FROM r.Vals.nodes('/A') AS x(t)
	)u
	WHERE LEN(Value) <= 7
)	
SELECT DISTINCT c.Id,STUFF 
                ((
                SELECT ' ' + CAST(a.Value AS VARCHAR(MAX))
                FROM CTE a
                WHERE ( a.Id = c.Id )
                FOR XML PATH('')
                ) ,1,1,'') 
                AS Vals
FROM CTE c

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Find Grand Parent, Parent and Child Puzzle ( Parent/Child Relationships )

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Find Grand Parent, Parent and Child Puzzle ( Parent/Child relationship tree between Range )

In this puzzle the requirement is to generate the range and level column. E.g. 1-100 is grandfather RANGE is 0 and level is 1, 2-50 is the 1st son so range column value will be 144 and level will be 1 and so on..

Please check out the sample input values and sample expected output below.

Sample Input

id start_value end_vaule range level
144 1 100 NULL NULL
145 2 50 NULL NULL
146 8 25 NULL NULL
147 51 70 NULL NULL
148 57 60 NULL NULL
164 200 250 NULL NULL
172 201 225 NULL NULL
174 201 213 NULL NULL
188 310 350 NULL NULL
192 325 350 NULL NULL
194 333 333 NULL NULL

Expected Output

id start_value end_vaule range level
144 1 100 0 1
145 2 50 144 2
146 8 25 145 3
147 51 70 144 2
148 57 60 147 3
164 200 250 0 1
172 201 225 164 2
174 201 213 172 3
188 310 350 0 1
194 333 333 188 2

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Testlevels
(
	 id INT 
	,start_value  INT 
	,end_vaule  INT
	,[range] INT
	,[level]  INT
)
GO

INSERT INTO Testlevels VALUES
(144,      1     , 100    ,  NULL   ,  NULL	   ),
(145,      2     , 50     , NULL    , NULL	   ),
(146,      8     , 25     , NULL    , NULL	   ),
(147,      51    ,  70    ,  NULL   ,  NULL	   ),
(148,      57    ,  60    ,  NULL   ,  NULL	   ),
(164,      200   ,   250  ,    NULL ,    NULL   ),
(172,      201   ,   225  ,    NULL ,    NULL   ),
(174,      201   ,   213  ,    NULL ,    NULL   ),
(188,      310   ,   350  ,    NULL ,    NULL   ),
(192,      325   ,   350  ,    NULL ,    NULL   ),
(194,      333   ,   333  ,    NULL ,    NULL   )
GO
			 
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--
 
;WITH CTE AS
(	
	SELECT * 
	FROM Testlevels a
	EXCEPT
	SELECT DISTINCT k.* FROM Testlevels a
	CROSS APPLY
	(
		SELECT * FROM Testlevels b
		WHERE 
			b.start_value >= a.start_value AND b.start_value <= a.end_vaule
		AND b.end_vaule >= a.start_value and b.end_vaule <= a.end_vaule
		AND b.id <> a.id	
	)k
)
,CTE3 AS 
(
	SELECT a.id , a.start_value , a.end_vaule , 0 [range] , 1 [level] FROM CTE a
	UNION ALL  
	SELECT t.id , t.start_value , t.end_vaule , c.id , c.[level] + 1 [level]
	FROM CTE3 c INNER JOIN Testlevels t ON ( c.start_value <= t.start_value AND c.end_vaule > t.end_vaule )		
)
SELECT id , start_value , end_vaule , [range], [level] FROM
(
   SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY range desc,level desc) rnk FROM CTE3 
)X 
Where rnk = 1
ORDER BY id

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | The Not Between Puzzle

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The NOT Between Puzzle

In this puzzle you have to find out the records in which start value and end value is not between any other start value and end value.
E.g. Id = 144, range 1 – 100 is not between any other value, same goes for id 164 and id = 188

Please check out the sample input values and sample expected output below.

Sample Input

id start_value end_vaule range level
144 1 100 NULL NULL
145 2 50 NULL NULL
146 8 25 NULL NULL
147 51 70 NULL NULL
148 57 60 NULL NULL
164 200 250 NULL NULL
172 201 225 NULL NULL
174 201 213 NULL NULL
188 310 350 NULL NULL
192 325 350 NULL NULL
194 333 333 NULL NULL

Expected Output

id start_value end_vaule range level
144 1 100 NULL NULL
164 200 250 NULL NULL
188 310 350 NULL NULL

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Testlevels
(
	 id INT 
	,start_value  INT 
	,end_vaule  INT
	,[range] INT
	,[level]  INT
)
GO

INSERT INTO Testlevels VALUES
(144,      1     , 100    ,  NULL   ,  NULL	   ),
(145,      2     , 50     , NULL    , NULL	   ),
(146,      8     , 25     , NULL    , NULL	   ),
(147,      51    ,  70    ,  NULL   ,  NULL	   ),
(148,      57    ,  60    ,  NULL   ,  NULL	   ),
(164,      200   ,   250  ,    NULL ,    NULL   ),
(172,      201   ,   225  ,    NULL ,    NULL   ),
(174,      201   ,   213  ,    NULL ,    NULL   ),
(188,      310   ,   350  ,    NULL ,    NULL   ),
(192,      325   ,   350  ,    NULL ,    NULL   ),
(194,      333   ,   333  ,    NULL ,    NULL   )
GO
			 
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--
 
SELECT * 
FROM Testlevels a
EXCEPT
SELECT DISTINCT k.* FROM Testlevels a
CROSS APPLY
(
SELECT * FROM Testlevels b
WHERE 
	b.start_value >= a.start_value AND b.start_value <= a.end_vaule
AND b.end_vaule >= a.start_value and b.end_vaule <= a.end_vaule
AND b.id <> a.id	
)k

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | The Ntext Column Problem

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Ntext Column Problem

In this puzzle you have to group by data based on id and Data column. Please check out the sample input values and sample expected output below.

Sample Input

Id Data Vals
1 a 12
1 a 10
1 a 12
1 b 100

Expected Output

Id Data Vals
1 a 34
1 b 100

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE ManageData
(
     Id INT
	,Data Ntext
	,Vals INT
)
GO
 
INSERT INTO ManageData VALUES
(1,'a',12),
(1,'a',10),
(1,'a',12),
(1,'b',100)
GO
 			 
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--
 
SELECT Id,CAST(Data AS VARCHAR(MAX)) Data,SUM(Vals)Vals FROM ManageData
GROUP BY Id,CAST(Data AS VARCHAR(MAX))

--

** Note 1- ** – If you do the grouping without casting the column you will receive below error.

/*————————
SELECT Id,Data,SUM(Vals)Vals FROM ManageData
GROUP BY Id,Data
————————*/
Msg 306, Level 16, State 2, Line 2
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

** Note 2- ** – You cannot use text, ntext, and image data types in group by, order and not even replace.

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com