?
Solved

Getting the correct row from two different tables.

Posted on 2011-05-05
2
Medium Priority
?
286 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:huerita37
2 Comments
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35702702
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
 
LVL 8

Accepted Solution

by:
Ghunaima earned 2000 total points
ID: 35704423
Try this

 sql1.sql
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question