SQL Puzzle | The Count Puzzle

The challenge here to write the smallest query. We have to summarized data based on the building Id. Please check the sample input and expected output.

Sample Input

tbl_RealEstate

tbl_login

tbl_history

Expected Output

Script

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

--

CREATE TABLE tbl_login
(
	ID int,
	BldID int,
	Name varchar(30)
)
GO

insert into tbl_login
values
(1, 1, 'Name1'),
(2, 1, 'Name2'),
(3, 2, 'Name3'),
(4, 3, 'Name4'),
(5, 2, 'Name5'),
(6, 4, 'Name6'),
(7, 5, 'Name7'),
(8, 6, 'Name8'),
(9, 1, 'Name9'),
(10, 1, 'Name10'),
(11, 2, 'Name11'),
(12, 3, 'Name12');
GO

CREATE TABLE tbl_history
(
	ID int,
	BldID int,
	Username varchar(30)
)
GO


insert into tbl_history
values
(1, 1, 'Name1'),
(2, 1, 'Name1'),
(3, 1, 'Name9'),
(4, 2, 'Name3'),
(5, 3, 'Name4'),
(6, 4, 'Name6'),
(7, 4, 'Name6'),
(8, 4, 'Name6'),
(9, 5, 'Name7'),
(10, 6, 'Name8'),
(11, 8, 'Name9'),
(12, 8, 'Name10'),
(13, 9, 'Name11'),
(14, 9, 'Name12');
GO

CREATE TABLE tbl_RealEstate
(
	 BldID int,
	RealEstateName varchar(30)
)
GO

insert into tbl_RealEstate values
(1, 'Building 1'),
(2, 'Building 2'),
(3, 'Building 3'),
(4, 'Building 4'),
(5, 'Building 5'),
(6, 'Building 6'),
(7, 'Building 7'),
(8, 'Building 8'),
(9, 'Building 9');



--

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 tbl_RealEstate as r 
OUTER APPLY
(
	SELECT COUNT(*) as LoginCount FROM tbl_login as l 
	WHERE BldID = r.BldID
)t1
OUTER APPLY
(
	SELECT COUNT(*) as HistoryCount FROM tbl_history as h
	WHERE BldID = r.BldID
)t2

--

Output

--

/*------------------------
SELECT * FROM tbl_RealEstate as r 
OUTER APPLY
(
	SELECT COUNT(*) as LoginCount FROM tbl_login as l 
	WHERE BldID = r.BldID
)t1
OUTER APPLY
(
	SELECT COUNT(*) as HistoryCount FROM tbl_history as h
	WHERE BldID = r.BldID
)t2
------------------------*/
BldID       RealEstateName                 LoginCount  HistoryCount
----------- ------------------------------ ----------- ------------
1           Building 1                     4           3
2           Building 2                     3           1
3           Building 3                     2           1
4           Building 4                     1           3
5           Building 5                     1           1
6           Building 6                     1           1
7           Building 7                     0           0
8           Building 8                     0           2
9           Building 9                     0           2

(9 row(s) affected)

--

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 Weird String Puzzle

In this puzzle you have to read a string value for example – 11Ab220111bbb5555ccc4444.
We have to get data like below-

If any character is repeating after the first occurance we have to print that number.
Example
1 -> for this we need to print 111 since 1 is repeating.
b -> for tihs we need to print bbb since b is repeating.
for other just print the character as they are not repeating. We do not have to print the duplicate characters.

Sample Input

Expected Output

Script

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

--

DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444';
SELECT @WeirdValue WeirdValue

--

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

--

DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444';

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE AS
(
	SELECT *, SUBSTRING(@WeirdValue,Number,1) Nums FROM Series 
	WHERE Number <= LEN(@WeirdValue)
)
,CTE1 AS
(
	SELECT * ,  CASE WHEN Nums = lag(Nums) 
				OVER(ORDER BY Number) THEN 0 ELSE 1 END cols 
	FROM 
	CTE
)
,CTE2 AS
(
	SELECT * , SUM(cols) OVER (PARTITION BY Nums ORDER BY Number) grouper FROM CTE1	
)
,CTE3 AS
(
	SELECT nums FROM CTE2 c WHERE grouper = 2
	UNION ALL
	SELECT DISTINCT nums FROM CTE2 r WHERE grouper = 1
	AND NOT EXISTS ( SELECT NULL FROM CTE2 c1 WHERE r.Nums = c1.Nums AND grouper = 2 )
)
,CTE4 AS
(
	SELECT DISTINCT nums nums1 , STUFF 
					((
					SELECT ', ' + CAST(nums AS VARCHAR)
					FROM CTE3 a
					WHERE ( a.nums = b.nums )
					FOR XML PATH('')
					) ,1,2,'') 
					AS nums
	FROM CTE3 b
)
SELECT nums1 Number , nums PrintAs FROM CTE4

--

Output

--

/*------------------------
DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444';

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE AS
(
	SELECT *, SUBSTRING(@WeirdValue,Number,1) Nums FROM Series 
	WHERE Number <= LEN(@WeirdValue)
)
,CTE1 AS
(
	SELECT * ,  CASE WHEN Nums = lag(Nums) 
				OVER(ORDER BY Number) THEN 0 ELSE 1 END cols 
	FROM 
	CTE
)
,CTE2 AS
(
	SELECT * , SUM(cols) OVER (PARTITION BY Nums ORDER BY Number) grouper FROM CTE1	
)
,CTE3 AS
(
	SELECT nums FROM CTE2 c WHERE grouper = 2
	UNION ALL
	SELECT DISTINCT nums FROM CTE2 r WHERE grouper = 1
	AND NOT EXISTS ( SELECT NULL FROM CTE2 c1 WHERE r.Nums = c1.Nums AND grouper = 2 )
)
,CTE4 AS
(
	SELECT DISTINCT nums nums1 , STUFF 
					((
					SELECT ', ' + CAST(nums AS VARCHAR)
					FROM CTE3 a
					WHERE ( a.nums = b.nums )
					FOR XML PATH('')
					) ,1,2,'') 
					AS nums
	FROM CTE3 b
)
SELECT nums1 Number , nums PrintAs FROM CTE4
------------------------*/
Number PrintAs
------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0      0
1      1, 1, 1
2      2
4      4
5      5
A      A
b      b, b, b
c      c

(8 row(s) affected)



--

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 Length of longest value from columns in all tables from a database

In this puzzle we have to find Length of longest value from columns in all tables from a database.

Sample Input

TABLE : testMaxLength1

TABLE : testMaxLength2

Expected Output

Script

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

--

/* CREATE a new DATABASE called of your choice and execute below tables*/

CREATE TABLE testMaxLength1
(
	 ID INT
	,Vals VARCHAR(100)
)
GO


INSERT INTO testMaxLength1 VALUES
(4, 'Hello World'),
(42, 'Hello World1')
GO


CREATE TABLE testMaxLength2
(
	 ID INT
	,Vals VARCHAR(100)
	,Flats DECIMAL(30,8)
)
GO


INSERT INTO testMaxLength2 VALUES
(4, 'Pawan World Hello' , 12323.9877 ),
(2, 'Pawa' , 23.1 )
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

--

IF OBJECT_ID('tempdb..#TempTableNames') IS NOT NULL
DROP TABLE #TempTableNames

IF OBJECT_ID('tempdb..#FinalData') IS NOT NULL
DROP TABLE #FinalData

CREATE TABLE #FinalData ( TableName VARCHAR(MAX), ColumnName VARCHAR(MAX), DataType VARCHAR(MAX), SetLength BIGINT, MaxLength BIGINT)
DECLARE @SQL VARCHAR(MAX)=''
DECLARE @TableName AS VARCHAR(250) = ''
SELECT name INTO #TempTableNames FROM sys.tables WHERE type = 'U'

