Tag Archives: SQL Server Data Tools

T-SQL Query | [ Complete the sequence Puzzle ]

01 Wednesday Apr 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 1 Comment

Tags

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


T-SQL Query | [ Complete the sequence Puzzle ]

The puzzle is simple. Here the participants were given month-wise score values and were asked to complete the sequence by creating entries for missing month. Please check out the sample input and expected output for details.

Sample Input

YearMonth Score
200903 100
200803 95
200802 99
200801 100
200711 100

Expected output

yearmonth score
200711 100
200712 100
200801 100
200802 99
200803 95
200804 95
200805 95
200806 95
200807 95
200808 95
200809 95
200810 95
200811 95
200812 95
200901 95
200902 95
200903 100
200904 100
200905 100
200906 100
200907 100
200908 100
200909 100
200910 100
200911 100
200912 100
201001 100
201002 100
201003 100
201004 100
201005 100
201006 100
201007 100
201008 100
201009 100
201010 100
201011 100
201012 100
201101 100
201102 100
201103 100
201104 100
201105 100
201106 100
201107 100
201108 100
201109 100
201110 100
201111 100
201112 100
201201 100
201202 100
201203 100
201204 100
201205 100
201206 100
201207 100
201208 100
201209 100
201210 100
201211 100
201212 100
201301 100
201302 100
201303 100
201304 100
201305 100
201306 100
201307 100
201308 100
201309 100
201310 100
201311 100
201312 100
201401 100
201402 100
201403 100
201404 100
201405 100
201406 100
201407 100
201408 100
201409 100
201410 100
201411 100
201412 100
201501 100
201502 100
201503 100
201504 100

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.

 


DECLARE @Scores TABLE
(
YearMonth   INT,
Score       INT
)

INSERT @Scores VALUES(200903, 100)
INSERT @Scores VALUES(200803, 95)
INSERT @Scores VALUES(200802, 99)
INSERT @Scores VALUES(200801 ,100)
INSERT @Scores VALUES(200711, 100)

UPDATE – 20-Apr-2015 – Solution 1 


--

;WITH CTE AS ( 
	SELECT CAST ( LEFT(YearMonth,4) + '/' + RIGHT(YearMonth,2) + '/' + '01' AS VARCHAR(10)) YearMonth , Score ,ROW_NUMBER() OVER ( ORDER BY YearMonth ) 
	ranker FROM SCORES 
) 
,CTE1 AS 
( 

	SELECT c1.YearMonth, CAST ( '<x>' + CAST(c1.ranker AS VARCHAR(10)) + '</x>' AS XML ) ranker,c1.ranker rnk 
	,c1.Score,ISNULL(c2.YearMonth,c1.YearMonth) YearMonthEnds FROM CTE c1 
	LEFT JOIN CTE c2 ON c1.ranker = c2.ranker - 1 
)
,CTE2 AS 
( 
			SELECT *, DATEDIFF ( M ,YearMonth, YearMonthEnds )  diff , CAST ( REPLICATE( CAST ( ranker AS VARCHAR(10)) + ',' , 
            CASE WHEN DATEDIFF ( M ,YearMonth, YearMonthEnds ) > 1     THEN  DATEDIFF ( M ,YearMonth, YearMonthEnds ) ELSE 1 END ) AS XML ) xmlcol FROM CTE1 ) 
			SELECT CONVERT(Varchar(7),DATEADD(m,RowNum,YearMonth),121) as AllocationMonth,Score FROM CTE2 s
CROSS APPLY
(
     SELECT project.D.value('.','VARCHAR(50)') as SplitData,ROW_NUMBER() OVER(Partition by Project.D.value('.', 'varchar(50)') ORDER BY (SELECT NULL)) -1 as RowNum   
     FROM s.xmlcol.nodes('x') as project(D)
) p ORDER BY AllocationMonth DESC

--

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 Max Puzzle ]

01 Wednesday Apr 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

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


T-SQL Query | [ The Max Puzzle ]

The puzzle is simple. Here we have find maximum from both the columns from sample input table. Please check out the sample input and expected output for details.

