SQL query for salary in emp table

You’ll have to get the percentage of the year worked and multiply it by the salary. If the joined_date is before January of that year, use ‘1.00’.

Since you did not specify a SQL type, I chose MSSQL as I’m most familiar with it.

Here’s an example using MSSQL:

DECLARE @yearToFindSalaryFor INT = 2018
DECLARE @dateEndOfSalaryYear DATE = ('01/01/' + 
                                     CONVERT(VARCHAR(5), (@yearToFindSalaryFor + 1)))
DECLARE @dateStartOfSalaryYear DATE = ('01/01/' + 
                                     CONVERT(VARCHAR(5), (@yearToFindSalaryFor)))

SELECT E1.YEARS_WORKED_FOR_YEAR, 
E2.sal * E1.YEARS_WORKED_FOR_YEAR AS SALARY_FOR_YEAR, E1.eid
FROM 
(
   SELECT 
   CASE WHEN joining_date > @dateEndOfSalaryYear
   THEN 0
   WHEN joining_date < @dateStartOfSalaryYear THEN 1
   -- cast one of the numbers as decimal, otherwise MSSQL will spit out an int value (1 or 0)
   -- since DATEDIFF will give INT value not decimal, we have to get 'days' and divide by 365
   ELSE DATEDIFF(day, joining_date, @dateEndOfSalaryYear)
       / CAST(365 AS DECIMAL(4, 0))
   END
   AS YEARS_WORKED_FOR_YEAR, eid FROM Employees
  ) AS E1
    JOIN Employees E2 ON E1.eid = E2.eid

Fiddle example: http://sqlfiddle.com/#!18/26485/34