/// <summary> /// Function which will create an SQL string for getting the top 5 percent of oldest employeees. /// </summary> /// <returns></returns> private static string getEmployeesThatAreTheOldest() { string sqlString = @"select TOP 5 PERCENT Id,FirstName,LastName,DateJoined from UserAccount ORDER BY DATEDIFF(DAY, UserAccount.DateJoined, GETDATE()) DESC;"; return(SQLAdapter.getEmployeesThatHaveHigherthanAverageSalary(sqlString)); }
/// <summary> /// Function which will get the employees which earn above the average gold income. /// </summary> /// <returns>SQL string</returns> private static string getEmployeesThatAreAboveGoldAverage() { string sqlString = @"SELECT Id,FirstName,LastName,AVG(CAST(CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('P@SSW04D', Salary))AS INT)) AS SALARY from UserAccount where MemberShipClass ='GOLD' GROUP BY Salary,Id,FirstName,LastName having Salary > AVG(CAST(CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('P@SSW04D', Salary))AS INT)) ORDER BY SALARY DESC;"; return(SQLAdapter.getEmployeesThatHaveHigherthanAverageSalary(sqlString)); }