June 20, 2011 at 5:26 pm
Hi,
I have below table data . AND I want the effective date less than the next start date of each student.
Example : In below table the 6th row . The startdate is 8/6/2007 And EffectiveDate is 7/7/2010
The EffectiveDate should compare the next start date in row 7 10/5/2009 ,
Which is false because the 7th row ( next startdate is 10/5/2009)
So, effectivedate of 6th row should be less than startdate(next start) of 7th row.
-- declare table as follows
declare @studentinfo table
(
rownum int identity(1,1)--- rownum is identity column used to fetch and pinpoint a single row in table
,name varchar(50)
,startdate datetime
,effectivedate datetime
)
---- prepare script similar to below based upon the need. --- in ur case insert data from source tables directly
insert into @studentinfo (name,startdate,effectivedate) values ('bmc North Kentucky',convert(datetime,'8/6/2007'),convert(datetime,'8/6/2007'))
insert into @studentinfo (name,startdate,effectivedate) values ('bmc North Kentucky',convert(datetime,'8/6/2007'),convert(datetime,'8/6/2007'))
insert into @studentinfo (name,startdate,effectivedate) values ('bmc North Kentucky',convert(datetime,'8/6/2007'),convert(datetime,'8/28/2009'))
insert into @studentinfo (name,startdate,effectivedate) values ('bmc North Kentucky',convert(datetime,'10/5/2009'),convert(datetime,'10/5/2009'))
insert into @studentinfo (name,startdate,effectivedate) values ('bmc North Kentucky',convert(datetime,'10/5/2009'),convert(datetime,'10/5/2009'))
insert into @studentinfo (name,startdate,effectivedate) values ('bmc North Kentucky',convert(datetime,'8/6/2007'),convert(datetime,'7/7/2010'))
insert into @studentinfo (name,startdate,effectivedate) values ('bmc North Kentucky',convert(datetime,'10/5/2009'),convert(datetime,'7/7/2010'))
Please help me ASAP .
Thanks in Advance.
June 20, 2011 at 7:00 pm
Can you show us what you have so far?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 20, 2011 at 8:25 pm
Hi,
Below is the stored procedure I wrote . But I can't bound the End date as per buisness requirement.
So , I need to create a variable which stores the information and compare with next start date.
Procedure:
SELECT
SyCampusID
,Descrip
,CASE
WHEN Descrip LIKE 'SOUTH%' THEN 14
WHEN Descrip LIKE 'ARGOSY%' THEN 95
WHEN Descrip LIKE '%ART%' THEN 54
WHEN Descrip LIKE 'BROWN%' THEN 83
END AS BrandID
INTO #schools
FROM CVueOHERep.c2000.dbo.SyCampus WITH (NOLOCK)
WHERE
Descrip LIKE 'SOUTH%'
OR Descrip LIKE 'ARGOSY%'
OR Descrip LIKE '%ART%'
OR Descrip LIKE 'BROWN%'
CREATE INDEX IX_Schools___PK ON #schools(SyCampusID)
CREATE INDEX IX_Schools___Brand ON #schools(BrandID)
print '39: ' + convert(varchar,getdate(),114)
--------------------------------------------------------------------------------
-- get eligible sessions. for now, use all terms
--------------------------------------------------------------------------------
SELECT
SessionID
,SessionName
,CAST(CONVERT(VARCHAR,SessionStartDate,102) AS DATETIME) AS SessionStartDate
,CAST(CONVERT(VARCHAR,SessionEndDate,102) AS DATETIME) AS SessionEndDate
,SchoolID AS BrandID
INTO #sessions
FROM OHE_Reporting.dbo.SessionDates WITH (NOLOCK)
WHERE
SessionStartDate IS NOT NULL
AND SessionEndDate IS NOT NULL
AND SessionStartDate > '2005-12-31 00:00:00'
AND SessionEndDate < GetDate()
And SessionID NOT IN (480,481,482,483,484,485,486)
print '56: ' + convert(varchar,getdate(),114)
--------------------------------------------------------------------------------
-- get the SSB for each year: students with School Status change
-- Applied/Accepted to Active
--------------------------------------------------------------------------------
;WITH RANKED_STARTS AS (
SELECT
sc.systudentid
,s.AdProgramID
,t.SessionID
,t.SessionStartDate
,t.SessionEndDate
,t.SessionName AS TermName
,c.Descrip AS Campus
,c.SyCampusID
,c.BrandID
,CAST(CONVERT(VARCHAR,sc.EffectiveDate,102) AS DATETIME) AS EffectiveDate
--,e.AdProgramDescrip AS Program
,ROW_NUMBER() OVER(
PARTITION BY
sc.SyStudentID
--sc.AdEnrollID
ORDER BY
sc.EffectiveDate DESC
,NEWID()
) AS Ranking
FROM CVueOHERep.c2000.dbo.SyStatChange sc WITH (NOLOCK)
--INNER JOIN CVueOHERep.c2000.dbo.AdEnroll e WITH (NOLOCK)
--ON sc.AdEnrollID = e.AdEnrollID
INNER JOIN CVueOHERep.c2000.dbo.SyStudent s WITH (NOLOCK)
ON sc.SyStudentID = s.SyStudentID
INNER JOIN #schools c ON s.SyCampusID = c.SyCampusID
INNER JOIN #sessions t
ON c.BrandID = t.BrandID
AND sc.EffectiveDate BETWEEN t.SessionStartDate AND t.SessionEndDate
WHERE
sc.prevSySchoolStatusID in (
5-- accepted
)
AND sc.newSySchoolStatusID = 13
AND sc.Status = 'P'
)
SELECT
SyStudentID
,Ranking
,AdProgramID
,SessionID AS InitialSessionID
,CAST(CONVERT(VARCHAR,SessionStartDate,102) AS DATETIME) AS InitialTermStartDate
,CAST(CONVERT(VARCHAR,SessionEndDate,102) AS DATETIME) AS InitialTermEndDate
,TermName As InitialTerm
,SyCampusID
,Campus
,BrandID
,EffectiveDate
-- ,DateDiff(dd,sessionstartDate,EffectiveDate ) AS Buckets
--,Program
INTO #students
FROM RANKED_STARTS starts
Select * from #students
where systudentID IN(1275749,11617757, 11996514 ,11869945,1303024)
print '110: ' + convert(varchar,getdate(),114)
create index IX_tempStudents___Student ON #students(SyStudentID)
create index IX_tempStudents___Brand ON #students(BrandID)
--------------------------------------------------------------------------------
-- get all status changes for each student in the SSB. count only one status
-- change per group (priority) for each student
--------------------------------------------------------------------------------
;WITH STATUS_CHANGES AS (
select
sc.SyStudentID
--,sc.AdEnrollID
,s.AdProgramID
,s.InitialSessionID
,s.InitialTerm
,s.InitialTermStartDate
,s.SyCampusID
,s.Campus
,s.BrandID
--,t.SessionID AS StatusSessionID
--,t.Sessionstartdate As StatusTermStartdate
,sc.EffectiveDate
-- ,s.Buckets
-- ,Sc.NewsyschoolstatusID
-- ,sc.PrevsyschoolstatusID
,CASE
WHEN sc.NewSySchoolStatusID IN (
17-- Graduate
) THEN 1 -- GRAD
WHEN sc.NewSySchoolStatusID IN (
11-- Cancel
,45-- Dismissal
,112-- Post-matriculation rejection
,20-- Withdrawal
)
AND sc.PrevSyschoolStatusID IN(
13-- Active
,113-- ANA
,102-- Pending cancel
,90-- Pending drop
,77-- Pending graduate
,14-- Probation
)
THEN 2 -- DROP
WHEN sc.NewSySchoolStatusID IN (
15-- LOA
,100120-- Military deployment
) THEN 3 -- AWAY
WHEN sc.NewSySchoolStatusID = 13
AND sc.PrevSyschoolStatusID IN(
7
,20
,45
) THEN 4 -- REENTRY
WHEN sc.NewSySchoolStatusID = 13
AND sc.PrevSySchoolStatusID = 5
--13-- Active
--,113-- ANA
--,102-- Pending cancel
--,90-- Pending drop
--,77-- Pending graduate
--,14-- Probation
THEN 5 -- STARTS
ELSE 6 -- "reentry" using either reentry status or DROP/AWAY -> Active
END AS StatusPriority
from #students s
left join CVueOHERep.c2000.dbo.SyStatChange sc with (nolock)
ON sc.SyStudentID = s.SyStudentID
AND sc.EffectiveDate > s.InitialTermStartDate
AND sc.NewSySchoolStatusID <> sc.PrevSySchoolStatusID
),
RANKED_STATUS_CHANGES AS (
SELECT
SyStudentID
--,AdEnrollID
,AdProgramID
,InitialSessionID
,InitialTerm
,InitialTermStartDate
,SyCampusID
,Campus
,BrandID
--,StatusSessionID
--,StatusTermStartDate
,StatusPriority
,CASE StatusPriority
WHEN 1 THEN 'GRAD'
WHEN 2 THEN 'DROP'
WHEN 3 THEN 'AWAY'
WHEN 4 THEN 'REENTRY'
WHEN 5 THEN 'START'
END AS Status
,CAST(CONVERT(VARCHAR,EffectiveDate,102) AS DATETIME) AS EffectiveDate
-- ,Buckets
-- ,NewSyschoolstatusID
-- ,PrevsyschoolstatusID
,ROW_NUMBER() OVER(
PARTITION BY
SyStudentID
,InitialsessionID
,StatusPriority
ORDER BY
EffectiveDate
,NewID()
) AS Ranking
FROM STATUS_CHANGES
),
EFFECTIVE_STATUS_CHANGES AS (
SELECT
*
FROM RANKED_STATUS_CHANGES
WHERE Ranking = 1
)
SELECT * from Effective_status_changes
The Result of this logic is :
SystudentId Startdate EffectiveDate Status
1 1011101 8/6/2007 8/6/2007 Start
2 1011101 8/6/2007 8/28/2009 Graduate
3 1011101 8/6/2007 7/7/2010 Reentry ------Incorrect effectivedate
4 1011101 8/6/2007 10/19/2010 Drop ------Incorrect effectivedate
5 1011101 10/5/2009 10/5/2009 Start
6 1011101 10/5/2009 7/7/2010 Reentry
7 1011101 10/5/2009 8/20/2010 Gradruate
8 1011101 10/5/2009 10/19/2010 Drop
Please look at the procedure and suggest me solution through which I can delete the incorrect effectivedate falls at 3 and 4 record in above result.
Thanks
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply