August 10, 2017 at 3:37 pm
I have the following table that contains can contain 0,1, or many records for each endyear when a query is excuted by personID.
The enrollmentID key is not used by any queries in my company. That column only exists since database tables requie an identity key.
CREATE TABLE [dbo].[Enrollment](
[enrollmentID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[calendarID] [int] NOT NULL,
[grade] [varchar](4) NULL,
[active] [bit] NOT NULL,
[startDate] [smalldatetime] NOT NULL,
[startStatus] [varchar](4) NULL,
[startComments] [varchar](250) NULL,
[endDate] [smalldatetime] NULL,
[endStatus] [varchar](4) NULL,
[endYear] [smallint] NOT NULL
)What I need to do is to allow is for a parameter called @endYear to be used and matched against the [endYear] column in the Enrollment table. The user will be able to select @endYear = 2018 or @endYear = 2017.
I need to select the most current enrollment record based upon endDate and endStatus = 202 or 205.
The problem is when @endYear=2017. I can not select records by personID if records exist where endyear =2018 and (there is not an endStatus = 202 OR 205).
Thus can you show me the t-sql 2012 on how to accomplish my goal?
August 11, 2017 at 3:02 am
The enrollmentID key is not used by any queries in my company. That column only exists since database tables requie an identity key.
This isn't true. Tables don't need to have a identity key (however, it's is often good practice). Something simple as this works:CREATE TABLE Client
(FirstName varchar(20),
Surname varchar(20),
DOB date);
INSERT INTO Client
VALUES ('Steve','Bloggs','19800101'),
('Jane','Smith','19840507'),
('George','Brown','19951119');
GO
SELECT *
FROM Client;
GO
DROP TABLE Client;
It does, however, appear that you are replicating the table. This does cause you to require a Primary key, but it's important to understand a table, by itself, does not require a key.
wendy elizabeth - Thursday, August 10, 2017 3:37 PMWhat I need to do is to allow is for a parameter called @endYear to be used and matched against the [endYear] column in the Enrollment table. The user will be able to select @endYear = 2018 or @endYear = 2017.
I need to select the most current enrollment record based upon endDate and endStatus = 202 or 205.
The problem is when @endYear=2017. I can not select records by personID if records exist where endyear =2018 and (there is not an endStatus = 202 OR 205).
Thus can you show me the t-sql 2012 on how to accomplish my goal?
I'm not sure what you mean here. It almost sounds like your describing you have conflicting parameters, but you want to return data (so you want data for personID 10 in yearend 2017, even though they have no data in 2017).
If you could supply sample data (in a format we can insert into your DDL you've provided), and what you expect to be able to return from it using specific parameters, that would help greatly.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply