Tag Archives: SQL SERVER2005/2008

T-SQL Query | [ The Non Zero Puzzle ]

02 Saturday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 2 Comments

Tags

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


T-SQL Query | [ The NON ZERO Puzzle ]

Puzzle Statement

1. We have two table named Atable and Btable
2. In table A we have a column called Value. Now this column contains some zeros.
3. What we have to do is join these two tables and for each given Id return Value from Atable if the Value is Non Zero otherwise return Value from BTable(Value)
4. For details please check out the sample input and expected output
5. Note that – We CANNOT use CASE,IIF,IF,REPLACE,UNION,UNION ALL.

NonZeroPuzzle

Sample Input

ATABLE

Id Value
1 0
2 12
3 9
4 0
5 0

BTABLE

Id Value
1 15
2 5
3 1
4 7
5 10

Expected Output

Id Value
1 15
2 12
3 9
4 7
5 10

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”
  • Your solution should be generic in nature.
  • We CANNOT use CASE,IIF,IF,REPLACE,UNION,UNION ALL.
  • 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 Atable
(
	  Id INT
	, Value INT
)
GO

CREATE TABLE Btable 
(
	  Id INT
	, Value INT
)
GO

INSERT INTO Atable VALUES (1,0),(2,12),(3,9),(4,0),(5,0)
INSERT INTO Btable VALUES (1,15),(2,5),(3,1),(4,7),(5,10)


--

Update Apr 29 | Solution 1 & 2 


--

------------------ SOLUTION 1 ----------------------


;WITH CTE AS 
(
    SELECT a.Id , a.Value FROM Atable a WHERE a.Value != 0
)
SELECT b.Id , ISNULL(c.Value,b.Value) Value FROM Btable b
LEFT OUTER JOIN CTE c ON c.Id =b.ID 



------------------ SOLUTION 2 ----------------------


SELECT a.Id, ISNULL(NULLIF(a.Value,0),b.Value) Value
FROM 
Atable a INNER JOIN Btable b ON a.Id = b .Id

--

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

Keep Learning

Http://MSBISkills.com

T-SQL Query | [ Finding the missing Gaps Puzzle ]

02 Thursday Apr 2015

Posted by in SQL Concepts, SQL SERVER, T SQL Puzzles, Tricky SQL Queries

≈ 5 Comments

Tags

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


T-SQL Query | [ Finding  the missing Gaps Puzzle ]

The puzzle is simple. Here you have to find the missing gaps between sequential numbers. Please check out the sample input and expected output for details.

Sample Input

num
1
2
3
6
8
11
12
13
15
17
28

Expected output

GapStart GapEnd
4 5
7 7
9 10
14 14
16 16
18 27

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 findGaps (num int)

insert into findGaps
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

GO

CREATE CLUSTERED INDEX Ix_Num ON findGaps(num)

UPDATE – 11-Apr-2015 – Solution 1


--

--Sol 1

SELECT GapStart + 1 GapStart , GapEnd - 1 GapEnd FROM 
(
	SELECT num GapStart , LEAD(num,1,0) OVER (ORDER BY Num) GapEnd , LEAD(num,1,0) OVER (ORDER BY Num) - num Gap 
	FROM findGaps 
) a
WHERE Gap > 1

--

Execution Plan below – Its a clean plain execution plan with some simple maths calculation.

Execution Plan

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Vendor and the Coupon Puzzle ]

01 Wednesday Apr 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

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


T-SQL Query | [ Vendor and the Coupon Puzzle ]

In this puzzle we have to find the latest discount for each row in the vendor table. The Please check the sample input and the expected output for details.

Sample Input

Vendor

Vendor Date
McDonald 02-03-2014
SubWay 01-05-2014
McDonald 02-06-2014
McDonald 14-03-2014
McDonald 04-06-2014
McDonald 05-08-2014
SubWay 09-06-2013
SubWay 07-06-2014
SubWay 01-05-2014
SubWay 15-05-2014

Coupon

VendorName CouponDate Discount(%)
McDonald 05-03-2014 10
McDonald 01-06-2014 20
McDonald 16-02-2014 25
McDonald 25-03-2014 15
McDonald 10-06-2014 27
McDonald 10-08-2014 30
SubWay 11-03-2014 25
SubWay 18-04-2014 30
SubWay 30-01-2014 15
SubWay 02-05-2014 17

Expected output

Vendor Date Discount
McDonald 02-03-2014 25
SubWay 01-05-2014 30
McDonald 02-06-2014 20
McDonald 14-03-2014 10
McDonald 04-06-2014 20
McDonald 05-08-2014 27
SubWay 09-06-2013 0
SubWay 07-06-2014 17
SubWay 01-05-2014 30
SubWay 15-05-2014 17

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 [dbo].[Coupons]

(

[VendorName] [varchar](50) NULL,

[CouponDate] [datetime] NULL,

[Discount(%)] [int] NULL

)

CREATE TABLE [dbo].[Vendors]

(

[Vendor] [varchar](50) NULL,

[Date] [datetime] NULL

) ON [PRIMARY]

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A2E500000000 AS DateTime), 10)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A33D00000000 AS DateTime), 20)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A2D400000000 AS DateTime), 25)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A2F900000000 AS DateTime), 15)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A34600000000 AS DateTime), 27)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A38300000000 AS DateTime), 30)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A2EB00000000 AS DateTime), 25)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A31100000000 AS DateTime), 30)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A2C300000000 AS DateTime), 15)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A31F00000000 AS DateTime), 17)

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A2E200000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A31E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A33E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A2EE00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A34000000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A37E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A1D800000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A34300000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A31E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A32C00000000 AS DateTime))

GO

Please leave a comment if you need solution to the above puzzle

Keep Learning

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