Multiple Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Joins

Author

Topic

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-29 : 05:08:30

Hi Folks,

I am trying to create a Resource forecasting sheet, but while doing this I am facing lot of trouble. When I execute the code below it gives me duplicates row and shows total number of rows is 428757…Plz advice………I want the details of users shows current month data...Lot of users project are old in it but i want to show the current one....

SELECT US.FIRST_NAME + ' ' + US.LAST_NAME AS RESOURCE_NAME, US.DEPARTMENT_ID,
US.REPORTING_MANAGER_ID, US.SUPERVISOR_ID,
UJ.JOB_NUMBER, UJ.ASSIGNED_DATE, US.USER_ID,
JN.DESCRIPTION, JN.BILLABLE_FLAG, JN.FROM_DATE,
JN.TO_DATE, JN.TOTAL_HRS, TD.DATE_WORKED

FROM USERS US JOIN USER_JOBS UJ ON ( US.USER_ID = UJ.USER_ID)
JOIN JOB_NUMBERS JN ON (UJ.JOB_NUMBER = JN.JOB_NUMBER)
JOIN TIMESHEET_DETAILS TD ON (JN.JOB_NUMBER = TD.JOB_NUMBER)

WHERE TD.DATE_WORKED IN
(SELECT TD.DATE_WORKED FROM TIMESHEET_DETAILS TD
WHERE TD.DATE_WORKED >= '20080516' AND TD.DATE_WORKED < '20080716')

ORDER BY US.FIRST_NAME

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 05:23:58

show some sample data and provide output you want with some explanation?also it would be better if you can give how tables are related and if relation is many to 1 or 1:1?

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-29 : 11:23:33

I want output, in this way…..

department name--RESOURCE NAME--USER ID--Project NAME--Total hrs in a month

First of all Mr.Shaikh, how r u dng..sir I convert the data in small letters, to make it comfortable for you last time u ask me to do that…..Though I changed the code n tried to compile it but it doesn’t work…….anyway relationship are as follow
Departments: Department_id is a PK
Users: user_id is a PK
User_jobs: usjid is pk--user_id is FK--job_number is FK
timesheet_details: Timsheet_number is PK, job_number is FK
job_numbers: job_number is PK

Changed code

select departments.department_name,
users.first_name + ' ' + users.last_name as resource_name,
user_jobs.user_id, job_numbers.description as project_name,
count(distinct td.hours_worked)

from departments
join users on (users.user_id = user_jobs.user_id)
join user_jobs on (user_jobs.job_number = job_numbers.job_number)
join job_numbers on (user_jobs.job_number = job_numbers.job_number)
join timesheet_details on (job_numbers.job_number = timesheet_details .job_number)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 12:38:50

where's some sample data from tables? without it how can we determine how they are related?

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-29 : 14:06:02

You want me to give you the real data of a table......mean live case.....

Jason....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 14:07:44

quote:
Originally posted by gunjansr83

You want me to give you the real data of a table......mean live case.....

Jason....

nope. just some sample data to illustrate the relationship b/w tables. whether its 1:1 1:n,...

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-30 : 04:39:18

Here it is......
Boss if you wont mind can you explain me that 1:1 or many:1 funda how it is help full in joins...

Department

Department_ID Department_name
1 Admin
2 Software development
3 BPO
4 Creative

User_jobs

Userj_id User_id Job_number Assigned_date Leader_id
21 403 15220 2008 403
22 405 14680 2008 605
23 406 14690 2008 786
24 407 14700 2008 983

Users

User_id Employee_number Fname Lname Department_id
592 1806 Jai Arora 2
403 1506 Sumit Baghal 3
407 1498 Quasim Khan 4
406 1501 rajat chada 1

Timesheets

Timesheet_number User_id Bllable_hours
19508 22 .45
18088 23 9

Timesheet_details

Timesheet_number Date_worked Hours_worked Job_number
19508 2008-05-16 .45 15220
18088 2008-04-15 9 14680

