I need to find the immediate parent id based on the below condition:
|CustomerNum|ParentCustomerNum|IsPlatform|
|-----------|-----------------|----------|
|132 |0 |1 |
|253 |132 |0 |
|2041 |253 |0 |
|2132 |253 |0 |
|521 |132 |0 |
|551 |132 |0 |
|594 |132 |1 |
|5642 |5434 |1 |
|5811 |5434 |1 |
Above is the sql table entries. I do have below condition to find the record.
1.) find record based on CustomerNum, check if it has ParentCustomerNum greater then 0 and IsPlatform is 1.
2.) find record based on CustomerNum, check if it has ParentCustomerNum is 0 and IsPlatform is 1.
3.) find record based on CustomerNum where ParentCustomerNum is greater then 0 and IsPlatform is not 0, then find the parent record and check again if that record has ParentCustomerNum is greater then 0 and IsPlatform is not 0.
What I have tried:
I tried CTE but having no luck on that side, I am new to the sql part.
WITH cte AS
(
SELECT CustomerId, ISSCustId, CustomerName, CustomerNum, IsPlatform, CustomerNum As TopParent
FROM [dbo].[VwCustomer]
UNION ALL
SELECT T.CustomerId, T.ISSCustId, T.CustomerName,
T.CustomerNum, T.IsPlatform, cte.TopParent
FROM [dbo].[VwCustomer] As T
JOIN cte
ON T.ParentCustomerNum = cte.CustomerNum
)
SELECT *
FROM cte
WHERE cte.CustomerNum = 253
ORDER BY TopParent DESC;
Expected values/query:
1. CustomerNum 594 has ParentCustomerNum as 132 and IsPlatform 1 (true). So, My query will stop here because IsPlatform is true. and will return this record only.
2. If for the above record IsPlatform is 0 (false) then query will traverse for CustomerNum 132 and will check if it has ParentCustomer and IsPlatform as false. @siggemannen let me know is any part is not clear. Thank you.