I think that based on the small data set example that I gave you this would achieve what I asked, thank you for your response. This does make sense in how to solve. My data set is significantly larger and it is coming from multiple tables of a database which all combined, there are about 60 columns of data. With this connection, I cannot pivot the data and I think if I could it would present many other challenges.
Is there a solution that I could use without Pivoting the data?
That's the data set problem and you need to use ETL tool to prepare appropriate data set.
2 of 2 people found this helpful
your case looks exactly like the challenge we faced in our my company. We wanted to calculate the number of contractors working each day and, as in your case, we had only start/end date.
This is issue is easily resolved with scaffolding - don't let it intimidate you! You just join your data with a second table with each day as a new record for the date range you need. This way every patient will (one row) will be multiplied over many rows. Using filters you can filter out the dates that are not needed (before start date and after end date).
I think I used this article initially, but there are a lot of great resources on scaffolding.