Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
WITH CTe_1
AS
(
SELECT * from WorkOrders
WHERE STEP_NBR=0 AND STEP_STATUS=’C’
)
SELECT WorkOrderID FROM CTe_1
WHERE WorkOrderID NOT IN
(
SELECT DISTINCT WO.WorkOrderID from WorkOrders WO
INNER JOIN CTE_1 CD
ON WO.WorkOrderID=CD.WorkOrderID
WHERE WO.STEP_NBR!=0
AND WO.STEP_STATUS!=’W’
)
LikeLike
select * from #WorkOrders a
where exists ( select * from #WorkOrders b
where a.WorkOrderID=b.WorkOrderID and STEP_NBR=0 and STEP_STATUS=’c’)
and exists ( select * from #WorkOrders b
where a.WorkOrderID=b.WorkOrderID and STEP_STATUS=’W’)
LikeLiked by 1 person
select WorkOrderID from WorkOrders where WorkOrderID in (
select WorkOrderID from WorkOrders
where step_nbr=0 and step_status=’C’
group by WorkOrderID ) and
step_status=’W’ group by WorkOrderID
LikeLike
it is very simple :
select workorderid from WorkOrders where STEP_NBR = 0 and STEP_STATUS = ‘c’
INTERSECT
select workorderid from WorkOrders where STEP_STATUS = ‘W’
LikeLiked by 1 person
select WorkOrderID from WorkOrders a
where STEP_NBR = 0 and STEP_STATUS = ‘C’
AND EXISTS
(select * from WorkOrders
where (STEP_NBR 0 and STEP_STATUS ‘C’) and STEP_STATUS = ‘W’
and WorkOrderID =a.WorkOrderID )
LikeLike
select w.WorkOrderID
from WorkOrders w
join
(
select WorkOrderID
from WorkOrders
where STEP_NBR = 0 and STEP_STATUS = ‘C’
)t
on t.WorkOrderID = w.WorkOrderID
group by w.WorkOrderID
having sum(case when w.step_status=’W’ then 1 else 0 end) = 2
LikeLiked by 1 person