Need help on using CTE (Common Table Expression)

  • 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

     

  • Try looking up ROW_NUMBER() OVER(PARTITION BY ... in BOL.

     

  • 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.

  • 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

  •  

    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