I am trying to create a singular calendar that the user can select from but my problem is that I have multiple datasources and dates.
Here is how my data model breaks down:
Table 1:Nurse hours
ID =LocationID&Year&Month
Location
Year
Month
Table 2: Pt Days
ID =LocationID&Year&Month
Location
Year
Month
Table 3: Falls
ID =LocationID&Year&Month
Location
Year
Month
Table 4:Ulcers
ID =HospitalID&Year&Quarter
Year
Quarter
Table 5: Perinatal
ID HospitalID&Year&Month
Year
Month
Table 6: Lactation
ID =HospitalID&Year&Month
Year
Month
Table 7:AbsData
ID =AbsAccount
StartDateTime
EndDateTime
LocationID
Table 8:CodeBlue
ID =LocationID&Year&Month&Day
LocationName
CodeDate
CodeTime
Table 9:Incidents
ID =IncidentID
LocationName
EventDate
EventTime
Link:
ID1 = LocationID&Year&Month
ID2 = HospitalID&Year&Quarter
ID3 = HospitalID&Year&Month
ID4 = AbsAccount
ID5 = LocationID&Year&Month&Day
ID6 = IncidentID
YearMonth (essentially the year and month of each table’s date field)
I then build a master calendar off the year month field but I know I have issues with it because most of my tables only have one date field, but my AbsData table has two thus causing a problem.
Yes I have read that and I think that might be the direction I need to go in, but alot of my tables dont have full dates. All they have is either Year and month or year and quarter. Now I could use a makedate and just give them an arbitrary day and/or Month but I wasnt sure if that is the best approach either.
I know about Makedate but I am not sure that is the best approach given I am arbitrarily choosing a day (1) for these events to have occurred when in fact they occurred over the whole month.
I guess I will see if there is a better way to do this whole table structure. I think I may need to do a canonical date.
