Populate column from separate sub-query

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

Populate column from separate sub-query

Author

Topic

iand109
Starting Member

14 Posts

Posted - 2009-10-16 : 06:01:58

Hi,
I've got a SQL database on SBS 2005.
I have one table called Staff (with staff records).
I have another table called Employment (with separate/multiple records of employment for each member of staff)

I need to populate a column in the Staff table from a separate query on the Employment table.
The query should work out the earliest start date (from the employment table) for each record in the staff table.
How could i do this?
Many thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-16 : 06:12:05

Plz explain complete structure (like ... CREATE TABLE abc ( field1 int, field2 varchar)) of your requried tables and expected results

iand109
Starting Member

14 Posts

Posted - 2009-10-16 : 06:29:11

Hi,
I don't need to create a new table - I just need to populate a date column within the existing STAFF table, from a separate query of multiple date fields in the Employment table, something along the lines of:

SELECT dbo.Employment.AppNo AS Expr1, min(dbo.Employment.EmpStart), dbo.STAFF.*
FROM dbo.Employment INNER JOIN
dbo.STAFF ON dbo.Employment.AppNo = dbo.STAFF.AppNo

Any help would be greatly appreciated,
thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-16 : 06:53:56

SELECT *, (SELECT MIN(mpStart) FROM dbo.Employment WHERE dbo.staff.AppNo = dbo.Employment.AppNo GROUP BY AppNo) As MINmpStart
FROM dbo.STAFF

iand109
Starting Member

14 Posts

Posted - 2009-10-16 : 07:16:26

Hi,
many thanks that is brilliant. Can I use that for the table itself, and if so how do I do that - or, do I create a View using that statement to generate the results?

iand109
Starting Member

14 Posts

Posted - 2009-10-16 : 07:32:32

I've already put it into an existing View so that's fantastic, thanks for your help. BUT, I also have one other problem with another part of this query, which is this. I have 3 fields in the Staff table: 'FORENAME', 'SURNAME' and 'KnownByName'. What would the syntax be to say IF KnownByName Is Not Null THEN KnownByName + ' ' + SURNAME AS Name ELSE FORENAME + ' ' + SURNAME AS Name END IF.?
I tried it with IF ... BEGIN ... END ... ELSE ... BEGIN ... END
But it came uup with an error multi-part identifier could not be bound.
Currently the full query is as follows:

SELECT DISTINCT
dbo.STAFF.FORENAME + ' ' + dbo.STAFF.SURNAME AS Name, Employment_1.EmpEnd, Employment_1.EmpStart, dbo.STAFF.Sex, dbo.STAFF.Manager,
dbo.STAFF.EmployeeNo, dbo.STAFF.Profile, dbo.STAFF.Qual1, dbo.STAFF.Qual2, dbo.STAFF.Qual3, dbo.STAFF.Qual4, dbo.STAFF.Qual5,
dbo.STAFF.Qual6, dbo.STAFF.Qual7, dbo.STAFF.Qual8, dbo.STAFF.Qual9, dbo.STAFF.Qual10, dbo.STAFF.SupressScores,
dbo.STAFF.LastScoreSheetPrinted, dbo.STAFF.UID, Employment_1.ID, Employment_1.Centre, Employment_1.Position, dbo.STAFF.Photograph,
(SELECT MIN(EmpStart) AS Expr1
FROM dbo.Employment
WHERE (dbo.STAFF.AppNo = AppNo)
GROUP BY AppNo) AS EarliestStartDate
FROM dbo.Employment AS Employment_1 LEFT OUTER JOIN
dbo.STAFF ON Employment_1.AppNo = dbo.STAFF.AppNo
WHERE (Employment_1.EmpEnd IS NULL) AND (Employment_1.EmpStart < GETDATE()) OR
(Employment_1.EmpEnd > GETDATE())

Any ideas?
Thanks again

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-16 : 15:17:14

chears

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-16 : 15:24:11

CASE WHEN KnownByName Is Not Null THEN KnownByName + ' ' + SURNAME ELSE FORENAME + ' ' + SURNAME END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources