Calendar Conundrum

[cbushey1]

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.

Thanks for the help!

[jonathandienst]

Henric Cronstrom addresses exactly this issue here: Canonical Date

[cbushey1]

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.

Looking for advice on getting the dates together.

[Colin-Albert]

If you do not have full dates, then you can use makedate to generate a date equal to the first (or last) day of the period or quarter.

[cbushey1]

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.