Best data model for finite states date transitions
Hello, I would like to visualize the number of documents in a particular state by date.
My documents have daily changes to their states and I have a log table with the start date & end date (null if this is the current state). The table structure is this:
fileId
state
start date
end date
1000
-1
STARTDATE
01/01/2020 08:29
1000
0
01/01/2020 08:29
03/01/2020 12:15
1000
1
03/01/2020 12:15
06/01/2020 14:17
1000
2
06/01/2020 14:17
07/01/2020 10:44
1000
3
07/01/2020 10:44
NULL
2000
-1
STARTDATE
01/01/2020 11:07
2000
0
01/01/2020 11:07
02/01/2020 17:13
2000
1
02/01/2020 17:13
04/01/2020 14:17
2000
2
04/01/2020 14:17
05/01/2020 10:44
2000
3
05/01/2020 10:44
NULL
I would like to write a measure that counts all the files that were in a particular state, for each date value. I will filter state=1 (for example) and I would like a chart which shows the number of files in state 1 at 01/01/2020, 02/01/2020, ... and so on. I need to visualize the time trend of the various states.
Can I write a measure or it's better to calculate the scalar product of document states and calendar dates ?
In SQL I would join calendar table with document state table ON calendar.date between documentState.startDate AND ISNULL(documentState.endDate, GETDATE()).
