5 Upcoming Staff Celebrations Shared by Jeff Richmond, The Well Community Church one year ago 12.0 General, Operations Intermediate Description This recipe displays a list of upcoming staff birthdays, anniversaries and workiversaries (yes, it's a word) with the person's photo, name, and date of the milestone. For anniversaries and workiversaries, it displays how many years they've been married or on staff. On the day of, it also displays a special "happy birthday/anniversary/workiversary" message. I know there are already a couple other recipes for displaying upcoming staff birthdays (etc.) on here, but since I've had three separate requests for my solution in the past few months, I figured I should just go ahead and finish up the last 10% to make an actual recipe out of it. If my solution isn't quite what you were looking for or you just want to see other options, be sure to check out these awesome recipes: Staff Birthdays by Brent Pirolli Staff Birthdays/Anniversaries by Randy Aufrecht (Please let me know if there are any other similar recipes I may have missed) A Note on Widowed Staff Members This recipe will still display upcoming anniversaries for staff members whose spouse has passed away unless they change their marital status to something other than "Married" and/or they remove the anniversary date from their profile. Because some people prefer to keep their marital status and anniversary date even after losing a spouse, we have also added a "Widowed" tag to our own Rock instance, and then added an additional filter to the upcoming anniversaries to make sure they are excluded from the list. This functionality is not included in this recipe, but please don't hesitate to let me know if you're interested in more info. Credit I can't remember for sure, but I think my solution may have actually started out life as an implementation of Brent's recipe. There are at least some definite layout similarities between the two. So if you like my recipe, Brent may deserve a good portion of the credit. If you don't, then he had nothing to do with it! Prerequisites Staff Person Tag We identify staff with an person tag: Name: Staff Scope: Organizational Entity Type: Person If you keep track of staff another way, you'll need to either create a Staff tag or modify the code in this recipe to work with your situation. You can also check out Brent's recipe for a solution that uses the RSR - Staff Workers security role (group), or Randy's recipe for a solution that uses a data view. Staff Hire/Exit Date Attributes You'll also need a way to keep track of the staff hire and exit dates. This recipe assumes those values are stored in person attributes. Name: Staff Hire Date and Staff Exit Date Key: StaffHireDate and StaffExitDate Show on Bulk: Yes Field Type: Date How-To Create a new Dynamic Data block on your internal Rock home page. Block Properties: Update Page: No Enabled Lava Commands: Cache Block Criteria: Query: There are some changes you'll need to make in the query. On lines 4 and 5, change the hire and exit date attribute IDs to match your system. On line 10, change the time zone to your own. Our database likes to return the current time using the UTC time zone, so we have to force dates to Pacific Time. If you're not Pacific, you'll need to change that. Finally, on line 15, the number of days to look ahead is currently set to 21, so you may want to adjust that too. DECLARE @Gender AS table ( ID int, Gender varchar(10) ) INSERT INTO @Gender SELECT 1, 'male' UNION SELECT 2, 'female' UNION SELECT 3, 'unknown' DECLARE @HireDateID AS int = 8215 DECLARE @ExitDateID AS int = 8216 DECLARE @MarriedStatusID AS int = 143 DECLARE @StaffTagID AS int SELECT @StaffTagID = ID FROM Tag WHERE Name = 'Staff' DECLARE @ChildClassID AS int = 2 DECLARE @Today AS datetime2 = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' DECLARE @ThisYear AS int = YEAR(@Today) DECLARE @ThisMonth AS int = MONTH(@Today) DECLARE @TodayNum AS int = DAY(@Today) DECLARE @TodayOfYear AS int = DATEPART(dayofyear, @Today) DECLARE @DayRange AS int = 21 SELECT A.ID, A.NickName, A.NickName + ' ' + A.LastName AS FullName, A.PhotoID, A.Email, A.EventMonth, A.EventDay, A.DaysUntilEvent, A.Years, IIF(A.AgeClassification = @ChildClassID, 'child-', '') + ISNULL(G.Gender, 'unknown') AS Gender, A.UpcomingBirthday, A.UpcomingAnniversary, A.UpcomingWorkiversary FROM ( SELECT P.ID, P.NickName, P.LastName, P.PhotoId, P.Email, P.AgeClassification, P.Gender, P.BirthYear AS EventYear, P.BirthMonth AS EventMonth, P.BirthDay AS EventDay, CASE WHEN P.BirthMonth = @ThisMonth AND P.BirthDay = @TodayNum THEN 0 ELSE DATEDIFF(day, @Today, CA.NextDate) END AS DaysUntilEvent, DATEDIFF(year, DATEFROMPARTS(P.BirthYear, P.BirthMonth, P.BirthDay), @Today) AS Years, CAST(1 AS bit) AS UpcomingBirthday, CAST(0 AS bit) AS UpcomingAnniversary, CAST(0 AS bit) AS UpcomingWorkiversary FROM Person AS P INNER JOIN TaggedItem AS T on P.Guid = T.EntityGuid AND T.TagID = @StaffTagID CROSS APPLY (SELECT DATEFROMPARTS( CASE WHEN P.BirthMonth > @ThisMonth OR (P.BirthMonth = @ThisMonth AND P.BirthDay >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, P.BirthMonth, CASE WHEN P.BirthMonth = 2 AND P.BirthDay = 29 AND ISDATE(CAST(@ThisYear AS varchar) + '-02-29') = 0 THEN 28 ELSE P.BirthDay END) AS NextDate) AS CA WHERE P.BirthMonth <> '' AND P.BirthYear <> '' AND (P.DaysUntilBirthday <= @DayRange OR (P.BirthMonth = @ThisMonth AND P.BirthDay = @TodayNum)) UNION ALL SELECT P.Id, P.NickName, P.LastName, P.PhotoId, P.Email, P.AgeClassification, P.Gender, YEAR(P.AnniversaryDate) AS EventYear, MONTH(P.AnniversaryDate) AS EventMonth, DAY(P.AnniversaryDate) AS EventDay, CASE WHEN MONTH(P.AnniversaryDate) = @ThisMonth AND DAY(P.AnniversaryDate) = @TodayNum THEN 0 ELSE DATEDIFF(day, @Today, CA.NextDate) END AS DaysUntilEvent, DATEDIFF(year, P.AnniversaryDate, @Today) AS Years, CAST(0 AS bit) AS UpcomingBirthday, CAST(1 AS bit) AS UpcomingAnniversary, CAST(0 AS bit) AS UpcomingWorkiversary FROM Person AS P INNER JOIN TaggedItem AS T on P.Guid = T.EntityGuid AND T.TagID = @StaffTagID CROSS APPLY (SELECT DATEFROMPARTS( CASE WHEN MONTH(P.AnniversaryDate) > @ThisMonth OR (MONTH(P.AnniversaryDate) = @ThisMonth AND DAY(P.AnniversaryDate) >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, MONTH(P.AnniversaryDate), CASE WHEN MONTH(P.AnniversaryDate) = 2 AND DAY(P.AnniversaryDate) = 29 AND ISDATE(CAST(@ThisYear AS varchar) + '-02-29') = 0 THEN 28 ELSE DAY(P.AnniversaryDate) END) AS NextDate) AS CA WHERE P.MaritalStatusValueID = @MarriedStatusID AND MONTH(P.AnniversaryDate) <> '' AND DAY(P.AnniversaryDate) <> '' AND (P.DaysUntilAnniversary <= @DayRange OR (MONTH(P.AnniversaryDate) = @ThisMonth AND DAY(P.AnniversaryDate) = @TodayNum)) UNION ALL SELECT P.ID, P.NickName, P.LastName, P.PhotoId, P.Email, P.AgeClassification, P.Gender, YEAR(V.ValueAsDateTime) AS EventYear, MONTH(V.ValueAsDateTime) AS EventMonth, DAY(V.ValueAsDateTime) AS EventDay, DATEDIFF(day, @Today, CA.NextDate) AS DaysUntilEvent, YEAR(CA.NextDate) - YEAR(V.ValueAsDateTime) AS Years, CAST(0 AS bit) AS UpcomingBirthday, CAST(0 AS bit) AS UpcomingAnniversary, CAST(1 AS bit) AS UpcomingWorkiversary FROM AttributeValue AS V INNER JOIN Person AS P ON P.ID = V.EntityID INNER JOIN TaggedItem AS T on P.Guid = T.EntityGuid AND T.TagID = @StaffTagID CROSS APPLY (SELECT DATEFROMPARTS( CASE WHEN MONTH(V.ValueAsDateTime) > @ThisMonth OR (MONTH(V.ValueAsDateTime) = @ThisMonth AND DAY(V.ValueAsDateTime) >= @TodayNum) THEN @ThisYear ELSE @ThisYear + 1 END, MONTH(V.ValueAsDateTime), CASE WHEN MONTH(V.ValueAsDateTime) = 2 AND DAY(V.ValueAsDateTime) = 29 AND ISDATE(CAST(@ThisYear AS varchar) + '-02-29') = 0 THEN 28 ELSE DAY(V.ValueAsDateTime) END) AS NextDate) AS CA WHERE AttributeID = @HireDateID AND (DATEDIFF(day, @Today, CA.NextDate) <= @DayRange OR (MONTH(V.ValueAsDateTime) = @ThisMonth AND DAY(V.ValueAsDateTime) = @TodayNum)) AND P.ID NOT IN (SELECT EntityID FROM AttributeValue AS EV WHERE EV.AttributeID = @ExitDateID AND NOT EV.ValueAsDateTime IS NULL AND EV.ValueAsDateTime <= @Today) ) AS A LEFT JOIN @Gender AS G ON G.ID = A.Gender ORDER BY A.DaysUntilEvent, A.EventMonth, A.EventDay, A.LastName, A.NickName, A.UpcomingAnniversary DESC, A.UpcomingBirthday DESC, A.UpcomingWorkiversary DESC Formatted Output: Our version displays our logo next to the "Happy workiversary" messages. For the purposes of this recipe our logo icon has been replaced by the thumbs up icon (). {%- cache key:'staff-milestones' duration:'3600' -%} <div class="panel panel-block milestones"> <div class="panel-heading"><h4 class="panel-title"><i class="fas fa-bullhorn"></i> Staff Celebrations</h4></div> <ul class="list-group"> {%- assign date = 'Now' | Date:' M/d' -%} {%- for row in rows -%} {%- assign iconClass = '' -%} {%- assign titleAttr = '' -%} {%- assign celebrateText = '' -%} {%- assign emailType = '' -%} {%- assign number = '' -%} {%- capture eventdate %}{{ row.EventMonth }}/{{ row.EventDay }}{% endcapture -%} {%- if row.UpcomingBirthday == true -%} {%- assign iconClass = 'fa fa-fw fa-birthday-cake' -%} {%- assign titleAttr = 'Birthday' -%} {%- assign emailType = 'a birthday' -%} {%- if date == eventdate -%} {%- capture celebrateText -%}<span class="celebrate"><i class="fa fa-gift"></i> <small>Happy Birthday!</small></span>{%- endcapture -%} {%- endif -%} {%- elseif row.UpcomingAnniversary == true -%} {%- assign iconClass = 'fa fa-fw fa-heart' -%} {%- assign number = row.Years | AsString | NumberToOrdinal -%} {%- if row.Years == 0 -%} {%- capture titleAttr -%}Getting married!{%- endcapture -%} {%- assign emailType = 'a congratulations' -%} {%- assign number = 'Soon!' -%} {%- else -%} {%- capture titleAttr -%}{{ number }} Anniversary{%- endcapture -%} {%- assign emailType = 'an anniversary' -%} {%- endif -%} {%- if date == eventdate -%} {%- if row.Years == 0 -%} {%- capture titleAttr -%}Just Married!{%- endcapture -%} {%- capture celebrateText -%}<span class="celebrate"><i class="fa fa-glass-cheers"></i> <small>Congratulations!</small></span>{%- endcapture -%} {%- else -%} {%- capture celebrateText -%}<span class="celebrate"><i class="fa fa-glass-cheers"></i> <small>Happy {{ number }} Anniversary!</small></span>{%- endcapture -%} {%- endif -%} {%- assign number = '' -%} {%- endif -%} {%- else -%} {%- assign iconClass = 'fa fa-fw fa-user-tie' -%} {%- assign number = row.Years | AsString | NumberToOrdinal -%} {%- if row.Years == 0 -%} {%- capture titleAttr -%}New team member!{%- endcapture -%} {%- assign number = 'New!' -%} {%- else -%} {%- capture titleAttr -%}{{ number }} Workiversary{%- endcapture -%} {%- endif -%} {%- assign emailType = 'a congratulations' -%} {%- if date == eventdate -%} {%- if row.Years == 0 -%} {%- capture celebrateText -%}<span class="celebrate"><i class="far fa-thumbs-up"></i> <small>Welcome to the team!</small></span>{%- endcapture -%} {%- else -%} {%- capture celebrateText -%}<span class="celebrate"><i class="far fa-thumbs-up"></i> <small>Happy {{ number }} Workiversary!</small></span>{%- endcapture -%} {%- endif -%} {%- assign number = '' -%} {%- endif -%} {%- endif -%} <li class="list-group-item {{row.Gender}}" title="{{ titleAttr }}"> <a href="/person/{{ row.Id }}" title="View {{ row.NickName }}'s profile" class="hidden-md"> {%- if row.PhotoId != null -%} <img src="/GetImage.ashx?id={{row.PhotoId}}&maxwidth=24&maxheight=24&mode=pad&bgcolor=d4d2d0" alt="{{row.FullName}}" /> {%- else -%} <img src="/Assets/Images/person-no-photo-{{row.Gender}}.svg" alt="No Photo" class="no-photo" /> {%- endif -%} </a> <a href="/person/{{ row.Id }}" title="View {{ row.NickName }}'s profile">{{ row.FullName }}</a> <a href="/Communication?person={{ row.Id }}" title="Send {{ row.NickName }} {{ emailType }} email"> <small><i class="far fa-envelope margin-l-sm visible-lg-inline-block" style="opacity:.6"> </i></small> </a> <span class="date pull-right"> {% if number != '' %}<small><small>{{ number }}</small></small>{% endif %} <i class="{{ iconClass }}"></i> <small>{{ row.EventMonth }}/{{ row.EventDay }}</small> </span> {{ celebrateText }} </li> {%- endfor -%} </ul> </div> {%- endcache -%} CSS Styles There's also a little bit of CSS that needs to be added to your Rock theme (Admin Tools > CMS Configuration > Themes > Rock > CSS Overrides): .milestones { li { line-height: 1em; } a { display: inline-block; } img { width: 30px; height: 30px; margin-right: 3px; padding: 1px; border: 2px solid @link-color; border-radius: 50%; &.no-photo { opacity: .5; } } .date { line-height: 1.5em; i { width: 1em; text-align: center; } small { display: inline-block; width: 2.5em; text-align: center; } } .celebrate { display: block; margin-left: 38px; } .fa-heart { color: lighten(@brand-danger, 5%); } .fa-birthday-cake { color: #866eff; } .fa-thumbs-up { color: @brand-color; } .fa-glass-cheers { color: #eab043; } .fa-gift { color: #2b94fd; } .fa-user-tie { color: #888; } .male .fa-birthday-cake { color: #4797ff; } .female .fa-birthday-cake { color: #ba84ff; } .male .fa-user-tie { color: #3c5e9e; } .female .fa-user-tie { color: #8762a5; } } Bonus Font Awesome doesn't currently include the "party popper" emoji, so I created one myself and added it to our own custom icon font. You can see an example of the icon in the screenshot below. I attached the .svg file for my party popper icon to this recipe for anyone who's interested and knows how to add their own custom font to Rock. Use the Download File button to get it and feel free to use or modify the icon however you want. By default, this recipe uses the bullhorn icon () in the panel heading instead of the party popper. Follow Up Please don't hesitate to leave a comment below or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe. If you like this recipe, you might also want to check out my Staff Celebration Confetti recipe. Change Log 2023-04-12 - Added marital status filter for anniversaries and added a note about widowed staff members. 2023-02-23 - Handle Feb 29th events. On non-leap years, the event will be treated as if it falls on Feb 28th instead. 2022-09-02 - Initial Version Download File