FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

/* declare a table variable to hold all rows */
DECLARE @student_programs TABLE (
	[STPR_STUDENT] [nvarchar](11) NOT NULL, -- student identifier. This is an arbitrary ID so the student's actual ID is not used.
	[CURRENT_STATUS_DESC] [nvarchar](32) NOT NULL, -- status description; possible values: Active, Withdrawn, Potential, Graduated, Changed Mind
	[CURRENT_STATUS_DATE] [datetime] NOT NULL, -- date that status was updated. NOTE: timestamp is always 00:0:00.000, so cannot use this to guage chronological order if on the same date.
	[BEGIN_DATE] [datetime] NOT NULL, -- date the status began for the program
	[STPR_ACAD_PROGRAM] [nvarchar](20) NOT NULL, -- academic program assigned
	[ACPG_CIP] [nvarchar] (10) NOT NULL) -- CIP code for academic program
;
/* declare a second table variable to store ranked rows */
DECLARE @student_ranked_programs TABLE (
	[rank_no] INT NOT NULL , -- this is the rank assigned to student's status history rows.
	[STPR_STUDENT] [nvarchar](11) NOT NULL, -- student identifier. This is an arbitrary ID so the student's actual ID is not used.
	[CURRENT_STATUS_DESC] [nvarchar](32) NOT NULL, -- status description; possible values: Active, Withdrawn, Potential, Graduated, Changed Mind
	[CURRENT_STATUS_DATE] [datetime] NOT NULL, -- date that status was updated. NOTE: timestamp is always 00:0:00.000, so cannot use this to guage chronological order if on the same date.
	[BEGIN_DATE] [datetime] NOT NULL, -- date the status began for the program
	[STPR_ACAD_PROGRAM] [nvarchar](20) NOT NULL, -- academic program assigned
	[ACPG_CIP] [nvarchar] (10) NOT NULL) -- CIP code for academic program
;
/* real values but student ID has been scrambled to protect identifying data */
INSERT INTO @student_programs VALUES
('0510998','Potential','2023-10-27 00:00:00.000','2021-06-30 00:00:00.000','AUTO.BASC.CERT','47.0604'),
('0510998','Active','2023-10-27 00:00:00.000','2021-06-30 00:00:00.000','CARP.TDCJ.CE','46.0201'),
('0510998','Active','2023-10-27 00:00:00.000','2021-06-30 00:00:00.000','COSC.TDCJ.CE','11.0101'),
('0510998','Changed Mind','2021-06-30 00:00:00.000','2019-08-26 00:00:00.000','UNDECIDED','0'),
('0463630','Graduated','2021-12-15 00:00:00.000','2019-04-29 00:00:00.000','AA','24.0101'),
('0463630','Changed Mind','2019-04-29 00:00:00.000','2019-08-26 00:00:00.000','UNDECIDED','0'),
('0463043','Changed Mind','2019-07-25 00:00:00.000','2016-01-01 00:00:00.000','ADNR.ADN.AAS','51.3801'),
('0463043','Graduated','2019-07-25 00:00:00.000','2016-01-01 00:00:00.000','ADNR.ADN.ACC.AAS','51.3801'),
('0459827','Changed Mind','2020-06-02 00:00:00.000','2018-08-01 00:00:00.000','AA','24.0101'),
('0459827','Graduated','2020-07-13 00:00:00.000','2020-06-01 00:00:00.000','ADNR.ADN.ACC.AAS','51.3801'),
('0459827','Active','2020-07-13 00:00:00.000','2020-06-01 00:00:00.000','CE','0'),
('0459827','Graduated','2023-05-10 00:00:00.000','2022-08-22 00:00:00.000','NURS.ADN.BSN.BS','51.3801'),
('0459827','Changed Mind','2019-06-20 00:00:00.000','2019-08-26 00:00:00.000','UNDECIDED','0'),
('0459729','Withdrawn','2020-12-14 00:00:00.000','2020-01-21 00:00:00.000','ADNR.PA.ADN.AAS','51.3801'),
('0459729','Withdrawn','2019-12-09 00:00:00.000','2018-08-01 00:00:00.000','NURS.AA','24.0101'),
('0459701','Active','2020-05-05 00:00:00.000','2020-05-18 00:00:00.000','ADNR.VN.ADN.AAS','51.3801'),
('0459701','Active','2016-08-01 00:00:00.000','2016-08-01 00:00:00.000','VOCN.VOCNURS.CERT2','51.3901'),
('0000510','Active','2022-11-02 00:00:00.000','2023-01-17 00:00:00.000','DRAM.AA','24.0101'),
('0000510','Changed Mind','2022-11-02 00:00:00.000','2023-01-17 00:00:00.000','MUSI.VOIC.AAM','50.0901'),
('0000510','Changed Mind','2022-07-12 00:00:00.000','2022-05-31 00:00:00.000','UNDECIDED','0')
;
/* select to view the table */
SELECT * FROM @student_programs
ORDER BY
	STPR_STUDENT ,
	CURRENT_STATUS_DATE DESC,
	BEGIN_DATE DESC
