Write a nested query in Sql server

Question

Imagine that I have a table in the database to keep the history of customers' status.

If I want to get customers from for example status 1001 to 1002, it’s simple

Select * from TableName where StartStatus=1001 and EndStatus=1002

If I want to write a query that returns the customers that change from status 1001 to 1005, how can I do that?

The result should be just one record for each customer (I need to omit the internal changes for a customer, for example, do not need 1001 to 1002 and 1002 to 1003 and 1003 to 1004)

For example in this data, the customer with id 2 changed from 1006 to 1005, then the query shouldn't return it

I assume the table also has a CustomerId column? You probably need a recursive CTE anyway to follow the path of status changes. Be careful of infinite loops — Martin Smith
– Martin Smith, Commented Oct 26, 2022 at 8:25
I might be missing something but isn't this just a case of looking for where a customer has a record with a StartStatus = 1001 and also a record with an EndStatus = 1005? Can you not inner join to the same table joining on CustomerID (or similar) and then filtering to T1.StartStatus = 1001 and T2.EndStatus = 1005 — RickyTillson
– RickyTillson, Commented Oct 26, 2022 at 8:27
This question is very unclear. Please do not post images of data, add sample data as text in the quedtion with expected results, see Minimal, Reproducible Example — Stu
– Stu, Commented Oct 26, 2022 at 8:57
@JohnS is Customer 2 excluded based on not having a StartStatus of 1001 or because they 'regressed' to 1005? — RickyTillson
– RickyTillson, Commented Oct 26, 2022 at 8:57
@RickyTillson Customer 1's status journey was 1000 -> 1001 -> 1002 -> 1003 -> 1004 -> 1005. This includes a path from 1001 to 1005. Customer 2's status journey was just 1006 -> 1005. This does not include a path from 1001 to 1005 — Martin Smith
– Martin Smith, Commented Oct 26, 2022 at 9:05

RickyTillson · Accepted Answer · 2022-10-26 09:08:21Z

1

Assuming that we're not worried about Customers moving 'backwards' into 1005 as long as there is ever a StartStatus of 1001 and an EndStatus of 1005 this should work

CREATE TABLE #Customer (CustomerID INT, StartStatus INT, EndStatus INT)
INSERT INTO #Customer (CustomerID, StartStatus, EndStatus)
VALUES (1, 1000, 1001),
       (1, 1001, 1002),
       (1, 1002, 1003),
       (1, 1003, 1004),
       (1, 1004, 1005),
       (2, 1006, 1005)

SELECT C1.CustomerID, C1.StartStatus, C2.EndStatus
FROM #Customer AS C1
INNER JOIN #Customer AS C2 ON C2.CustomerID = C1.CustomerID 
WHERE C1.StartStatus = 1001 AND C2.EndStatus = 1005

answered Oct 26, 2022 at 9:08

RickyTillson

3331 gold badge2 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write a nested query in Sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related