Getting the correct row from two different tables.

I need to get certain fields from either the Personnel or PersonnelHistory table depending on the 'ServiceDate'.  I use the 'service date' and compare it to the 'RequiredTo' field in both tables.  The 'RequiredTo' field is like an 'EffectiveDate'.

If there is a change in the Personnel table then a snap shot of that record before the change is saved in the PersonnelHistory table.  

How do I find the correct row?  

create FUNCTION [dbo].[build_personnel_sign_with_date]
(
	@agency_id 			INTEGER,
	@personnel_id	 	INTEGER,
	@sign_date			DATE
)
RETURNS VARCHAR(500)
AS
BEGIN

	DECLARE	@last_name VARCHAR(100),
		@first_name VARCHAR(100),
		@license_type VARCHAR(100),
		@license_number VARCHAR(100),
		@temp VARCHAR(200),
		@signature VARCHAR(500),
		@EffectiveDate VARCHAR(500),
		@ID INTEGER

	SET	@last_name	= NULL
	SET	@first_name	= NULL
	SET @ID = NULL
	SET @EffectiveDate = null
	
	SELECT TOP 1 @ID = ID
	FROM PersonnelHistory
	WHERE ID IN
		(SELECT MAX(ID) as MaxID
		FROM PersonnelHistory
		WHERE datediff(d, @sign_date, convert(datetime, RequiredTo, 101)) >= 0 
		AND PersonnelID = @personnel_id
		GROUP BY RequiredTo)
	
	IF @ID IS NOT NULL
	BEGIN
		SELECT @last_name	= [LastName], 
			@first_name	= [FirstName], 
			@license_type	= [LicenseType], 
			@license_number	= [LicenseNumber]
		FROM 	[PersonnelHistory]
		WHERE	[ID] = @ID
	END
	ELSE
	BEGIN		
		SELECT	@last_name	= [LastName], 
			@first_name	= [FirstName], 
			@license_type	= [LicenseType], 
			@license_number	= [LicenseNumber],
			@EffectiveDate = [requiredTo]
		FROM 	[Personnel]
		WHERE	[ID] = @personnel_id
	END
	
	IF @EffectiveDate > @sign_date
	BEGIN
		SELECT @last_name	= [LastName], 
			@first_name	= [FirstName], 
			@license_type	= [LicenseType], 
			@license_number	= [LicenseNumber]
		FROM 	[PersonnelHistory]
		WHERE ID = (SELECT MAX(ID) FROM PersonnelHistory ph WHERE ph.ID = ID)
		AND PersonnelID = @personnel_id	
	END
	 
	IF @last_name IS NULL OR @first_name IS NULL
	BEGIN
		SET @signature = NULL
	END
	ELSE
	BEGIN
		SET @signature = UPPER(LTRIM(RTRIM(ISNULL(@last_name, '')))) + ', ' + LTRIM(RTRIM(ISNULL(@first_name, '')))
		SET @temp = LTRIM(RTRIM(ISNULL(@license_type, ''))) + ' ' + LTRIM(RTRIM(ISNULL(@license_number, '')))
		IF LEN(@temp) > 0 SET @signature = @signature + ', ' + @temp
	END

	RETURN @signature

END

Open in new window

huerita37Asked:
Who is Participating?
 
GhunaimaCommented:
Try this

 sql1.sql
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Hi,
It's not clear what you call the "correct" row. I would assume it would be the row in Personnel, since the rows in PersonnelHistory are just audit rows, and it is assumed that the value in the main table is the correct one. Can you clarify what it is you want to do?

thx,

Philippe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.