Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: aggregate in multiple table.

    Hello, i have a table called tblschedule that has field for resourceID, employeeID, and scheduleDate. And also i have another table called tblResource that has resourceID and ResourceName. The third table called tblEmployee has employeeID, employeeFirstName and employeeLastname. I want to get a report for each resourceName (not ResourceID) that per employee schedule (COUNT). I need the report has ResourceName field, employeeName field and count. How can i write a store procedure as it need join three table to get the count. Thank you very much!

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by yyu
    Hello, i have a table called tblschedule that has field for resourceID, employeeID, and scheduleDate. And also i have another table called tblResource that has resourceID and ResourceName. The third table called tblEmployee has employeeID, employeeFirstName and employeeLastname. I want to get a report for each resourceName (not ResourceID) that per employee schedule (COUNT). I need the report has ResourceName field, employeeName field and count. How can i write a store procedure as it need join three table to get the count. Thank you very much!
    assuming the tables are as follows,

    tblEmployee
    (
    EmployeeID Int
    EmployeeFirstName Varchar(50)
    EmployeeLastName Varchar(50)
    tblResource
    (
    ResourceID Int ,
    ResourceName Varchar(50)
    )

    tblSchedule
    (
    ResourceID Int ,
    EmployeeID Int ,
    ScheduleDate DateTime
    )

    Try this and let me know if u have much refined version of this....

    Select tE.EmployeeFirstName, tE.EmployeeLastName,tR.ResourceName,EmpCount.Sched ule
    From tblEmployee tE
    Inner Join tblSchedule tSE
    On tE.EmployeeID = tSE.EmployeeID
    Inner join tblResource tR
    On tR.ResourceID = tSE.ResourceID
    Inner Join (Select EmployeeID,Count(EmployeeID) As Schedule From tblSchedule Group By EmployeeID) EmpCount
    On tE.EmployeeId = EmpCount.EmployeeID
    Cheers....

    baburajv

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    Thank you very much! But i have one more question, i would like to group the resourceID too. The result like:

    Laptop John 12
    Laptop smith 2

    projector Rick 2

    Cellphone Kim 2

    Is it possible? Many thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •