Updated: Loading all existing information too when using bridge table

[megasupermoon]

I am making a report where the user can see the number of patient falls (HandicapEvents) that occurred in different departments by year, month, day of the week and work shifts. Except for one bar graph, everything else works as intended, and there are no errors in the numbers or other information. The graph shows how many events there are in relation to all treatment days in the unit in relation to 1000 treatment days.

Sum(HANDICAP_AMOUNT) * Sum(HANDICAPEVENT_DURATION) * 1000

However, the numbers are wrong, because in treatment days, the code only takes treatment days related to events. According to my research, when querying the table CarePeriods alone, it gives the correct number of days per unit, but the BridgeTable supposedly messes it up?

How would I get all the existing treatment days for each unit for the graph without messing anything up and breaking Bridge? With new load script (without breaking/deleting brigde), maybe, or something?

Queries and data model (don’t worry about the sync tables) attached.

Thanks for advance!

EDIT: It would be awesome if someone can decode/understand "messy" data model and give advice/help me using that, but I added simplified(?) data model too. 

[Kushal_Chawda]

@megasupermoon  your data model is really messy. It has so many synthetic keys. Not sure if I can decode this to identify the issue. Probably you can narrow down to the tables involved which corresponds to your issue with sample data example.