Job_numbers

Job_number Description Total_hrs Active_flag
15220 Project charter .30 y
14680 Quality 2000 .50 n

Jason....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 05:13:10

[code]SELECT d.Department_name,
COALESECE(Fname+' ','')+COALESECE(Lname,'') AS ResourceName,
u.UserID,
jn.Description,
SUM(td.Billable_hours) AS TotalMonthhrs
FROM Users u
INNER JOIN User_jobs uj
ON uj.User_id=u.User_id
INNER JOIN Department d
ON d.Department_ID=u.Department_Id
INNER JOIN job_numbers jn
ON jn.job_number=uj.Job_number
INNER JOIN Timesheet_details td
ON td.Job_number=jn.job_number
GROUP BY d.Department_name,COALESECE(Fname+' ','')+COALESECE(Lname,''),u.UserID,DATEADD(mm,DATEDIFF(mm,0,td.Date_worked),0)[/code]

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-30 : 07:00:12

It is giving me an error
Server: Msg 195, Level 15, State 10, Line 2
'COALESECE' is not a recognized built-in function name.

I am using SQL server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 07:04:00

quote:
Originally posted by gunjansr83

It is giving me an error
Server: Msg 195, Level 15, State 10, Line 2
'COALESECE' is not a recognized built-in function name.

I am using SQL server 2000

its a typo. its COALESCE

SELECT d.Department_name,
COALESCE(Fname+' ','')+COALESCE(Lname,'') AS ResourceName,
u.UserID,
jn.Description,
SUM(td.Billable_hours) AS TotalMonthhrs
FROM Users u
INNER JOIN User_jobs uj
ON uj.User_id=u.User_id
INNER JOIN Department d
ON d.Department_ID=u.Department_Id
INNER JOIN  job_numbers jn
ON jn.job_number=uj.Job_number
INNER JOIN Timesheet_details td
ON td.Job_number=jn.job_number
GROUP BY d.Department_name,COALESCE(Fname+' ','')+COALESCE(Lname,''),u.UserID,DATEADD(mm,DATEDIFF(mm,0,td.Date_worked),0)

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-30 : 09:08:57

I got the following error out of it...

Server: Msg 207, Level 16, State 1, Line 15
Invalid column name 'Fname'.
Server: Msg 207, Level 16, State 1, Line 15
Invalid column name 'Fname'.
Server: Msg 207, Level 16, State 1, Line 15
Invalid column name 'Lname'.
Server: Msg 207, Level 16, State 1, Line 15
Invalid column name 'Lname'.
Server: Msg 207, Level 16, State 1, Line 15
Invalid column name 'UserID'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Fname'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Fname'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Lname'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Lname'.
Server: Msg 207, Level 16, State 1, Line 3
Invalid column name 'UserID'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name 'Billable_hours'.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-09-30 : 09:37:57

the columns you listed when giving sample data are not the same as in your original query. fname... should be first_name maybe??? just make the column names in the query match whatever is actually in your database

Em

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-30 : 10:10:01

yes sir you were correct, i applogise for this mess. but i want data on monthly basis......some explain me this "DATEADD(mm,DATEDIFF(mm,0,td.Date_worked),0)"

SELECT d.Department_name,
COALESCE(First_name+' ','')+COALESCE(Last_name,'') AS ResourceName,
jn.Description,
SUM(td.hours_worked) AS TotalMonthhrs
FROM Users u
INNER JOIN User_jobs uj
ON uj.User_id=u.User_id
INNER JOIN Departments d
ON d.Department_ID=u.Department_Id
INNER JOIN job_numbers jn
ON jn.job_number=uj.Job_number
INNER JOIN Timesheet_details td
ON td.Job_number=jn.job_number
GROUP BY d.Department_name,COALESCE(First_name+' ','')+COALESCE(Last_name,''), jn.Description, DATEADD(mm,DATEDIFF(mm,0,td.Date_worked),0)

Jason....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources