Daily Archives: March 31, 2015

T-SQL Query | [ The Missing Numbers Puzzle ]

31 Tuesday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

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


T-SQL Query | [ The Missing Numbers Puzzle ]

Puzzle Statement

  1. We have a single input table called MissingNumbers.
  2. Here we have given some numbers in unordered manner.
  3. We have to pick the minimum and maximum number from the table and fill numbers if missed from the sequence.
  4. Please check out the sample input and expected output for details.

Sample Input

ID
5
1
2
8

Expected output

ID
1
2
3
4
5
6
7
8

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”.
  • Your solution should be generic in nature.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE MissingNumbers
(
ID INT
)
GO

INSERT INTO MissingNumbers(ID) VALUES
(5),
(1),
(2),
(8)

UPDATE – 11-Apr-2015 – Solution 1 & 2 


--

--SOLUTION 1
SELECT DISTINCT Number FROM master..spt_values WHERE number <= ( SELECT MAX(ID) ID FROM MissingNumbers m1 ) AND number > 0

--SOLUTION 2
;WITH CTE AS
(
      SELECT TOP 1 m.ID FROM MissingNumbers m   ORDER BY ID
      UNION ALL
      SELECT ID + 1 FROM CTE c WHERE EXISTS (SELECT m.ID FROM MissingNumbers m WHERE m.ID > c.ID )
)
SELECT ID Number FROM CTE

--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ The Manage and Employee Puzzle – 2 ]

31 Tuesday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 2 Comments

Tags

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


T-SQL Query | [ The Manage and Employee Puzzle – 2 ]

The puzzle is simple. Here we have to find out employees from  the input table with their manager and dept. Please check out the sample input and expected output for details.

Sample Input

Id Name Dept Manager
101 Ranjan SW
102 Deeksha SW 101
103 sham SW 101
104 Junaid SW 101
105 Vinay HQ
106 akhilesh HQ 105
107 Pranav HQ 105

Expected output

Name DeptNm MgrNm
akhilesh HQ Vinay
Pranav
Deeksha SW Ranjan
sham
Junaid

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

Script

Use the below script to generate the source table and fill them up with the sample data.


----

CREATE TABLE Complex
(Id INT, Name VARCHAR(50), Dept VARCHAR(50), Manager VARCHAR(50))

INSERT INTO Complex VALUES (101,'Ranjan','SW','')
INSERT INTO Complex VALUES (102,'Deeksha','SW','101')
INSERT INTO Complex VALUES (103,'sham','SW','101')
INSERT INTO Complex VALUES (104,'Junaid','SW','101')
INSERT INTO Complex VALUES (105,'Vinay','HQ','')
INSERT INTO Complex VALUES (106,'akhilesh','HQ','105')
INSERT INTO Complex VALUES (107,'Pranav','HQ','105')

---

UPDATE – 20-Apr-2015 – Solution 1


--


SELECT p.Name,CASE WHEN rnk > 1 THEN '' ELSE DeptName END DeptNm, CASE WHEN rnk > 1 THEN '' ELSE MgrName END MgrNm
FROM 
(
      SELECT c.Dept DeptName ,c.Name MgrName , c2.Name , c2.Id  , ROW_NUMBER() OVER (PARTITION BY c.Dept ORDER BY c.Dept ) rnk FROM Complex c
      INNER JOIN Complex c2 ON c.Id = c2.Manager
) P

--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ The Sequencer Puzzle ]

31 Tuesday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

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


T-SQL Query | [ The Sequencer Puzzle ]

The puzzle is simple. We have solve this puzzle using numbers with jumps, e.g. for 3 rows with AAA the numbers would be 11,12,13; BBB-21,22; CCC-31,32; DDD-41,42,43,44;. Please check out the sample input and expected output for details.

Sample Input

Orders OrderItem
AAA 1
AAA 2
AAA 3
BBB 1
BBB 2
CCC 1
CCC 2
DDD 1
DDD 2
DDD 3
DDD 4

Expected output

Orders OrderItems
AAA 11,12,13
AAA 11,12,13
AAA 11,12,13
BBB 21,22
BBB 21,22
CCC 31,32
CCC 31,32
DDD 41,42,43,44
DDD 41,42,43,44
DDD 41,42,43,44
DDD 41,42,43,44

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

Script

Use the below script to generate the source table and fill them up with the sample data.


---


CREATE TABLE Sequencer
(
Orders VARCHAR(3)
,OrderItem INT
)
GO

INSERT INTO Sequencer(Orders,OrderItem) VALUES
('AAA' ,1),
('AAA' ,2),
('AAA' ,3),
('BBB' ,1),
('BBB' ,2),
('CCC' ,1),
('CCC' ,2),
('DDD' ,1),
('DDD' ,2),
('DDD' ,3),
('DDD' ,4)

---

UPDATE – 20-Apr-2015 – Solution 1


--

;WITH CTE AS
(
      SELECT r.Orders , CAST(rnk  * 10 + OrderItem AS VARCHAR(3)) rnk FROM 
      (
            SELECT * , ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) )  rnk  FROM 
            (
                  SELECT DISTINCT ORDERS FROM Sequencer
            ) d 
      ) r
      INNER JOIN Sequencer a on r.Orders = a.Orders
)
SELECT Orders , STUFF ((SELECT ',' + rnk FROM CTE w WHERE w.Orders = y.Orders FOR XML PATH('')) , 1,1,'') OrderItems
FROM CTE y

--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Matching data between rows and columns ]

31 Tuesday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 4 Comments

Tags

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


T-SQL Query | [ Matching data between rows and columns ]

