|
|
Posted by heri on 09/26/20 11:45
Can you please assist me on how to get the 2nd record in case there are
3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get
the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.
How to GET the 2nd Row of Record in Case that There are 3 or more
records IN A SINGLE ROW ???
--------------------------------------------------------------------------------
This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.
Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...
SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,
(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND
@ToDate
ORDER BY startdate DESC) AS NEWBASIC,
******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ???
(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND
@ToDate
ORDER BY startdate ASC) AS OLDBASIC
FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @FromDate AND
@ToDate
ORDER BY E.LastName
Navigation:
[Reply to this message]
|