CODEWITHSUNDEEP
sql-servert-sql
SajjadZare
SajjadZare

Reputation: 2378

Write a nested query in Sql server

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

enter image description here

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

Upvotes: -1

Views: 75

Answers (1)

RickyTillson
RickyTillson

Reputation: 333

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

Upvotes: 1

Related Questions