Sample Input

Exchange_Rate Date
0.011978 14-07-2013
0.01198 13-07-2013
0.011979 14-07-2013
0.011979 13-07-2013
0.01199 10-07-2013
0.011999 09-07-2013

Expected output

Date Exchange_Rate
14-07-2013 0.011999

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 Data

CREATE TABLE MAxExchange ( Exchange_Rate FLOAT , Date DATE )

--Insert Data
INSERT INTO MAxExchange(Exchange_Rate,Date)
VALUES
(0.011978,'2013-07-14' ),
(0.011980, '2013-07-13'),
(0.011979, '2013-07-14'),
(0.011979, '2013-07-13'),
(0.011990, '2013-07-10'),
(0.011999, '2013-07-09')

--

UPDATE – 11-Apr-2015 – Solution 1


--

--Solution 1

SELECT MAX(Exchange_Rate) Exchange_Rate , MAX(Date) Date FROM MAxExchange

--

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

≈ 3 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

T-SQL Query | [ The Sorting (Horizontal & Vertical) Puzzle ]

31 Tuesday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 2 Comments

Tags

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


T-SQL Query | [ The Sorting (Horizontal & Vertical) Puzzle ]

Original puzzle link – http://beyondrelational.com/blogs/tc/archive/2009/06/23/tsql-challenge-10-horizontal-and-vertical-sorting.aspx

The puzzle is simple. In this puzzle we needed to sort a result set horizontally as well as vertically. Please check out the sample input and expected output for details.

Sample Input

c1 c2 c3
2 1 3
3 2 1
Z X Y
B C D
Y Z X
B C A

Expected output

C1 C2 C3
1 2 3
A B C
B C D
X Y Z

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 Sorting
(
c1 CHAR(1),
c2 CHAR(1),
c3 CHAR(1)
)

insert into Sorting (c1, c2, c3) values ('2','1','3')
insert into Sorting (c1, c2, c3) values ('3','2','1')
insert into Sorting (c1, c2, c3) values ('Z','X','Y')
insert into Sorting (c1, c2, c3) values ('B','C','D')
insert into Sorting (c1, c2, c3) values ('Y','Z','X')
insert into Sorting (c1, c2, c3) values ('B','C','A')

--

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


--


--Solution 1 | Using XML and CrossApply


;WITH CTE AS
( 
    SELECT ROW_NUMBER() OVER (ORDER BY c1) AS id,
     c1,c2,c3,
     CAST('<x>'+c1+'</x><x>'+c2+'</x><x>'+c3+'</x>' 
      AS XML) AS xmlcol
FROM Sorting
)
,CTE1 AS 
(
      SELECT id,splitdata,rnk FROM CTE s
      CROSS apply
      (
            SELECT ProjectData.D.value('.', 'varchar(50)') as splitdata
            , ROW_NUMBER() over (partition by id 
			order by (ProjectData.D.value('.', 'varchar(50)'))) rnk
            FROM s.xmlcol.nodes('x') as ProjectData(D)
      ) b
)
SELECT DISTINCT [1] [C1], [2] [C2], [3] [C3]
FROM CTE1
  PIVOT
  (MAX(splitdata) FOR rnk IN ([1],[2],[3])) p
ORDER BY [1], [2], [3]



--Solution 2 | Using Pivot and Unpivot



;WITH 
unpivotted AS (
     SELECT *
     FROM (
           SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum, * 
           FROM Sorting
     ) t UNPIVOT (vals FOR col IN (c1,c2,c3)) p
)
, 
ordered_cols AS (
     SELECT     rownum, vals, 
                ROW_NUMBER() OVER (
                     PARTITION BY rownum ORDER BY vals) AS colnum
     FROM unpivotted
)
SELECT DISTINCT [1] [C1], [2] [C2], [3] [C3]
FROM ordered_cols
  PIVOT
  (MAX(vals) FOR colnum IN ([1],[2],[3])) p
ORDER BY [1], [2], [3]





--

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

Keep Learning

http://MSBISkills.com