Tags

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


SQL Puzzle | The Row Number Puzzle

In this puzzle we have to get each customers data in a single row. In the period a customer will either have host value of blank, actual host name or unassigned. We also have to find the when the customer started with us and when was last time he contacted us, who was his/her contact. Please check out the sample input values and sample expected output below.

Can you do that in a single select query ?

Sample Input

changeDate customerid sales_before sales_after
2017-07-06 7000275 NULL Amy Smith
2017-07-14 7000275 Amy Smith Unassigned
2017-07-18 7000275 UnAssigned Amy Smith
2017-07-06 7000287 Unassigned John West
2017-07-05 7000333 NULL Amy Smith
2017-06-13 7000333 Amy Smith Unassigned
2017-07-19 7000333 UnAssigned Joe west
2017-07-26 7000333 Joe west John Doe

Expected Output

Customerid FirstChangeDate LastChangeDate NumberChanges sales_before sales_after
7000275 2017-07-06 2017-07-18 3 NULL Amy Smith
7000287 2017-07-06 2017-07-06 1 Unassigned John West
7000333 2017-06-13 2017-07-26 4 Amy Smith Unassigned

Script

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

--


--

CREATE TABLE MinMax
(
	changeDate date,
	customerid integer,
	sales_before varchar(100),
	sales_after varchar(100)
)
GO


INSERT INTO MinMax 
VALUES 
( '20170706', 7000275, NULL, 'Amy Smith' ) ,
( '20170714', 7000275, 'Amy Smith', 'Unassigned' ) ,
( '20170718', 7000275, 'UnAssigned', 'Amy Smith' ) ,
( '20170706', 7000287, 'Unassigned', 'John West' ) ,
( '20170705', 7000333, NULL, 'Amy Smith' ) ,
( '20170613', 7000333, 'Amy Smith', 'Unassigned' ) ,
( '20170719', 7000333, 'UnAssigned', 'Joe west' ) ,
( '20170726', 7000333, 'Joe west', 'John Doe' )
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


--
 
SELECT * FROM 
(
	SELECT Customerid , MIN(changeDate) FirstChangeDate , MAX(changeDate) LastChangeDate , COUNT(*)  NumberChanges FROM MinMax
	GROUP BY customerid
)k
CROSS APPLY
(
	SELECT TOP 1 sales_before FROM MinMax a
	WHERE a.customerid = k.customerid 
	ORDER BY changeDate
)r
CROSS APPLY
(
	SELECT TOP 1 sales_after FROM MinMax a
	WHERE a.customerid = k.customerid 
	ORDER BY changeDate
)r1

--

 

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