March 12, 2007 at 2:48 am
Hello All,
Table: Employee
EmployeeId | EmployeeName | EmployeeAdd |
|
|
|
Table: EmployeeBill
BIllId | BillAmount | BillMonth | EmployeeId |
|
|
|
|
Question:
I need to write a SQL query (not using Loops and Cursors) to return/generates the 'latest three bills for each employee'
I tried to write the query using CTE but could not succeed.
Can this be achieved using CTE, if yes please help me write it. OR can we achieve this with single query statement?
Any reply would be much helpful.
Thanks and Regards,
Jagga
March 12, 2007 at 3:26 am
Try looking up ROW_NUMBER() OVER(PARTITION BY ... in BOL.
March 16, 2007 at 4:23 am
I come up with a solution (writing single SQL Statement) using CROSS APPLY Operator available on 2k5.
(The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.)
Thanks for replying.
March 16, 2007 at 4:58 am
I was thinking along the lines of:
SELECT *
FROM Employee E
JOIN (
SELECT ROW_NUMBER() OVER(PARTITION BY B.EmployeeId ORDER BY B.BillId DESC) AS Row
,B.BillId
,B.BillAmount
,B.BillMonth
,B.EmployeeId
FROM EmployeeBill AS B
) D ON E.EmployeeId = D.EmployeeId
WHERE D.Row < 4
March 19, 2007 at 11:38 pm
I tried the below one.
SELECT RS.*
FROM
( SELECT
DISTINCT EmployeeId
FROM
EmployeeBill ) AS EB
CROSS APPLY
(
SELECT TOP 3
E.EmployeeId,
E.EmployeeName,
E.EmployeeAdd,
BILL.BIllId ,
BILL.BillAmount ,
BILL.BillMonth
FROM
Employee E
INNER JOIN
EmployeeBill BILL
ON BILL.EmployeeId = E.EmployeeId
WHERE
E.EmployeeId = EB.EmployeeId
ORDER BY
BILL.BillMonth DESC ) AS RS
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply