Daily Archives: May 2, 2015

T-SQL Query | [ The Overlapping Dates Puzzle ]

02 Saturday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

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


T-SQL Query | [ The Overlapping Dates Puzzle ]

Puzzle Statement

1. We have a table named Overlap.
2. Here we have start date and end date , now if the end date + 1 = start date; These dates are called Overlapping dates, we have to club those rows into one row
3. For details please check out the sample input and expected output

OverlappingPuzzle

Sample Input

Id StartDate StopDate
10 25-01-2014 30-01-2014
11 21-02-2014 24-02-2014
12 07-04-2014 30-04-2014
13 01-11-2014 30-11-2014
14 01-12-2014 31-12-2014

Expected Output

ID StartDate EndDate
13 01-11-2014 01-12-2014

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 Overlap
(
	 Id INT
	,StartDate DATETIME
	,StopDate DATETIME
)
GO

INSERT INTO Overlap VALUES
(10     ,'2014-01-25 00:00:00.000'     ,'2014-01-30 00:00:00.000'),
(11     ,'2014-02-21 00:00:00.000'     ,'2014-02-24 00:00:00.000'),
(12     ,'2014-04-07 00:00:00.000'     ,'2014-04-30 00:00:00.000'),
(13     ,'2014-11-01 00:00:00.000'     ,'2014-11-30 00:00:00.000'),
(14     ,'2014-12-01 00:00:00.000'     ,'2014-12-31 00:00:00.000')


--

Update 2 May | Solution 1 


--

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


;WITH CTE AS
(
	SELECT a.*,DATEADD(DAY, 1, A.StopDate) Ndt
	FROM Overlap a LEFT OUTER JOIN Overlap b
	ON a.Id = b.Id
)
SELECT b.ID, b.StartDate , b.Ndt EndDate FROM CTE c LEFT JOIN CTE b ON c.StartDate = b.Ndt WHERE b.Id IS NOT NULL

--

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