SQL select statement problem......help

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

Development Tools

ASP.NET

SQL select statement problem......help

Author

Topic

ravivr24
Starting Member

4 Posts

Posted - 2008-07-04 : 11:15:50

I have two tables..the first one is the Job Order table and the second is the employees table.

So what i want to do is that i want to select the following from these two tables:

tblEmployees:
1. employeeno
2. emplastname
3. empfirstname
4. empposition
5. empexpertise

tblJoborder:
1. count joborderno as 'workloads'

---and select where the joborderstatus <> 'OK' from the joborder table..and.. empposition = 'Welder' and empstatus = 'Active' which are located in the employee table. the hardest part where im having problems is the selecting of employee records which the employee is working on and display a count of the joborderno which are not (joborderstatus <>OK) which will display all the workloads of the employee which are <> 'ok' and if the employee has no workloads it does not display that employee so i want that even that employee would be displayed.....like workload = 0,....thanks guys and girls i hope you can solve this it would be a great help...:)

and by the way i am using this sql query:
SELECT e.EmployeeNo, e.empfirstname + ' '+ e.emplastname as 'Employee Name' ,e.empposition, e.expertise , COUNT(j.JobOrderNo) AS 'Workloads' FROM tblEmployees e INNER JOIN tblJobOrder j ON e.EmployeeNo = j.EmployeeNo WHERE (j.joborderstatus = 'Unit OK') and (e.EmpPosition = 'Welder') AND (e.EmpStatus = 'Active') OR (e.EmpPosition = 'Welder Asst') AND (e.EmpStatus = 'Active') GROUP BY e.EmployeeNo, e.empfirstname, e.emplastname,e.empposition, e.expertise

many thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-04 : 11:41:53

Hi,

Does this do what you need it to? I'm not 100% sure exactly what you need. If I've made any typo's in you tableName / columnName please correct.


SELECT
	e.[employeeNo]
	, e.[empFirstName] + ' ' + e.[empLastname] AS [Employee Name]
	, e.[empPosition]
	, e.[expertise]
	, COUNT(j.[jobOrderNo]) AS Workloads
FROM
	tblEmployees e
	LEFT JOIN tblJobOrder j ON j.[employeeNo] = e.[employeeNo]
WHERE
	e.[empStatus] = 'Active'
	AND e.[empPosition] IN ('Welder', 'Welder Asst')
	AND j.[jobOrderStatus] <> 'Unit OK'
GROUP BY
	e.[EmployeeNo]
	, e.[empfirstname] + ' ' + e.[emplastname]
	, e.[empposition]
	, e.[expertise]

-------------
Charlie

ravivr24
Starting Member

4 Posts

Posted - 2008-07-04 : 11:50:37

thank you charlie what you suggested worked but i also wanted to add that if that employee does not have a joborder still display but the cell would display 0 under the workload field of a technician who does not have a joborder...thanks again

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 04:24:54

Got it.

Here you go. Need to move the where condition on tblJobOrder into the LEFT JOIN.

Sample


IF object_Id('tblEmployees') IS NOT NULL DROP TABLE tblEmployees
IF object_Id('tblJobOrder') IS NOT NULL DROP TABLE tblJobOrder

CREATE TABLE tblEmployees (
	[employeeNo] INT
	, [empFirstName] VARCHAR(50)
	, [empLastName] VARCHAR(50)
	, [empPosition] VARCHAR(50)
	, [expertise] VARCHAR(255)
	, [empStatus] VARCHAR(50)
	)

CREATE TABLE tblJobOrder (
	[employeeNo] INT
	, [jobOrderNo] INT
	, [jobOrderStatus] VARCHAR(50)
	)

INSERT INTO tblEmployees (
	[employeeNo]
	, [empFirstName]
	, [empLastName]
	, [empPosition]
	, [expertise]
	, [empStatus]
	)
SELECT
	10
	, 'testA'
	, 'McTestA'
	, 'Welder'
	, 'Welding, making tea'
	, 'active'
UNION SELECT
	11
	, 'testerB'
	, 'Gereric Surname B'
	, 'Welder Asst'
	, 'Wating around, Standing still, Asking for a Long stand'
	, 'active'

INSERT INTO tblJobOrder (
	[employeeNo]
	, [jobOrderNo]
	, [jobOrderStatus]
	)
SELECT
	10
	, 45
	, 'Unit OK'
UNION SELECT
	10
	, 12
	, 'Awaiting Approval'

SELECT
	e.[employeeNo]
	, e.[empFirstName] + ' ' + e.[empLastname] AS [Employee Name]
	, e.[empPosition]
	, e.[expertise]
	, COUNT(j.[jobOrderNo]) AS Workloads
FROM
	tblEmployees e
	LEFT JOIN tblJobOrder j ON
		j.[employeeNo] = e.[employeeNo]
		AND j.[jobOrderStatus] <> 'Unit OK'
WHERE
	e.[empStatus] = 'Active'
	AND e.[empPosition] IN ('Welder', 'Welder Asst')
GROUP BY
	e.[EmployeeNo]
	, e.[empfirstname] + ' ' + e.[emplastname]
	, e.[empposition]
	, e.[expertise]

Produces output like...

employeeNo,Employee Name,empPosition,expertise,Workloads
10,testA McTestA,Welder,Welding, making tea,1
11,testerB Gereric Surname B,Welder Asst,Wating around, Standing still, Asking for a Long stand,0
-------------
Charlie

ravivr24
Starting Member

4 Posts

Posted - 2008-07-09 : 07:11:19

Thank You very much charlie the code that you have sent has really worked amazing...Round of applause i really want to learn about sql thanks to sqlteam it helped me...:)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-09 : 07:17:29

Thank you for the kind words :) I've learnt a lot from browsing the forums and it's really improved my sql skills.

Keep looking on the forums here, this is a great resource that has helped me many times -- often the question I want to ask has already been asked by someone else and as long as you can specify what you need there is always someone with the know-how and the inclination to help.

Good Fortune to you.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources