CTE and Self Join with NULL Values

Question

Post reply

CTE and Self Join with NULL Values

Jacob Pressures

SSCertifiable

Points: 5804
More actions
May 6, 2010 at 10:17 am

#386866

I need to build a table to show you the problem. But i can't find the code to do so.
I need to provide you with a table with similar data.
Thanks!
I found the article. Its http://www.sqlservercentral.com/articles/Best+Practices/61537/
I'll be back after building my table.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 1

Here is the data that i need help with.

E stands for Enrollments/Re-Enrollments

W stands for Withdrawals

This is a school with students enrolled. What I'm looking for are the students that WITHDREW and then RE-ENROLLED.

The Withdrawals and Re-enrollments should appear in the same record per W/Re-E cycle. The Withdrawal should be listed first then the subsequent Re-enrollment.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#Enrollment','U') IS NOT NULL DROP TABLE #Enrollment

--===== Create the test table with

CREATE TABLE #Enrollment (

EnrollmentID INT NOT NULL,

StudentID INT NOT NULL,

AcadSessionID INT NOT NULL,

StartDate DATETIME NOT NULL,

EndTime DATETIME NULL,

ActionClass CHAR(1) NOT NULL,

)

INSERT INTO #Enrollment (EnrollmentID, StudentID, AcadSessionID, StartDate, EndTime, ActionClass )

SELECT '46','64','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '47','65','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '49','67','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '50','68','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '51','69','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '52','70','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '53','71','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '54','72','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '55','73','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '56','74','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '57','75','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '58','76','4','Aug 10 2009 12:00AM','Sep 17 2009 11:59PM','E' UNION ALL

SELECT '60','78','4','Aug 10 2009 12:00AM','Feb 26 2010 11:59PM','E' UNION ALL

SELECT '61','79','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '62','80','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '63','81','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '64','82','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '65','83','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '67','85','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '68','86','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '69','87','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '71','89','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '72','90','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '73','91','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '74','92','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '75','93','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '76','94','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '77','95','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '78','96','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '79','97','4','Aug 10 2009 12:00AM','Oct 26 2009 11:59PM','E' UNION ALL

SELECT '80','98','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '81','99','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '82','100','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '83','101','4','Aug 10 2009 12:00AM','Jan 6 2010 11:59PM','E' UNION ALL

SELECT '84','102','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '85','103','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '86','104','4','Aug 10 2009 12:00AM','Jan 5 2010 11:59PM','E' UNION ALL

SELECT '87','105','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '88','106','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '89','107','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '90','108','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '92','110','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '93','111','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '94','112','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '95','113','4','Aug 10 2009 12:00AM','Oct 1 2009 11:59PM','E' UNION ALL

SELECT '96','114','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '97','115','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '100','118','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '101','119','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '102','120','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '103','121','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '104','122','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '105','123','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '106','124','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '107','125','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '108','126','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '109','127','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '110','128','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '111','129','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '112','130','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '113','131','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '114','132','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '115','133','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '116','134','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '117','135','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '118','136','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '119','137','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '121','139','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '122','140','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '123','141','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '124','142','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '125','143','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '126','144','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '127','145','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '129','147','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '130','148','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '131','149','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '132','150','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '134','152','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '136','154','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '137','155','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '138','156','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '139','157','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '140','158','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '141','159','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '142','160','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '143','161','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '144','162','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '145','163','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '146','164','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '147','165','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '148','166','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '149','167','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '150','168','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '151','169','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '152','170','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '153','171','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '154','172','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '155','173','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '156','174','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '158','176','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '159','577','4','Aug 10 2009 12:00AM','Nov 15 2009 11:59PM','E' UNION ALL

SELECT '160','178','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '161','179','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '162','180','4','Aug 10 2009 12:00AM','Nov 30 2009 11:59PM','E' UNION ALL

SELECT '163','181','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '164','182','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '165','183','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '166','184','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '167','185','4','Aug 10 2009 12:00AM','Dec 8 2009 11:59PM','E' UNION ALL

SELECT '168','186','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '169','187','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '170','188','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '171','189','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '172','190','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '173','191','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '174','192','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '175','193','4','Aug 10 2009 12:00AM','Oct 22 2009 11:59PM','E' UNION ALL

SELECT '176','194','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '577','195','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '178','196','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '179','197','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '181','199','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '183','201','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '184','202','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '186','204','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '187','205','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '188','206','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '189','207','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '191','209','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '192','210','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '193','211','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '194','212','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '195','213','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '196','214','4','Aug 10 2009 12:00AM','Apr 26 2010 11:59PM','E' UNION ALL

