So i have a fact table which contains date, sessionid and userid. I have a dim table which contains sessionid and license status. Bothe are connected by sessionid by one to many relationship. I want to track overtime changes by (granularity of day) when was a single persons license status was changed ( day) and also changed to what. (see output table columns in red)
I have 5 licnese type blank(which means no status has been recorded),free, pro, semi-pro, max. I want to track changes for every userid. solutions in any form would be appreciated thank you
Input tables
| Fact table | |
| | | |
| date | sessionid | userid |
| 01.01.23 | abc | person1 |
| 05.05.23 | cdc | person2 |
| 0.4.06.23 | kla | person1 |
| dim table | |
| | |
| sessionid | license status |
| abc | Free |
| cdc | blank |
| kla | Pro |
Output table
| dim table | | | |
| | | | |
| sessionid | license status | status changed | date changed |
| abc | Free | | 01.01.23 |
| cdc | blank | | |
| kla | Pro | Free-> Pro | 0.4.06.23 |
