SQL Code for Important Staff Dates
Some people have asked for the code used to generate Birthdays, Anniversaries, and Employment Anniversaries in the Report Grid for Query Arena module on our intranet. Below is the SQL code for each module we have added to the page.
Staff Birthday’s
select DAY(birth_date) as 'Birth Date','<a href="default.aspx?page=7&person='+CONVERT(VARCHAR(20),cp.person_id)+'">'+cp.nick_name+' '+cp.last_name+'</a>' AS 'Staff Member'
from core_person as CP
where MONTH(birth_date) = MONTH (GETDATE()) and staff_member = 1
order by DAY(birth_date), last_name asc
Staff Anniversaries
select DAY(anniversary_date) as 'Anniversary Date','<a href="default.aspx?page=7&person='+CONVERT(VARCHAR(20),cp.person_id)+'">'+cp.nick_name+' '+cp.last_name+'</a>' AS 'Staff Member'
from core_person as CP
where MONTH(anniversary_date) = MONTH (GETDATE()) and staff_member = 1
order by DAY(anniversary_date), last_name asc
Staff Employment Anniversaries
(this code works in Shelby V5 and assumes you are using V5 payroll for Employment Information)
Use ShelbyDB
SELECT
DateDiff(yy, HireDate, GetDate()) as "Years On Staff",
NA.Salutation + ' ' + NA.LastName AS Name
, HD.Descr AS Department
FROM Shelby.NaNames NA
INNER JOIN Shelby.PREmp PR ON NA.NameCounter = PR.NameCounter
LEFT OUTER JOIN Shelby.PRHomeDept HD ON HD.Counter = PR.HomeDeptCounter
LEFT OUTER JOIN Shelby.NAAddresses Adr ON Adr.AddressCounter = NA.MainAddress
LEFT OUTER JOIN Shelby.NAPhones PH ON PH.NameCounter = NA.NameCounter AND PH.PhoneCounter = 1
LEFT OUTER JOIN Shelby.NAPhones MO ON MO.NameCounter = NA.NameCounter AND MO.PhoneCounter = 5
WHERE CoNu = 1
AND Status = 'A'
AND Month(HireDate) = CASE WHEN Month(GetDate()) = 12 THEN 1 ELSE Month(GetDate()) END
ORDER BY "Years On Staff" Desc, Name
You just need to make sure you add the Report Grid for Query Module to your page, then copy and paste the SQL code into the module.
Wow. It's Quiet Here...
Be the first to start the conversation!