SELECT '197','215','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '198','216','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '199','217','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '200','218','4','Aug 10 2009 12:00AM','Aug 10 2009 11:59PM','E' UNION ALL

SELECT '201','219','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '202','220','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '203','221','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '205','223','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '206','224','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '207','225','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '208','226','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '209','227','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '210','228','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '211','229','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '212','230','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '213','231','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '214','232','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '215','233','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '216','234','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '217','235','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '218','236','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '219','237','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '220','238','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '221','239','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '222','240','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '223','241','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '224','242','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '225','243','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '226','244','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '227','245','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '228','246','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '229','247','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '230','248','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '231','249','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '232','250','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '233','251','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '234','252','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '235','253','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '236','254','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '237','255','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '238','256','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '239','257','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '240','258','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '241','259','4','Aug 10 2009 12:00AM','Jan 6 2010 11:59PM','E' UNION ALL

SELECT '242','260','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '243','261','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '244','262','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '245','263','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '246','264','4','Aug 10 2009 12:00AM','Aug 27 2009 11:59PM','E' UNION ALL

SELECT '248','266','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '249','267','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '250','268','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '251','269','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '252','270','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '254','272','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '255','273','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '256','274','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '257','275','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '258','276','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '259','277','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '260','278','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '261','279','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '262','280','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '263','281','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '264','282','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '265','283','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '266','284','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '267','285','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '268','286','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '269','287','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '270','288','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '271','289','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '272','290','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '273','291','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '274','292','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '275','293','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '276','294','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '277','295','4','Aug 10 2009 12:00AM','Jan 29 2010 11:59PM','E' UNION ALL

SELECT '278','296','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '279','297','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '280','298','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '281','299','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '282','300','4','Aug 10 2009 12:00AM','Aug 12 2009 11:59PM','E' UNION ALL

SELECT '283','301','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '284','302','4','Aug 10 2009 12:00AM','Aug 10 2009 11:59PM','E' UNION ALL

SELECT '285','303','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '286','304','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '287','305','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '288','306','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '289','307','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '290','308','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '291','309','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '292','310','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '293','311','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '294','312','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '295','313','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '296','314','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '297','315','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '298','316','4','Aug 10 2009 12:00AM','Aug 10 2009 11:59PM','E' UNION ALL

SELECT '299','317','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '300','318','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '301','319','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '302','320','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '303','321','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '304','322','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '305','323','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '306','324','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '307','325','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '308','326','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '309','327','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '310','328','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '311','329','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '312','330','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '313','331','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '314','332','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '315','333','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '316','334','4','Aug 10 2009 12:00AM','Jan 11 2010 11:59PM','E' UNION ALL

SELECT '317','335','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '318','336','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '319','337','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '320','338','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '321','339','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '322','340','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '323','341','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '324','342','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '325','343','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '326','344','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '327','345','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '328','346','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '329','347','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '330','348','4','Aug 10 2009 12:00AM','Jan 6 2010 11:59PM','E' UNION ALL

SELECT '331','349','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '332','350','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '333','351','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '334','352','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '335','353','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '336','354','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '337','355','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '338','356','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '339','357','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '340','358','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '341','359','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '342','360','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '343','361','4','Aug 10 2009 12:00AM','Oct 15 2009 11:59PM','E' UNION ALL

SELECT '344','362','4','Aug 10 2009 12:00AM','Sep 2 2009 11:59PM','E' UNION ALL

SELECT '345','363','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '346','364','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '347','365','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '348','366','4','Aug 10 2009 12:00AM','Oct 13 2009 11:59PM','E' UNION ALL

SELECT '349','367','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '350','368','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '351','369','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '352','370','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '353','371','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '354','372','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '355','373','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '356','374','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '357','375','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '358','376','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '359','377','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '360','378','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '361','379','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '362','380','4','Aug 10 2009 12:00AM','Aug 12 2009 11:59PM','E' UNION ALL

SELECT '363','381','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '364','382','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '365','383','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '366','384','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '367','385','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '368','386','4','Aug 10 2009 12:00AM','Mar 23 2010 11:59PM','E' UNION ALL

SELECT '369','387','4','Aug 10 2009 12:00AM','Apr 13 2010 11:59PM','E' UNION ALL

SELECT '370','388','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '371','389','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '372','390','4','Aug 10 2009 12:00AM','Mar 31 2010 11:59PM','E' UNION ALL

