I am getting a very strange issue using SQL. I am running this in SQL Server Management Studio. I have built two CTEs trying to find dates where membership starts and membership ends, with the end goal of being able to show the date ranges where members are active. The code that builds the CTEs is fairly extensive and I am leaving it out to start, but if it turns out to be needed I can add it. starting_points and ending_points are the names of the CTEs.
I run this query
SELECT *
FROM starting_points starts
JOIN ending_points ends
ON ends.MEMBER = starts.MEMBER
AND ends.rowNum = starts.rowNum
WHERE ends.MEMBER = 1
This is the result
MEMBER | begin_date | rowNum | MEMBER | end_date | rowNum |
---|---|---|---|---|---|
1 | 20201116 | 1 | 1 | 20201118 | 1 |
1 | 20201120 | 2 | 1 | 20210316 | 2 |
1 | 20210402 | 3 | 1 | 99991231 | 3 |
When I run this code
SELECT *
FROM starting_points starts
JOIN ending_points ends
ON ends.MEMBER = starts.MEMBER
AND ends.rowNum = starts.rowNum
ORDER BY ends.MEMBER
I get this result
MEMBER | begin_date | rowNum | MEMBER | end_date | rowNum |
---|---|---|---|---|---|
1 | 20201116 | 1 | 1 | 20201118 | 1 |
1 | 20201120 | 2 | 1 | 99991231 | 2 |
2 | 20201113 | 1 | 2 | 99991231 | 1 |
3 | 20211209 | 1 | 3 | 99991231 | 1 |
I am very confused as to why this is happening. As far as I know, the where clause should only filter down to return rows where the condition is met, but in this case it is adding a new row and changing the value in one of the columns in row 2. For clarity, the correct return is the one that uses the where clause and returns three rows for member #1.
As far as I can tell this is only happening on one member. I have spot checked about 20 other members and have not found this happening on any of them.
Thank you in advance for the help.