;

STPR_STUDENT CURRENT_STATUS_DESC CURRENT_STATUS_DATE BEGIN_DATE STPR_ACAD_PROGRAM ACPG_CIP
------------ -------------------------------- ----------------------- ----------------------- -------------------- ----------
0000510 Active 2022-11-02 00:00:00.000 2023-01-17 00:00:00.000 DRAM.AA 24.0101
0000510 Changed Mind 2022-11-02 00:00:00.000 2023-01-17 00:00:00.000 MUSI.VOIC.AAM 50.0901
0000510 Changed Mind 2022-07-12 00:00:00.000 2022-05-31 00:00:00.000 UNDECIDED 0
0459701 Active 2020-05-05 00:00:00.000 2020-05-18 00:00:00.000 ADNR.VN.ADN.AAS 51.3801
0459701 Active 2016-08-01 00:00:00.000 2016-08-01 00:00:00.000 VOCN.VOCNURS.CERT2 51.3901
0459729 Withdrawn 2020-12-14 00:00:00.000 2020-01-21 00:00:00.000 ADNR.PA.ADN.AAS 51.3801
0459729 Withdrawn 2019-12-09 00:00:00.000 2018-08-01 00:00:00.000 NURS.AA 24.0101
0459827 Graduated 2023-05-10 00:00:00.000 2022-08-22 00:00:00.000 NURS.ADN.BSN.BS 51.3801
0459827 Graduated 2020-07-13 00:00:00.000 2020-06-01 00:00:00.000 ADNR.ADN.ACC.AAS 51.3801
0459827 Active 2020-07-13 00:00:00.000 2020-06-01 00:00:00.000 CE 0
0459827 Changed Mind 2020-06-02 00:00:00.000 2018-08-01 00:00:00.000 AA 24.0101
0459827 Changed Mind 2019-06-20 00:00:00.000 2019-08-26 00:00:00.000 UNDECIDED 0
0463043 Changed Mind 2019-07-25 00:00:00.000 2016-01-01 00:00:00.000 ADNR.ADN.AAS 51.3801
0463043 Graduated 2019-07-25 00:00:00.000 2016-01-01 00:00:00.000 ADNR.ADN.ACC.AAS 51.3801
0463630 Graduated 2021-12-15 00:00:00.000 2019-04-29 00:00:00.000 AA 24.0101
0463630 Changed Mind 2019-04-29 00:00:00.000 2019-08-26 00:00:00.000 UNDECIDED 0
0510998 Potential 2023-10-27 00:00:00.000 2021-06-30 00:00:00.000 AUTO.BASC.CERT 47.0604
0510998 Active 2023-10-27 00:00:00.000 2021-06-30 00:00:00.000 CARP.TDCJ.CE 46.0201
0510998 Active 2023-10-27 00:00:00.000 2021-06-30 00:00:00.000 COSC.TDCJ.CE 11.0101
0510998 Changed Mind 2021-06-30 00:00:00.000 2019-08-26 00:00:00.000 UNDECIDED 0