SELECT '373','391','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '374','392','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '375','393','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '376','394','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '377','395','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '378','396','4','Aug 10 2009 12:00AM','Sep 27 2009 11:59PM','E' UNION ALL

SELECT '379','397','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '380','398','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '381','399','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '382','400','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '383','401','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '384','402','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '385','403','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '386','404','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '387','405','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '388','406','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '389','407','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '390','408','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '391','409','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '392','410','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '393','411','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '394','412','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '395','413','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '396','414','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '397','415','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '398','416','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '399','417','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '400','418','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '401','419','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '402','420','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '403','421','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '404','422','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '405','423','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '406','424','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '407','425','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '408','426','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '410','428','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '411','429','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '412','430','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '413','431','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '414','432','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '415','433','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '416','434','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '417','435','4','Aug 10 2009 12:00AM','Sep 27 2009 11:59PM','E' UNION ALL

SELECT '418','436','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '419','437','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '420','438','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '421','439','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '422','440','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '423','441','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '424','442','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '425','443','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '427','445','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '428','446','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '430','448','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '431','449','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '432','450','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '433','451','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '434','452','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '435','453','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '436','454','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '437','455','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '441','459','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '442','460','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '443','461','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '444','462','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '445','463','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '446','464','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '447','465','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '448','466','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '449','467','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '450','468','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '451','469','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '453','471','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '454','472','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '455','473','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '456','474','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '457','475','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '458','476','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '459','477','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '460','478','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '461','479','4','Aug 10 2009 12:00AM','Aug 10 2009 11:59PM','E' UNION ALL

SELECT '462','480','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '463','481','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '464','482','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '465','483','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '467','485','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '468','486','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '469','487','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '470','488','4','Aug 10 2009 12:00AM','Nov 3 2009 11:59PM','E' UNION ALL

SELECT '471','489','4','Aug 10 2009 12:00AM','Aug 10 2009 11:59PM','E' UNION ALL

SELECT '472','490','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '473','491','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '474','492','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '475','493','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '477','495','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '479','497','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '480','498','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '481','499','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '482','500','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '483','501','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '484','502','4','Aug 10 2009 12:00AM','Nov 23 2009 11:59PM','E' UNION ALL

SELECT '485','503','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '486','504','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '487','505','4','Aug 10 2009 12:00AM','Sep 30 2009 11:59PM','E' UNION ALL

SELECT '488','506','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '489','507','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '490','508','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '491','509','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '492','510','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '493','511','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '200','512','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '495','513','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '496','514','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '497','515','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '498','516','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '499','517','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '500','518','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '501','519','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '502','520','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '503','521','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '504','522','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '505','523','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '506','524','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '507','525','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '508','526','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '510','528','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '511','529','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '512','530','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '513','531','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '514','532','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '515','533','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '516','534','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '517','535','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '518','536','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '519','537','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '520','538','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '521','539','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '522','540','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '523','541','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '524','542','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '525','543','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '526','544','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '527','545','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '528','546','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '529','547','4','Aug 10 2009 12:00AM','Sep 30 2009 11:59PM','E' UNION ALL

SELECT '530','548','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '531','549','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '532','550','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '534','552','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '536','554','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '537','555','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '538','556','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '539','557','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '540','558','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '541','559','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '542','560','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '543','561','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '544','562','4','Aug 10 2009 12:00AM','Apr 5 2010 11:59PM','E' UNION ALL

SELECT '545','563','4','Aug 10 2009 12:00AM','Mar 22 2010 11:59PM','E' UNION ALL

SELECT '546','564','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '547','565','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '548','566','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '549','567','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '550','568','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '551','569','4','Aug 10 2009 12:00AM','Aug 23 2009 11:59PM','E' UNION ALL

SELECT '553','571','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '554','572','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '555','573','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '556','574','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '557','575','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '558','576','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '559','577','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '560','578','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '562','580','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '563','581','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '564','582','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '565','583','4','Aug 10 2009 12:00AM','Nov 4 2009 11:59PM','E' UNION ALL

SELECT '566','584','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '567','585','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '568','586','4','Aug 10 2009 12:00AM','Feb 25 2010 11:59PM','E' UNION ALL

