using getdate for returning course list running for 2 months
-
hi I am new to stored procedures in sql management studio 2012.
I have to write a stored procedure to return course list for the next 2 months. how do I do this?
my script as follows-
[create procedure pr_addtrainerqual(
@TrainerID char(10),@tsname varchar(30),@tfname varchar(30),@Title varchar(10),@taddress varchar(50),@temail varchar(40),@Coursecode varchar(4))
as
insert into tTrainer(TrainerID,tsname,tfname,Title,taddress,temail)
values
(@TrainerID,@tsname,@tfname,@Title,@taddress,@temail)
--begin part 2 assigning a trainer to a teaching course--
insert into tTrainerqualification(TrainerID,Coursecode)
values
(@TrainerID,@Coursecode)
--begin part 3 return course instance list for next 2 months--
select * from tCourseInstance
where CourseCode=@Coursecode
and tCourseInstance.CourseDate between getdate() and EOMONTH(getdate(),2)
exec pr_addtrainerqual 'TID1587','Morgan','Dexter','Lecturer','12 North Street Brighton BN2 2JH','dmorgan@redsoftcourses.com','R224']
- the first task was to add the new trainer to an existing system. then , next was to alter it to assign a course (existing) to this trainer. then third part is to return the list of courses for 2 months that this trainer will teach. I am having trouble writing the 3rd part..is it possible to find from my code above what I have done wrong?
Please help here. thank you
Edit when I run it it executes but when I highlight the select statement for part 3, it is displaying values outside the range of 2 months.
-
We know way too little about your tables and data, so we cannot answer your questions.
We need:
* The structure of all involved tables, posted as CREATE TABLE statements. You can omit irrelevant columns, but please do include all constraints.
* Some representative data, posted as INSERT statements. Post enough data to illustrate the normal case and any special cases, but do not post any more than that.
(Also, make sure to test the code in an empty database; ideally we should be able to just copy and paste without any extra work).
* Expected results, along with an explanation (if it's obvious to you, that doesn't mean it's obvious to us).
Also, please get rid of the silly "t" prefix in front of your table names. Next month you'll have to redesign a table and create a view under the old name to ensure backwards compatibility without having to touch and re-test all code - and then you'll have a view called tTrainer.
Also remove the pr_ prefix for the stored procedure - if it's just after the EXEC keyword, I already know it's a stored procedure even without that reminder.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Create Table tTrainer (
TrainerIDchar(10) primary key not null,
tsname varchar(30) not null,
tfname varchar(30),
Titlevarchar(10),
taddressvarchar(50),
temailvarchar(40));
Create Table tRoom(
roomnovarchar(4) primary key not null,
Buildingno int not null,
SetupID varchar(4) not null,
Foreign key(Buildingno) references tBuilding(Buildingno),
Foreign key(SetupID) references tSpecialist(SetupID)
);
Create Table tCourseInstance (
CourseCode varchar(4) not null,
CourseDate Datetime not null,
TrainerIDchar(10),
RoomNovarchar(4),
Primary key(CourseCode, CourseDate),
Foreign key(CourseCode) references tCourse(Code),
Foreign key(TrainerID) references tTrainer(TrainerID),
foreign key(Roomno) references tRoom(Roomno)
);
Create table tTrainerqualification (
TrainerID char(10) not null,
Coursecode varchar(4) not null,
Primary key(TrainerID, Coursecode),
Foreign key(TrainerID) references tTrainer(TrainerID),
Foreign key(CourseCode) references tCourse(Code));
below is the inserted data for reference
Insert into tTrainer values('TID1585','Davis','Terrence','Lecturer','7 Westley Fields Brighton BN4 2JH ','tdavis@redsoftcourses.com');
Insert into tTrainer values('TID1584','Shackley','Michael','Lecturer','126 the Highway Brighton BN2 4TH','mshackley@redsoftcourse.com');
Insert into tTrainer values('TID1583','Brien','Connan','Dean','25 Parbury Drive London EC2 8GY','cbrien@redsoftcourses.com');
Insert into tTrainer values('TID1582','Sawant','Sandeep','Leader1','71 Kimping Lane Epping EP89 JH','ssawant@redsoftcourses.com');
Insert into tTrainer values('TID1581','Moideen','Nadia','Leader2','21 Cranfield Islington London IS4 2DF','nmoideen@redsoftcourses.com');
Insert into tTrainer values('TID1580','Srekovksa','Olga','Trainer','53 Poppington Drive B25 1KH','osrekovska@redsoftcourses.com');
Insert into tTrainer values('TID1579','Petychaki','Danai','Leader3','15B Paddock Fields Falmer','dpetychaki@redsoftcourses.com');
insert into tRoom values('Rn20','100','HW20');
insert into tRoom values('Rn21','200','HW18');
insert into tRoom values('Rn23','300','SW20');
insert into tRoom values('Rn22','200','HW22');
insert into tRoom values('Rn25','200','HW23');
insert into tRoom values('Rn24','300','SW23');
insert into tRoom values('Rn26','200','SW19');
Insert into tCourseInstance values('R222','150620','TID1582','Rn20');
Insert into tCourseInstance values('R223','160110','TID1579','Rn21');
Insert into tCourseInstance values('R224','150616','TID1581','Rn22');
Insert into tCourseInstance values('R225','150503','TID1583','Rn23');
Insert into tCourseInstance values('R226','141104','TID1584','Rn24');
Insert into tCourseInstance values('R222','150503','TID1585','Rn25');
Insert into tCourseInstance values('R224','160503','TID1579','Rn26');
Insert into tCourseInstance values('R224','150420','TID1582','Rn26');
Insert into tCourseInstance values('R225','160412','TID1580','Rn20');
Insert into tCourseInstance values('R227','150507','TID1579','Rn26');
Insert into tCourseInstance values('R222','161001','TID1580','Rn25');
Insert into tCourseInstance values('R223','120316','TID1581','Rn21');
Insert into tCourseInstance values('R223','160609','TID1582','Rn21');
Insert into tTrainerqualification values('TID1582','R222');
Insert into tTrainerqualification values('TID1579','R223');
Insert into tTrainerqualification values('TID1583','R225');
Insert into tTrainerqualification values('TID1579','R227');
Insert into tTrainerqualification values('TID1584','R226');
Insert into tTrainerqualification values('TID1580','R222');
insert into tTrainerqualification values('TID1581','R223');
Insert into tTrainerqualification values('TID1582','R224');
Insert into tTrainerqualification values('TID1580','R223');
basically I have to create a single stored procedure that adds a new trainer to the existing system, assign that trainer to teach a particular course(already existent) and then to return a list of course instances running in the next 2 months
Ideally it should work like this :-
TID1500 ,'x',y',,'Lecturer''123 street',R222
The table should contain the information plus the months in which the course R222 runs.
I hope I have posted enough information. however, please info if more is required to make sense of this.
-
Your sample data does NOT contain any records that are valid for the selection between today and the upcoming two months. The EOMONTH function will return March 31st as date, so you want the courses between Jan 13th and March 31st (boundaries included).
If you execute the SELECT statement below you can check the selection on your live data. I've added a CASE statement to display if the date falls inside the given range. Probably this will also give you some insight on why records are or are not selected.
SELECT *
, CASE
WHEN CourseDate BETWEEN getdate() AND EOMONTH(getdate(), 2)
THEN 'true'
ELSE 'false'
END AS selection
, getdate() AS start_date
, EOMONTH(getdate(), 2) AS end_date
FROM tCourseInstance
--WHERE CourseDate = 'R224'
ORDER BY CourseDate
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Thanks for the additional information. Please repost the INSERT statements for the CourseInstance table, using an unambiguous date format: yyyymmdd. Now I have no idea how to interpret the dates you post.
Based on the sample data you posted and the sample store proc invocation at the end, what exactly should be returned? (Not a description, you already gave that, but the exact data)? Should it also add extra data in the CourseInstance table, or just return a list to the program that invokes the stored procedure?
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
the procedure should return the list of courses that the newly assigned trainer can teach.
I suppose it should look something like this
result window
I have attached a snippet.
-
Based on what logic is that row returned? The date listed is not within the next two months, and the course listed is not the course you include in the parameters to the stored procedure.
Also, again, please repost the INSERT statements using YYYYMMDD date format.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
hi,
thank you very much for this I had tested the wrong sample earlier but this helped me verify the date runs.
big Thumbs up !
-
nimmusuya (1/13/2016)
basically I have to create a single stored procedure that adds a new trainer to the existing system, assign that trainer to teach a particular course(already existent) and then to return a list of course instances running in the next 2 monthsBasically, that's the wrong way to do such things. Consider making 3 stored procedures, 1 for each task.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply