I have been given a task where the data set looks like this (as an example) within SQL Server:
- Week Unit Customer Activity
- 1/8/2024 a 123 Install
- 1/8/2024 b 123 Install
- 1/8/2024 c 123 Removal
- 1/15/2024 d 123 Removal
- 1/15/2024 e 123 Install
- 1/22/2024 f 123 Removal
- 1/22/2024 g 123 Install
- 6/16/2024 h 123 Removal
- 6/16/2024 i 123 Removal
- 8/1/2024 j 123 Install
And the goal is to determine whether each install/removal is part of a replacement (meaning that each removal is offset by one install within an 8 week span, or vice versa) or if it is a new install (doesn’t have any offsetting removal within 8 weeks and is true "growth") or a true removal (doesn’t have an offsetting install within 8 weeks).
I am having a hard time writing into my view how to have each activity "find" its opposite to define itself as a replacement or to determine that it is a new install or true removal because it doesn’t have a match within that 8 week span. Has anyone done anything like this or have any ideas on how I could get started?
Thank you!
You need to sign in to view this answers