SELECT '569','587','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '570','588','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '571','589','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '572','590','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '573','591','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '575','593','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '577','595','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '578','596','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '579','597','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '580','598','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '581','599','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '582','600','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '584','602','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '585','603','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '586','604','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '587','605','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '588','606','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '590','608','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '591','609','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '592','610','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '593','611','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '594','612','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '595','613','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '596','614','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '598','616','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '599','617','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '600','618','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '602','620','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '604','622','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '605','623','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '606','624','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '607','625','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '608','626','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '609','627','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '610','628','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '611','629','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '612','630','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '614','632','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '615','633','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '617','635','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '618','636','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '621','639','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '622','640','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '623','641','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '625','643','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '626','644','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '627','645','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '629','647','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '630','648','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '631','649','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '632','650','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '634','652','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '635','653','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '636','654','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '637','655','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9377','10793','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9378','10794','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9399','10838','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9405','10883','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9419','10899','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9614','11173','4','Aug 10 2009 12:00AM','Oct 11 2009 11:59PM','E' UNION ALL

SELECT '9632','11195','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9707','11330','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9709','11332','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9826','11459','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9832','11465','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9839','11472','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9899','11537','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9910','11555','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9945','11596','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '9980','11633','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10018','11673','4','Aug 10 2009 12:00AM','Mar 26 2010 11:59PM','E' UNION ALL

SELECT '10048','11706','4','Aug 10 2009 12:00AM','Jan 7 2010 11:59PM','E' UNION ALL

SELECT '10071','11741','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10086','11756','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10152','479','4','Aug 11 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10153','489','4','Aug 11 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10154','218','4','Aug 11 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10155','316','4','Aug 11 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10156','302','4','Aug 11 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10180','11872','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10195','11889','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10201','11896','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10242','11944','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10257','11958','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10275','15000','4','Aug 10 2009 12:00AM','Aug 18 2009 11:59PM','E' UNION ALL

SELECT '10278','11990','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10330','2036','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10387','12090','4','Aug 10 2009 12:00AM','Oct 1 2009 11:59PM','E' UNION ALL

SELECT '10400','12104','4','Aug 10 2009 12:00AM','Apr 9 2010 11:59PM','E' UNION ALL

SELECT '10415','12136','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10424','12153','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10564','12389','4','Aug 10 2009 12:00AM','Oct 1 2009 11:59PM','E' UNION ALL

SELECT '10613','12496','4','Aug 10 2009 12:00AM','Aug 30 2009 11:59PM','E' UNION ALL

SELECT '10626','12518','4','Aug 10 2009 12:00AM','Jan 5 2010 11:59PM','E' UNION ALL

SELECT '10681','12670','4','Aug 11 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10751','77','4','Aug 11 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10753','638','4','Aug 11 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10754','203','4','Aug 11 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10755','551','4','Aug 11 2009 12:00AM','Aug 11 2009 11:59PM','E' UNION ALL

SELECT '10756','198','4','Aug 11 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10763','66','4','Aug 11 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10765','12916','4','Aug 10 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10772','496','4','Aug 12 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10796','12938','4','Aug 12 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10805','13000','4','Aug 12 2009 12:00AM','Jan 22 2010 11:59PM','E' UNION ALL

SELECT '10813','12986','4','Aug 12 2009 12:00AM','Mar 1 2010 11:59PM','E' UNION ALL

SELECT '10835','551','4','Aug 12 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10867','4052','4','Aug 13 2009 12:00AM',NULL,'E' UNION ALL

SELECT '10884','380','4','Aug 13 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10886','300','4','Aug 13 2009 12:00AM',NULL,'W' UNION ALL

SELECT '10899','13360','4','Aug 13 2009 12:00AM','Jan 11 2010 11:59PM','E' UNION ALL

SELECT '10917','621','4','Aug 13 2009 12:00AM','Jan 5 2010 11:59PM','E' UNION ALL

SELECT '10983','200','4','Aug 11 2009 12:00AM','Sep 3 2009 11:59PM','E' UNION ALL

SELECT '11012','13672','4','Aug 17 2009 12:00AM','Sep 10 2009 11:59PM','E' UNION ALL

SELECT '11015','13679','4','Aug 17 2009 12:00AM','Sep 13 2009 11:59PM','E' UNION ALL

SELECT '11075','13891','4','Aug 18 2009 12:00AM','Sep 13 2009 11:59PM','E' UNION ALL

SELECT '11133','15000','4','Aug 19 2009 12:00AM','Sep 20 2009 11:59PM','W' UNION ALL

SELECT '11136','138','4','Aug 19 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11166','14654','4','Aug 21 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11179','116','4','Aug 24 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11187','888','4','Aug 24 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11230','264','4','Aug 28 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11265','12496','4','Aug 31 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11288','12055','4','Sep 1 2009 12:00AM','Sep 7 2009 11:59PM','E' UNION ALL

SELECT '11317','16500','4','Sep 4 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11325','200','4','Sep 4 2009 12:00AM','Oct 28 2009 11:59PM','W' UNION ALL

SELECT '11393','13672','4','Sep 11 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11395','16782','4','Sep 11 2009 12:00AM','Nov 23 2009 11:59PM','E' UNION ALL

SELECT '11419','13679','4','Sep 14 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11420','13891','4','Sep 14 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11458','5526','4','Sep 15 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11482','76','4','Sep 18 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11497','15000','4','Sep 21 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11499','18014','4','Sep 21 2009 12:00AM','Mar 24 2010 11:59PM','E' UNION ALL

SELECT '11532','362','4','Sep 3 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11565','396','4','Sep 28 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11566','435','4','Sep 28 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11579','19008','4','Sep 28 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11604','505','4','Oct 1 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11610','12389','4','Oct 2 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11616','19438','4','Oct 2 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11624','227','4','Oct 2 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11671','656','4','Oct 6 2009 12:00AM','Apr 21 2010 11:59PM','E' UNION ALL

SELECT '11674','12090','4','Oct 2 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11739','547','4','Oct 1 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11788','11173','4','Oct 12 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11826','366','4','Oct 14 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11851','361','4','Oct 16 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11920','193','4','Oct 23 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11921','21438','4','Oct 26 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11932','1676','4','Oct 26 2009 12:00AM','Jan 6 2010 11:59PM','E' UNION ALL

SELECT '11939','592','4','Oct 27 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11940','97','4','Oct 27 2009 12:00AM',NULL,'W' UNION ALL

SELECT '11942','427','4','Oct 26 2009 12:00AM',NULL,'E' UNION ALL

SELECT '11947','200','4','Oct 29 2009 12:00AM','Nov 29 2009 11:59PM','E' UNION ALL

SELECT '11987','553','4','Nov 2 2009 12:00AM',NULL,'E' UNION ALL

SELECT '12046','488','4','Nov 4 2009 12:00AM',NULL,'W' UNION ALL

SELECT '12054','583','4','Nov 5 2009 12:00AM',NULL,'W' UNION ALL

SELECT '12154','22959','4','Nov 16 2009 12:00AM','Feb 3 2010 11:59PM','E' UNION ALL

SELECT '12159','577','4','Nov 16 2009 12:00AM','Nov 23 2009 11:59PM','W' UNION ALL

SELECT '12169','23003','4','Nov 16 2009 12:00AM',NULL,'E' UNION ALL

SELECT '12170','12055','4','Sep 8 2009 12:00AM',NULL,'W' UNION ALL

SELECT '12249','577','4','Nov 24 2009 12:00AM',NULL,'E' UNION ALL

SELECT '12251','16782','4','Nov 24 2009 12:00AM','Feb 22 2010 11:59PM','W' UNION ALL

SELECT '12253','502','4','Nov 24 2009 12:00AM',NULL,'W' UNION ALL

SELECT '12283','180','4','Dec 1 2009 12:00AM',NULL,'W' UNION ALL

SELECT '12284','200','4','Nov 30 2009 12:00AM','Dec 1 2009 11:59PM','W' UNION ALL

SELECT '12293','200','4','Dec 2 2009 12:00AM','Feb 2 2010 11:59PM','E' UNION ALL

SELECT '12363','185','4','Dec 9 2009 12:00AM',NULL,'W' UNION ALL

SELECT '12455','12518','4','Jan 6 2010 12:00AM','Feb 22 2010 11:59PM','W' UNION ALL

SELECT '12456','621','4','Jan 6 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12457','104','4','Jan 6 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12470','6492','4','Jan 5 2010 12:00AM',NULL,'E' UNION ALL

SELECT '12481','24595','4','Jan 5 2010 12:00AM',NULL,'E' UNION ALL

SELECT '12496','24608','4','Jan 5 2010 12:00AM',NULL,'E' UNION ALL

SELECT '12501','24612','4','Jan 5 2010 12:00AM',NULL,'E' UNION ALL

SELECT '12507','24615','4','Jan 5 2010 12:00AM',NULL,'E' UNION ALL

SELECT '12615','259','4','Jan 7 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12616','1676','4','Jan 7 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12617','348','4','Jan 7 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12618','101','4','Jan 7 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12666','24806','4','Jan 8 2010 12:00AM','Jan 14 2010 11:59PM','E' UNION ALL

