Best data model for finite states date transitions

DiegoLotti — Thu, 09 Jan 2020 14:02:18 GMT

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()).

Can you help ?

topic Best data model for finite states date transitions in App Development

Best data model for finite states date transitions