WHILE EXISTS ( SELECT TOP 1 1 FROM #TempTableNames )
BEGIN

	SELECT TOP 1 @TableName = name FROM #TempTableNames

	SELECT @SQL = @SQL + 'SELECT ' + '''' + @TableName + '''' + ' as TableName' + ',' + QUOTENAME(sc.name, '''') + ' AS ColumnName, ' + QUOTENAME(t.name, '''') + ' AS DataType, ' +
	QUOTENAME(sc.max_length, '''') + ' AS SetLength, MAX(LEN(' + QUOTENAME(sc.name) + ')) AS MaxLength FROM '+@TableName+ char(10) +' UNION '
	FROM sys.columns sc
	INNER JOIN sys.types t on t.system_type_id = sc.system_type_id and t.name != 'sysname'
	WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)
	SET @SQL = LEFT(@SQL, LEN(@SQL)-6)

    INSERT INTO #FinalData	
	EXEC(@SQL)

	DELETE FROM #TempTableNames WHERE name = @TableName

END

SELECT DISTINCT * FROM #FinalData

--

Output

--

/*------------------------
OUTPUT
------------------------*/
TableName                     ColumnName   DataType      SetLength            MaxLength
----------------------------- ------------ ------------- -------------------- --------------------
testMaxLength1                ID           int           4                    2
testMaxLength1                Vals         varchar       100                  12
testMaxLength2                Flats        decimal       17                   14
testMaxLength2                ID           int           4                    1
testMaxLength2                Vals         varchar       100                  17

(5 row(s) affected)

--

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 Complex Hierarchy Puzzle [All Positions below – Line Manager]

In this puzzle we have to write a query that will give us the list of all Sr Line Staff or lower with their corresponding Line Manager. For details please refer the input data and the expected output.

Sample Input

Employees Table

Positions table

PositionTypes Table

Expected Output

Script

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

--

CREATE TABLE tblPositionTypes    
(
     PositionTypeID INT 
     ,PositionType VARCHAR(35)
)
GO

INSERT INTO tblPositionTypes VALUES
(1,'Line Staff'),
(2,'Team Lead'),
(3,'Sr Line Staff'),
(4,'Line Manager'),
(5,'Sr Line Manager')
GO

CREATE TABLE tblPositions        
(
     PositionID     INT 
     ,Position VARCHAR(100)
     ,PositionTypeID INT
)
GO

INSERT INTO tblPositions VALUES
(1,'Operational Line Staff',1),
(2,'Operational Team Lead',2),
(3,'Operational Sr Line Staff',3),
(4,'Operational Line Manager',4),
(5,'Research Line Staff',  1),
(6,'Research Line Manager', 4),
(7,'Sales Staff',     1),
(8,'Sales Team Lead', 2),
(9,'Sales Manager',   4),
(10, 'Branch Sr Manager',  5)
GO

CREATE TABLE tblEmployees  
(
     EmployeeID     INT
     ,Name VARCHAR(10)
     ,ParentID  INT
     ,PositionID INT 
)
GO

INSERT INTO tblEmployees VALUES       
(1   ,'Joe'     ,4   ,1         ),
(2   ,'Sue'     ,5   ,5         ),
(3   ,'John'    ,6   ,7         ),
(4   ,'Amy'     ,7   ,2         ),
(5   ,'Luis'    ,10  ,6         ),
(6   ,'Harry'   ,8   ,8   ),
(7   ,'Pete'    ,9   ,3         ),
(8   ,'Rhonda', 10,  9    ),
(9   ,'Maria'   ,10  ,4   ),
(10  ,'Jack'    ,NULL,     10   ),
(11  ,'Kate'    ,12  ,5         ),
(12  ,'Aaron'   ,10  ,6   ),
(13  ,'Julie'   ,14  ,7   ),
(14  ,'Sarah'   ,15  ,8   ),
(15  ,'Bob'     ,10  ,9         )
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 e.EmployeeID,e.Name,p.Position,e.ParentID FROM tblEmployees e
     INNER JOIN tblPositions    p ON e.PositionID = p.PositionID    
     INNER JOIN tblPositionTypes pt ON pt.PositionTypeID = p.PositionTypeID
     WHERE pt.PositionTypeID IN (1,2,3)    
)
,CTE1 AS
(
     SELECT e.EmployeeID,e.Position,e.Name,e.Name Name1,e.ParentID , 0 distance FROM CTE e
     UNION ALL
     SELECT * FROM 
     (
           SELECT e.EmployeeID,e1.Position,e1.name,e.name name1,e.ParentID, e1.distance + 1 distance
           FROM CTE1 e1 INNER JOIN tblEmployees e ON e.EmployeeID = e1.ParentID 
     )k   
)
SELECT R.EmployeeID,R.Name,R.Position ,Q.Name1 [Manager Name] FROM 
(
     SELECT * FROM CTE1 e 
     WHERE DISTANCE = 0
)r
CROSS APPLY
(
     SELECT * FROM CTE1 t
     WHERE t.Name = r.Name AND DISTANCE = ( SELECT MAX(DISTANCE) - 1 FROM CTE1 t WHERE t.Name = r.Name )
)q
ORDER BY r.EmployeeID 


--

Output

--

/*------------------------
;WITH CTE AS
(
     SELECT e.EmployeeID,e.Name,p.Position,e.ParentID FROM tblEmployees e
     INNER JOIN tblPositions    p ON e.PositionID = p.PositionID    
     INNER JOIN tblPositionTypes pt ON pt.PositionTypeID = p.PositionTypeID
     WHERE pt.PositionTypeID IN (1,2,3)    
)
,CTE1 AS
(
     SELECT e.EmployeeID,e.Position,e.Name,e.Name Name1,e.ParentID , 0 distance FROM CTE e
     UNION ALL
     SELECT * FROM 
     (
           SELECT e.EmployeeID,e1.Position,e1.name,e.name name1,e.ParentID, e1.distance + 1 distance
           FROM CTE1 e1 INNER JOIN tblEmployees e ON e.EmployeeID = e1.ParentID 
     )k   
)
SELECT R.EmployeeID,R.Name,R.Position ,Q.Name1 [Manager Name] FROM 
(
     SELECT * FROM CTE1 e 
     WHERE DISTANCE = 0
)r
CROSS APPLY
(
     SELECT * FROM CTE1 t
     WHERE t.Name = r.Name AND DISTANCE = ( SELECT MAX(DISTANCE) - 1 FROM CTE1 t WHERE t.Name = r.Name )
)q
ORDER BY r.EmployeeID 
------------------------*/

EmployeeID  Name       Position                     Manager Name
----------- ---------- ---------------------------- ------------
1           Joe        Operational Line Staff       Maria
2           Sue        Research Line Staff          Luis
3           John       Sales Staff                  Rhonda
4           Amy        Operational Team Lead        Maria
6           Harry      Sales Team Lead              Rhonda
7           Pete       Operational Sr Line Staff    Maria
11          Kate       Research Line Staff          Aaron
13          Julie      Sales Staff                  Bob
14          Sarah      Sales Team Lead              Bob

(9 row(s) affected)

--

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