SELECT '12682','11706','4','Jan 8 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12691','334','4','Jan 12 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12693','13360','4','Jan 12 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12786','25281','4','Jan 19 2010 12:00AM',NULL,'E' UNION ALL

SELECT '12802','25297','4','Jan 19 2010 12:00AM',NULL,'E' UNION ALL

SELECT '12857','13000','4','Jan 23 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12880','24806','4','Jan 15 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12909','295','4','Jan 30 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12937','200','4','Feb 3 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12974','22959','4','Feb 4 2010 12:00AM',NULL,'W' UNION ALL

SELECT '12991','26145','4','Feb 5 2010 12:00AM',NULL,'E' UNION ALL

SELECT '13144','16782','4','Feb 23 2010 12:00AM',NULL,'R' UNION ALL

SELECT '13149','12518','4','Feb 23 2010 12:00AM',NULL,'R' UNION ALL

SELECT '13194','586','4','Feb 26 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13243','12986','4','Mar 2 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13270','78','4','Feb 27 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13272','444','4','Mar 8 2010 12:00AM',NULL,'E' UNION ALL

SELECT '13395','563','4','Mar 23 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13400','30735','4','Mar 23 2010 12:00AM','Mar 30 2010 11:59PM','E' UNION ALL

SELECT '13423','386','4','Mar 24 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13426','30927','4','Mar 24 2010 12:00AM',NULL,'E' UNION ALL

SELECT '13442','18014','4','Mar 25 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13446','11673','4','Mar 27 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13467','30735','4','Mar 31 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13475','390','4','Apr 1 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13503','562','4','Apr 6 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13534','12104','4','Apr 10 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13546','387','4','Apr 14 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13581','656','4','Apr 22 2010 12:00AM',NULL,'W' UNION ALL

SELECT '13588','33086','4','Apr 22 2010 12:00AM',NULL,'E' UNION ALL

SELECT '13599','214','4','Apr 27 2010 12:00AM',NULL,'W'

-- The FOLLOWING Query Lists the Pertinent Information

--- Notice that student 200 Enrolled then Withdrew 3 times. There is a Null value in the EndTime. I still need this data even though she did not Re-enroll the last time. However the CTE script below this query does not capture this because of the "e2.StartDate > e.StartDate " restriction in the WHERE clause.

SELECT EnrollmentID, StudentID, StartDate, EndTime, ActionClass, ROW_NUMBER()

OVER(PARTITION BY StudentID ORDER BY StudentID , StartDate) AS rn

FROM #Enrollment

WHERE AcadSessionID IN (4,30) AND StudentID IN

(SELECT StudentID

FROM #Enrollment

WHERE ActionClass = 'E' AND AcadSessionID IN (4,30)

GROUP BY StudentID

HAVING COUNT(StudentID) > 1)

-- The Best query that I have is this one. But it removes NULLs of students withdrawal again but not re-enrolled

--- I use the RowCount in the CTE to create this clause "e2.rn - e.rn = 1". It forces the W/Re-E cycle to be consecutive. Otherwise it produces a kind of cartestian produce among the StartDates.

--The PROBLEM with this query is that student 200 appears to be Enrolled because her lastest withdrawal does not show because there is no corresponding Re-Enrollment.

;

WITH Reenrollments

AS

(

SELECT EnrollmentID, StudentID, StartDate, EndTime, ActionClass, ROW_NUMBER()

OVER(PARTITION BY StudentID ORDER BY StudentID , StartDate) AS rn

FROM #Enrollment

WHERE AcadSessionID IN (4,30) AND StudentID IN

(SELECT StudentID

FROM #Enrollment

WHERE ActionClass = 'E' AND AcadSessionID IN (4,30)

GROUP BY StudentID

HAVING COUNT(StudentID) > 1)

)

SELECT e.EnrollmentID, e.StudentID, e.StartDate, e.ActionClass, e2.StartDate, e2.ActionClass, e.EndTime

FROM Reenrollments e

LEFT JOIN Reenrollments e2 ON e.StudentID = e2.StudentID AND e.ActionClass = 'W' AND e2.ActionClass = 'E'

WHERE e2.StartDate > e.StartDate AND e2.rn - e.rn = 1

ORDER BY e.StudentID , e.StartDate

--Finally, this is a query I was able to produce that included the NULLs I need, but It placed the Withdrawals in the wrong column. Notice that the Withdrawals are in the column with the Enrollments. Why is that?

