HWA2_P4_3.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 2
   October 23, 2008

   PART 4.3
   Create a stored procedure that will provide a table listing all billets
   in a given organization with current assigned personnel. Sort in
   ascending order by Name. Name the stored procedure PersonnelAssignment.
   ------------------------------------------------------------------------- */

USE DB9975
GO

CREATE PROCEDURE PersonnelAssignment AS
	SELECT
		PERSON_DESIGNATE.PersonId AS [Per#],
		dbo.PersonName(PERSON.Id) AS [Name],
		BILLET.Title              AS [Position],
		ORGANIZATION.Title        AS [Organization],
		StartDate                 AS [Start Date]

	FROM PERSON_DESIGNATE

	JOIN PERSON       ON PERSON_DESIGNATE.PersonId = PERSON.Id
	JOIN BILLET       ON PERSON_DESIGNATE.BilletId = BILLET.Id
	JOIN ORGANIZATION ON BILLET.OrganizationId     = ORGANIZATION.Id

	-- This assumes the start date and end date are not in the future.
	WHERE EndDate IS NULL

	ORDER BY Name
GO

EXECUTE PersonnelAssignment
GO