Forum Discussion
Employee Milestones
I'm looking for format Employee Milestones this way. Script below gives me year of service including anniversary date. How do I make it so if employee anniversary (& keep it in this bucket for 30 days after anniversary date) fall in this bucket, names are added automatically? Greg_Deckler
SELECT *,
CASE
WHEN DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()), service_date) < GETDATE()
THEN DATEDIFF(Year, service_date, GETDATE()) + 1
ELSE DATEDIFF(Year, service_date, GETDATE())
END AS [ServiceAwardDateNumberOfYears],
CASE
WHEN DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()), service_date) < GETDATE()
THEN DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()) + 1, service_date)
ELSE DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()), service_date)
END AS [AnniversaryDate]
FROM [PERSON]
2 Replies
- v-piga-msft
Resident Rockstar
Hi diskovered ,
I have a little confused about your sceanrio.
Do you want to group your data by years?
Based on your code above, you may could try IF function to achieve that in Power BI.
If it is convenient, could you share some data sample and your desired output in Power BI so that we could help further on it?
Best Regards,
Cherry
- diskovered
Helper III
Hi Cherry,
Scenario:
Say today is an [employee's] 5th or 10th or 30 year anniversary. I want employee's name shown in their respective year of service area. Second condition is I don't want their names to stay there forever, I only want it there for 30 days after their anniversary date. eg: (Sorry, I can't conveniently share my pbix) Thanks