Summary	The view shows the crew members' birthday information within the current year and highlights the nearest upcoming dates with some colors.
Keywords	Birthday
Category	Crew List View

Description

The view shows the crew members' birthday information within the current year and highlights the nearest upcoming dates with some colors.

View Sample

Main Data Selection

All crew members.

SQL statement


select 
      p01.pin as PIN,
	  case when p01.sex = 'M' then 'Mr'
	       when p01.sex = 'F' then 'Mrs'
      end as Gender,
      p01.NAME as 'Full Name',
      p01.FIRSTNAME as 'First Name',
      p01.MIDDLENAME as 'Middle Name',
      p01.LASTNAME as 'Last Name',
      dateadd(yy,datediff(yy,p01.birthdate,getdate()),birthdate) 'Celebration Date',
      Month(p01.birthdate) as 'Month Nr' ,
      DateName(mm,Dateadd(yy,datediff(yy,p01.birthdate,getdate()),p01.birthdate))  Month,
      CONVERT(INT, DATENAME(d,DATEADD(YY,DATEDIFF(YY,P01.BIRTHDATE,GETDATE()),BIRTHDATE))) AS Date,
      DateName(wk,Dateadd(yy,datediff(yy,p01.birthdate,getdate()),birthdate))  Week,
      DateName(weekday,Dateadd(yy,datediff(yy,p01.birthdate,getdate()),birthdate))  Day,
      Datediff(yy,p01.birthdate,getdate()) Age,
      p01.Birthdate,
	  p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME as 'Country / Location',
	  P0T.TELENO as Email,
	  mob.Teleno as 'Mobile Phone',
      p01.RANK as 'Current Rank', 
	  p01.Client  numorgid,  
      p01.EmploymentStartDate,
      p01.EmploymentEnddate

from dbo.pw001p01 p01 
 LEFT JOIN PWCOUNTRY COUNTRY ON  P01.COUNTRYOFBIRTH  = COUNTRY.COUNTRYCODE
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
 LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM  PW001P0T P0T2
WHERE  (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
 LEFT JOIN PW001P0T mob ON ((P01.PIN = mob.PIN) AND (mob.TELETYPE = 3) AND (NOT mob.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM  PW001P0T mob2
WHERE  (mob.PIN = mob2.PIN) AND (mob2.TELETYPE = 3) AND ((mob2.TELEPRIORITY < mob.TELEPRIORITY) OR
((mob2.TELEPRIORITY = mob.TELEPRIORITY) AND (mob2.SEQUENCENO < mob.SEQUENCENO)))))

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
`PIN`	The PIN number from the Personal Details.
`Gender`	The Gender number from the Personal Details.
`Full Name`	The Full Name from the Personal Details.
`First Name`	The First Name from the Personal Details.
`Middle Name`	The Middle Name from the Personal Details.
`Last Name`	The Last Name from the Personal Details.
`Celebration Date`	The birthday Celebration Date in current year.
`Month Nr`	The number of celebration month in current year.
`Month`	The celebration month in current year.
`Date`	The celebration date in current year.
`Week`	The week in current year.
`Day`	The celebration day in current year.
`Age`	The age the person is turning in current year.
`Birthdate`	The birth date from the Personal Details.
`Country / Location`	The birth place from the Personal Details.
`Email`	The e-mail address from the Personal Details that has the highest priority.
`Mobile Phone`	The mobile phone from the Personal Details that has the highest priority.
`Current Rank`	The current rank from the Personal Details.
`numorgid`	The employment organization id from the Personal Details.
`EmploymentStartDate`	The employment start date from the Personal Details.
`EmploymentEnddate`	The employment end date from the Personal Details.

Start Page

N/A

Other

To highlight the passed/upcoming birth dates the following settings need to be configured.

Reports, Views and Similar

Birthday View