-- Also With WHERE clause commented out, there is no way to make sure the W/E are consecutive. I don't need th earliers Withdrawals to be paired with ALL the later Re-enrollments or vise versa.

WITH Reenrollments

AS

(

SELECT EnrollmentID, StudentID, StartDate, EndTime, ActionClass, ROW_NUMBER()

OVER(PARTITION BY StudentID ORDER BY StudentID , StartDate) AS rn

FROM #Enrollment

WHERE AcadSessionID IN (4,30) AND StudentID IN

(SELECT StudentID

FROM #Enrollment

WHERE ActionClass = 'E' AND AcadSessionID IN (4,30)

GROUP BY StudentID

HAVING COUNT(StudentID) > 1)

)

SELECT e.EnrollmentID, e.StudentID, e2.StartDate, e2.ActionClass, e.StartDate, e.ActionClass, e.EndTime

FROM Reenrollments e

LEFT JOIN Reenrollments e2 ON e.StudentID = e2.StudentID AND e.ActionClass = 'E' AND e2.ActionClass = 'W'

--WHERE e2.StartDate < e.StartDate OR e2.StartDate = NULL AND e2.rn - e.rn = -1

ORDER BY e.StudentID , e.StartDate

Thanks very much. HOw do i get the Withdrawals in the right column and make them consecutive?

Ken McKelvey SSCoach Points: 18859 More actions · Answer 2

Assuming an enrollment is never followed by an enrollment, I would be inclined to ignore the EndDate.

Try something like:

;WITH Reenrollments

AS

(

SELECT *

,ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY StartDate) As RowNum

FROM #Enrollment

WHERE AcadSessionID IN (4,30) AND StudentID IN

(

SELECT StudentID

FROM #Enrollment

WHERE ActionClass = 'E'

AND AcadSessionID IN (4,30)

GROUP BY StudentID

HAVING COUNT(StudentID) > 1

)

SELECT R1.StudentID, R1.EnrollmentID, R1.StartDate AS Enrolled

,R2.EnrollmentID As WithDrewEnrollmentID, R2.StartDate AS Withdrew

FROM Reenrollments R1

LEFT JOIN Reenrollments R2

ON R1.StudentID = R2.StudentID

AND R2.RowNum = R1.RowNum + 1

WHERE R1.RowNum % 2 > 0

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Hi there, can you please provide us how your desired output will look like. i have actually coded a piece for you, but i want to confirm it with your desired result to cross-verify..

TIA.

Cheers!

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 4

Thanks guys! Ken, you totally changed the query. It was in the basic format that i needed. I'm looking for all the people who withdrew and then RE-enrolled. Thus all withdrawals will have enrollments following it.

ColdCoffee, Thanks for your help. I'm trying to think of how best to answer your question. I'd like to provide a screen shot.

If you do a search on StudentID 200.

select * from #enrollment where studentID = 200

YOu will see their inital enrollmentID is 10983 on 8-11-2009 (Not to be Included in resultset).

