CODEWITHSUNDEEP
sql-server
sm86
sm86

Reputation: 87

criteria to return correct results not working in sql server

Is there a way to only show the yellow results below? Using sql server I've tried doing where provider_name = previous_discharge_prov or previous_discharge_prov is null but it returns row _num 1 and 3 for cust_id 120343796 instead of 2 and 3. Thanks.

enter image description here

Upvotes: 0

Views: 21

Answers (1)

Thom A
Thom A

Reputation: 95561

The logic is very unclear here, however, at a guess based on your comment "I need to show the cust_id's who were admitted consecutively to the same provider" perhaps you mean this:

WITH CTE AS(
    SELECT {Your Needed Columns},
           LAG(ProviderName) OVER (PARTITION BY CustID ORDER BY RowNum) AS PreviousProvider,
           LEAD(ProviderName) OVER (PARTITION BY CustID ORDER BY RowNum) AS NextProvider
    FROM dbo.YourTable)
SELECT {Your Needed Columns}
FROM CTE
WHERE ProviderName IN (PreviousProvider,NextProvider);

Upvotes: 1

Related Questions