Tags

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


SQL Puzzle | Not the Last Order Puzzle

In this puzzle we have check for the order Id = 001, If 001 orderid is the last order id for any last name then we should omit all the records for that LastName. if 001 orderid is NOT the last order id for any last name then we should get all the records for that LastName. Please check the sample input and expected output.

Sample Input

LastName OrderId OrderDate
A 001 2017-12-17 07:37:32.383
B 002 2017-12-16 07:37:32.383
B 001 2017-12-17 07:37:32.383
C 001 2017-12-15 07:37:32.383
C 004 2017-12-16 07:37:32.383
C 005 2017-12-17 07:37:32.383
D 005 2017-12-17 13:45:34.007

Expected Output

LastName OrderDate OrderId
C 2017-12-15 07:37:32.383 001
C 2017-12-16 07:37:32.383 004
C 2017-12-17 07:37:32.383 005
D 2017-12-17 13:45:34.007 005

Script

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

--

CREATE TABLE LastOrders
(
	 LastName Varchar(20)
	,OrderId Varchar(10)
	,[OrderDate] DATETIME
)
GO

INSERT INTO LastOrders VALUES 
('A', '001' , GETDATE() )		   ,
('B', '002' , GETDATE() -1 )	   ,
('B', '001' , GETDATE() )		   ,
('C', '001' , GETDATE() -2 )	   ,
('C', '004' , GETDATE() - 1 )	   ,
('C', '005' , GETDATE() )
GO

INSERT INTO LastOrders VALUES 
('D', '005' , GETDATE() )
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 – 1

--

;WITH cteorders AS
(
	SELECT * FROM 
	(
		SELECT u.LastName, u.[OrderID], u.[OrderDate]
		FROM 
		(
			SELECT DISTINCT LastName FROM LastOrders
		)x
		CROSS APPLY 
		(
			SELECT TOP 1 * 
			FROM LastOrders k			
			WHERE k.LastName = x.LastName	
			ORDER BY OrderDate DESC
		)u
	)P WHERE [OrderID]  <> '001'
)
SELECT L.LastName,L.OrderDate,L.OrderId FROM cteorders c
INNER JOIN LastOrders L ON c.LastName = L.LastName

--

Output

--

/*------------------------
;WITH cteorders AS
(
	SELECT * FROM 
	(
		SELECT u.LastName, u.[OrderID], u.[OrderDate]
		FROM 
		(
			SELECT DISTINCT LastName FROM LastOrders
		)x
		CROSS APPLY 
		(
			SELECT TOP 1 * 
			FROM LastOrders k			
			WHERE k.LastName = x.LastName	
			ORDER BY OrderDate DESC
		)u
	)P WHERE [OrderID]  <> '001'
)
SELECT L.LastName,L.OrderDate,L.OrderId FROM cteorders c
INNER JOIN LastOrders L ON c.LastName = L.LastName
------------------------*/
LastName             OrderDate               OrderId
-------------------- ----------------------- ----------
C                    2017-12-15 07:37:32.383 001
C                    2017-12-16 07:37:32.383 004
C                    2017-12-17 07:37:32.383 005
D                    2017-12-17 13:45:34.007 005

(4 row(s) affected)

                                                                          
--

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