She withdrew on 09-04-2009 with EnrollmentID of 11325 (Ignore EndTimes but include this record. I don't care about initial enrollments).

She re-enrolled again. The EnrollmentID is 11947 (First Re-enrollment should be included since we are capturing Withdrawals/(RE-)Enrollments cycles). This is first cycle of Withdraw and Re-enrollment.

Withdrew again (12284). This is the begining of second cycle. This should be included in resultset.

Enrolled again lol (12293). This is the end of the second cycle of W/E. It should also be included.

Then FINALLY she Withdrew (12937) but never re-enrolled. This is the beginning of the third cycle which is not complete because she didn't re-enroll. So there is no corresponding re-enrollment. My query breaks here because Since she didn't enroll again, the EnrollmentID of 12937 doesn't show up in the resultset below. LOOKING AT THIS DATA it SEEMS she is still enrolled when she is not!

Since i based this on enrollmentIDs instead of dates, I added both EnrollmentIDs (previous queries only had one) so it is easier to follow. Using the query below 12937 does not show up in the result set, but it should. Everything else is FINE! I would expect the 3rd record to begin with 12937 and NULL (or NA) to appear in the Enrollment columns.

;

WITH Reenrollments

AS

(

SELECT EnrollmentID, StudentID, StartDate, EndTime, ActionClass, ROW_NUMBER()

OVER(PARTITION BY StudentID ORDER BY StudentID , StartDate) AS rn

FROM #Enrollment

WHERE AcadSessionID IN (4,30) AND StudentID IN

(SELECT StudentID

FROM #Enrollment

WHERE ActionClass = 'E' AND AcadSessionID IN (4,30)

GROUP BY StudentID

HAVING COUNT(StudentID) > 1)

)

SELECT e.EnrollmentID, e.StudentID, e.StartDate, e.ActionClass, e2.EnrollmentID, e2.StartDate, e2.ActionClass, e.EndTime

FROM Reenrollments e

LEFT JOIN Reenrollments e2 ON e.StudentID = e2.StudentID AND e.ActionClass = 'W' AND e2.ActionClass = 'E'

WHERE e2.StartDate > e.StartDate AND e2.rn - e.rn = 1

ORDER BY e.StudentID , e.StartDate

Thanks!

Ken McKelvey SSCoach Points: 18859 More actions · Answer 5

Thus all withdrawals will have enrollments following it.

In my query they do.

The idea is that RowNum % 2 = 1 for enrollments and RowNum % 2 = 0 for withdrawals.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 6

For StudentID 577, if anyone is confused, There is an EnrollmentID of 559. It is an error. It got included by mistake and is not part of the data.

Ken McKelvey SSCoach Points: 18859 More actions · Answer 7

To drive by withdrawals with the same idea, try the following:

;WITH Reenrollments

AS

(

SELECT *

,ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY StartDate) As RowNum

FROM #Enrollment

WHERE AcadSessionID IN (4,30) AND StudentID IN

(

SELECT StudentID

FROM #Enrollment

WHERE ActionClass = 'E'

AND AcadSessionID IN (4,30)

GROUP BY StudentID

HAVING COUNT(StudentID) > 1

)

SELECT R1.StudentID, R1.EnrollmentID AS WithDrewEnrollmentID, R1.StartDate AS Withdrew

,R2.EnrollmentID , R2.StartDate AS Enrolled

FROM Reenrollments R1

LEFT JOIN Reenrollments R2

ON R1.StudentID = R2.StudentID

AND R2.RowNum = R1.RowNum + 1

WHERE R1.RowNum % 2 = 0

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 8

Hi Ken. Thanks very much. I'm still studying your query just to learn how i could do this differently (syntax wise). However, your query STARTS with INITAL enrollment then withdrawal. I only want WITHDRAWAL then RE-Enrollments (E). E is the same letter regardless of whether it is initial enrollment or re-enrollment. The database allows R's for re-enrollment but the school did not use it. Sorry for the confusion. The first enrollment is the initial one. Any enrollments following a withdrawal is a RE-enrollment.

I am studying your query just for education.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 9

Thanks a million Ken! That's exactly what i want! I understand how you got them in the same record using AND R2.RowNum = R1.RowNum + 1 .

OK, i understand the WHERE clause better now. lol. Wow! I would have never thought to do this.

How did you know that the rownumbers would automatically split into Odd and Even numbers? I don't really see anything saying which column belongs to which group.

thanks again.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

There may be some mileage in this:

;WITH EnrollmentRollup AS (

SELECT

StudentID,

Summary = SUM(CASE ActionClass WHEN 'E' THEN 1 WHEN 'W' THEN -1 ELSE 0 END),

Actions = COUNT(*)

FROM #Enrollment

WHERE ActionClass <> 'R'

GROUP BY StudentID

HAVING COUNT(*) > 1

)

SELECT StudentID, Actions, Summary,

CurrentSituation = CASE

WHEN Actions = 1 AND Summary = 1 THEN 'Enrolled Once'

WHEN Actions = 2 AND Summary = 0 THEN 'Enrolled/Withdrew'

WHEN Summary = 0 THEN 'Enrolled/Withdrew several times'

WHEN Actions = 3 AND Summary = 1 THEN 'Enrolled again'

WHEN Summary > 1 THEN 'Enrollment/withdrawal mismatch'

ELSE 'Something else' END

FROM EnrollmentRollup

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 11

Ok the conclusion I've come to is that I restricted too early on. If i had just allowed the cartesian product on the query and then looked at it and then restricted from there. I JUST MIGHT have come up with your query.

The biggest problem i guess with my query was that e2.StartDate > e.StartDate prevented the NULLs from appearing and i need something to do the same thing but get around the need to use that expression.

I must say, your query does seem a bit cryptic and unintuitive however. I'm not shooting it down. I try to avoid that so it is easier to maintain and i don't have to re-study the whole process to get different results.

Thanks very much!!!! I'm going to keep play with it.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 12

Chris, i need to run home for a minute. I will look at your solution ASAP!

Thanks for the effort!