Original puzzle link – http://beyondrelational.com/blogs/tc/archive/2009/10/19/TSQL-Challenge-15-matching-data-between-rows-and-columns.aspx

The puzzle is simple. In this puzzle we were required to find the first and last IDs for consecutive rows with same values of Send and Ack states. Please check out the sample input and expected output for details.

Sample Input

Rower

Row
100
104
101
99
77
20
10

Cols

Col
1
2
3
4
5
6
7
8
9

Expected output

ROW 1 2 3 4 5 6 7 8 9
10 X X X
20 X X X X
77 X X
99 X X X
100 X X X X
101 X
104 X X X X

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

Script

Use the below script to generate the source table and fill them up with the sample data.


-------


CREATE TABLE Cols (Col INT);

INSERT INTO Cols VALUES (1);
INSERT INTO Cols VALUES (2);
INSERT INTO Cols VALUES (3);
INSERT INTO Cols VALUES (4);
INSERT INTO Cols VALUES (5);
INSERT INTO Cols VALUES (6);
INSERT INTO Cols VALUES (7);
INSERT INTO Cols VALUES (8);
INSERT INTO Cols VALUES (9);

CREATE TABLE Rower (Row INT);

INSERT INTO Rower VALUES (100);
INSERT INTO Rower VALUES (104);
INSERT INTO Rower VALUES (101);
INSERT INTO Rower VALUES (99);
INSERT INTO Rower VALUES (77);
INSERT INTO Rower VALUES (20);
INSERT INTO Rower VALUES (10);

----

UPDATE – 20-Apr-2015 – Solution 1


--

DECLARE @colsPivot AS VARCHAR(MAX) ='',@colsPivotIN AS VARCHAR(MAX) =''
SELECT @colsPivot = STUFF((SELECT ',' + QUOTENAME(Col) FROM cols FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SELECT @colsPivotIN = STUFF((SELECT ',' +  ' CASE WHEN Row % ' + QUOTENAME(Col) + ' = 0 THEN ''X''  ELSE '''' END '  + QUOTENAME(Col) 
FROM cols FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
DECLARE @ExStr AS VARCHAR(MAX) = 
							'  ;WITH CTE AS ( select *,ROW_NUMBER() over (order by (select null )) rnk from cols ) 
							,CTE1 AS (
							SELECT * FROM CTE
							PIVOT 
							( max(rnk) FOR col in (' + @colsPivot + ') ) pvt
							) 
							SELECT ROW , ' + @colsPivotIN +  ' from Rower r,CTE1 ORDER BY r.Row'
EXEC (@ExStr)
--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ The Send & Ack Puzzle ]

31 Tuesday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 2 Comments

Tags

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


T-SQL Query | [ The Send & Ack Puzzle ]

Original puzzle link – http://beyondrelational.com/blogs/tc/archive/2009/06/04/tsql-challenge-9.aspx

The puzzle is simple. In this puzzle we were required to find the first and last IDs for consecutive rows with same values of Send and Ack states. Please check out the sample input and expected output for details.

Sample Input

ID CreationDate Content SendState AckState
1 24-11-2013 Msg #1 0 0
2 24-11-2013 Msg #2 0 0
3 24-11-2013 Msg #3 1 1
4 24-11-2013 Msg #4 1 1
5 24-11-2013 Msg #5 1 1
6 25-11-2013 Msg #6 1 0
7 25-11-2013 Msg #7 1 0
8 25-11-2013 Msg #8 1 0
9 25-11-2013 Msg #9 1 0
10 25-11-2013 Msg #10 1 1

Expected output

ID ID SendState AckState
1 2 0 0
3 5 1 1
6 9 1 0
10 10 1 1

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE Grouper
(
ID INT IDENTITY(1,1),
CreationDate DATETIME,
Content NVARCHAR(10),
SendState BIT,
AckState BIT
)

INSERT INTO Grouper (CreationDate,Content,SendState,AckState)
SELECT GETDATE()-1.0,'Msg #1',0,0 UNION
SELECT GETDATE()-0.9,'Msg #2',0,0 UNION
SELECT GETDATE()-0.8,'Msg #3',1,1 UNION
SELECT GETDATE()-0.7,'Msg #4',1,1 UNION
SELECT GETDATE()-0.6,'Msg #5',1,1 UNION
SELECT GETDATE()-0.5,'Msg #6',1,0 UNION
SELECT GETDATE()-0.4,'Msg #7',1,0 UNION
SELECT GETDATE()-0.3,'Msg #8',1,0 UNION
SELECT GETDATE()-0.2,'Msg #9',1,0 UNION
SELECT GETDATE()-0.1,'Msg #10',1,1

---------

UPDATE – 20-Apr-2015 – Solution 1


--

;WITH CTE1 AS 
(
	SELECT g1.*, ROW_NUMBER() OVER (ORDER BY g1.ID) rnk
	FROM Grouper g1 left JOIN Grouper g2 on g1.id + 1 = g2.id and g1.SendState = g2.SendState and g1.AckState  = g2.AckState
	WHERE g2.ID IS NULL
),
CTE2 AS 
(
	SELECT g2.*,ROW_NUMBER() OVER (ORDER BY g2.ID) rnk
	FROM Grouper g1 RIGHT JOIN Grouper g2 on g1.id = g2.id - 1 and g1.SendState = g2.SendState and g1.AckState  = g2.AckState
	WHERE g1.ID IS NULL
)
SELECT c2.ID,c1.ID,c1.SendState,c2.AckState FROM CTE1 c1 INNER JOIN CTE2 c2 on c1.rnk = c2.rnk

--

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

Keep Learning

http://MSBISkills.com