• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

Trying to display all the data

I am trying to show all the information needed in a query, but the database and data is weird.

Need:
Employee
Function
Total Hours per function
Total Units per function

Database:
-Employee
ID         Name
45        Bob
74        Dan

-Timecard
ID         employeeID      Date
23              45               4/1/09
56              74               4/1/09
98              45               4/2/09

-Timecard Detail
ID               TimecardID          Function          Total Hours
1                     23                   Email                      2
3                     23                   Phones                  4
4                     23                   Sweeping              2
7                     56                   Meeting                  3
9                     56                   Coding                   4

-Timecard Work
ID                  TimecardID        Function              Units
76                     23                  Email                      6
79                     23                  Desk                      8
88                     56                  Coding                   16

Notice that function is really another number, but put text in there so you can get an idea of what it is.

What I would like is:
EmployeeName                Function                Hours              Units
Bob                                  Email                        2                       6
Bob                                  Phones                    4                       0
Bob                                  Sweeping                2                       0
Bob                                  Desk                         0                      8
Dan                                  Meeting                     3                      0
Dan                                  Coding                      4                      16


I want to use both function from Timecard Detail and Timecard Work tables

Any ideas?
0
Meps
Asked:
Meps
  • 6
  • 5
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this is what you require
SELECT EM.Name, TD.Function, TD.[Total Hours], TW.Units
FROM [Timecard Detail] TD, [Timecard Work] TW, Timecard TC, Employee EM
WHERE TD.TimecardID = TW.TimecardID
AND TW.TimecardID = TC.ID
AND TC.employeeID = EM.ID

Open in new window

0
 
MepsAuthor Commented:
I also need the TimecardWork Functions.

This will only return the Detail functions.  Their can also be Work preformed with a different function from the one listed in the detail.
0
 
MepsAuthor Commented:
In the example listed above, Function: Desk is not a function in Detail, but in Work.  But the Timecard ID releates to the same Timecard.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this helps:
SELECT EM.Name, TD.Function, TD.[Total Hours], TW.Units
FROM Timecard TC inner join Employee EM on TC.employeeID = EM.ID
right outer join [Timecard Detail] TD on TC.ID = TW.TimecardID
right outer join [Timecard Work] TW on TC.ID = TW.TimecardID

Open in new window

0
 
MepsAuthor Commented:
This still doesn't allow me to show the Timecard Work Function.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
can you provide what is the result set of my query so that I can modify it
0
 
MepsAuthor Commented:
EmployeeName                Function                Hours              Units
Bob                                  Email                        2                       6
Bob                                  Phones                    4                       0
Bob                                  Sweeping                2                       0
Dan                                  Meeting                     3                      0
Dan                                  Coding                      4                      16
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I hope you should be having a Function Table which has all Functions stored in it.
Kindly confirm so that we can join with that table to return all the Functions.
0
 
MepsAuthor Commented:
Yea, I do.

WorkFunctions is the table name.
ID          Name
1           Email
2           Desk
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try this one out:

Test out both with last line commented and uncommented.
SELECT EM.Name, TD.Function, TD.[Total Hours], TW.Units
FROM WorkFunctions WF LEFT OUTER JOIN [Timecard Work] TW on WF.Name = TW.Function
LEFT OUTER JOIN [Timecard Detail] TD on WF.Name = TD.Function
LEFT OUTER JOIN Timecard TC on TC.ID = TD.TimecardID
LEFT OUTER JOIN Employee EM on TC.employeeID = EM.ID
-- WHERE TD.TimecardID = TW.TimecardID

Open in new window

0
 
MepsAuthor Commented:
You helped out a lot, and I had to modify your query, but it works.

Thank you very much.

I changed TD.Function in the select statment to WF.Name
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now