Find two values of one Column based on value of another column per ID

I have a sqlite db with one table that called Loan. This table with sample data is here: Loan Table at sqlfiddle.com

This table contains below Columns:

mainindex | brindx | YearMonth | EmpID | VarNo | Name | LastName | CodepayID | CodepaySH | Lval | Lint | Lrmn

Now, I need a query to show desired result, contain

[empid],[Codepayid],[Lval-1],[Lval-2],[Sum(Lint)],[Lrmn-1],[Lrmn-2],

With this Conditions:

[lVal-1] as Value of Lval Column of each Employee Correspond to their Lowest YearMonth
[lVal-2] as Value of Lval Column of each Employee Correspond to their Highest YearMonth
[Sum(Lint)] Sum of Lint Column for each Employee.
[Lrmn-1] as Value of Lrmn Column of each Emplyee Correspond to that Lowest YearMonth
[Lrmn-2] as Value of Lrmn Column of each Emplyee Correspond to that Highest YearMonth

For example:

select empid, Codepayid, Lval1, Lval2, Sum(Lint), Lrmn1, Lmrn2
   from Loan
   where CodepayID=649 and EmpID=12450400
   group by EmpID

Result:

EmpID	CodepayID	Lval1	Lval2	Sum(Lint)	Lrmn1	Lrmn2
12450400	649	405480	405485	270320	337900	202740

Answer

Use FIRST_VALUE() and SUM() window functions:

SELECT DISTINCT EmpID, CodepayID,
       FIRST_VALUE(Lval) OVER (PARTITION BY EmpID, CodepayID ORDER BY YearMonth) LVal1,
       FIRST_VALUE(Lval) OVER (PARTITION BY EmpID, CodepayID ORDER BY YearMonth DESC) LVal2,
       SUM(Lint) OVER (PARTITION BY EmpID, CodepayID) sum_Lint,
       FIRST_VALUE(Lrmn) OVER (PARTITION BY EmpID, CodepayID ORDER BY YearMonth) Lrmn1,
       FIRST_VALUE(Lrmn) OVER (PARTITION BY EmpID, CodepayID ORDER BY YearMonth DESC) Lrmn2
FROM loan

See the demo.