Tags

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


T-SQL Query | [ The work order puzzle ]  – In this puzzle we have to find out orders where the StepId = zero and Status = ‘C AND all the remaining rows for that OrderId Should have Status= ‘W’, For example, the query should return only ‘AA100’ in the sample data. Please check out the sample input and expected output for details.

Sample Input

WorkOrderID STEP_NBR STEP_STATUS
AA100 0 C
AA100 1 W
AA100 2 W
AA200 0 W
AA200 1 W
AA300 0 C
AA300 1 C

Expected Output

WorkOrderID
AA100

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

CREATE TABLE WorkOrders
(
WorkOrderID CHAR(5) NOT NULL,
STEP_NBR INTEGER NOT NULL CHECK (step_nbr BETWEEN 0 AND 1000),
STEP_STATUS CHAR(1) NOT NULL CHECK (step_status IN ('C', 'W')), -- complete, waiting
)
GO

--Insert Data
INSERT INTO WorkOrders(WorkOrderID,STEP_NBR,STEP_STATUS) VALUES
('AA100', 0, 'C'),
('AA100', 1, 'W'),
('AA100', 2, 'W'),
('AA200', 0, 'W'),
('AA200', 1, 'W'),
('AA300', 0, 'C'),
('AA300', 1, 'C')
GO


SELECT WorkOrderID,STEP_NBR,STEP_STATUS FROM WorkOrders

Update May 14 | Solutions



--


---------------------------------------
--Sol 1
---------------------------------------

SELECT workorder_id
FROM WorkOrders
GROUP BY workorder_id
HAVING COUNT(*) =    COUNT(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1 ELSE NULL END) 
                   + COUNT(CASE WHEN step_nbr = 0 AND step_status = 'C' THEN 1 ELSE NULL END)


---------------------------------------
--Sol 2
---------------------------------------
				   
SELECT workorder_id
FROM Projects
GROUP BY workorder_id
HAVING COUNT(step_nbr) = SUM
							(
							CASE
							WHEN step_nbr <> 0 AND step_status = 'W' THEN 1
							WHEN step_nbr = 0 AND step_status = 'C' THEN 1
							ELSE 0 END
							)




--

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

Keep Learning

http://MSBISkills.com

Advertisements