A quick and easy query to give you a list of the birthdays from the Employee Master Data application in SBO.
Copy and paste the lines below into the Query Generator tool.
/*———————–*/
/* Displays a list of all the employees in SBO with their birthdays (without the year) */
SELECT
lastname as ‘Last Name’
,firstname as ‘First Name’
,Case When birthdate is Null then ‘Not Entered’ Else
CASE len(CONVERT(varchar(2), datepart(mm, birthdate)))
WHEN 1 THEN ’0′ + CONVERT(varchar(1), datepart(mm, birthdate))
ELSE CONVERT(varchar(2), datepart(mm, birthdate))
END
+ ‘/’ +
CASE len(CONVERT(varchar(2), datepart(dd, birthdate)))
WHEN 1 THEN ’0′ + CONVERT(varchar(1), datepart(dd, birthdate))
ELSE CONVERT(varchar(2), datepart(dd, birthdate))
END
END AS Birthday
FROM OHEM
Order by birthday, lastname, firstname
/*———————–*/
If you wanted to access the Employee Master from this query you could add:
empid,
after the Select
and
FOR BROWSE
on the line following the Order By
If you would like to make this query into an alert, you could do all of the above and add the line
WHERE birthdate is not null
between the FROM OHEM and the Order By.
That would look like:
/* Displays a list of all the employees in SBO with their birthdays */
/*(without the year) */
SELECT
empid as ‘ID’
,lastname as ‘Last Name’
,firstname as ‘First Name’
,Case When birthdate is Null then ‘Not Entered’ Else
CASE len(CONVERT(varchar(2), datepart(mm, birthdate)))
WHEN 1 THEN ’0′ + CONVERT(varchar(1), datepart(mm, birthdate))
ELSE CONVERT(varchar(2), datepart(mm, birthdate))
END
+ ‘/’ +
CASE len(CONVERT(varchar(2), datepart(dd, birthdate)))
WHEN 1 THEN ’0′ + CONVERT(varchar(1), datepart(dd, birthdate))
ELSE CONVERT(varchar(2), datepart(dd, birthdate))
END
END AS Birthday
FROM OHEM
WHERE birthdate is not null
Order by birthday, lastname, firstname
FOR BROWSE
Thanks to Ed Monk of SBONotes.com
Subscribe to RSS
