In a t-sql 2012, I have a table that looks like the following:
CREATE TABLE [dbo].[Enroll](
[enrollID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[calendardate] [int] NOT NULL,
[serviceType] varchar NOT NULL,
[active] [bit] NOT NULLNOT NULL),
[startDate] [smalldatetime] NOT NULL,
[endDate] [smalldatetime] NULL,
[endYear] [smallint] NOT NULL
The table is not really looked at by enrollID. This table usually accessed by personID, service type, startdate and enddate.
For each personID there can be lots of records.
For the most current record (selected by start date and end date), I want to look for records
where the service Type= P. If there is no service type=P, then I want to pick a record if service type=N.
Thus would you show me the t-sql 2012 for a way on how to accomplish this goal?