To count the days student is Active

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

To count the days student is Active

Author

Topic

spendyala
Starting Member

15 Posts

Posted - 2008-11-25 : 14:31:59

Hi,

I need some help on this. I am working on a student data base. I have a table that keeps the status change details of the Students
My problem here is that when the student becomes active, i have to see if the student has sat earlier in the school for more than 30 days.

Normally it would be only once but if the student has dropped couple of times, i have to keep track of all those days that the student sat in the school.

For ex: let us consider this example:

13 - Active Status, 6 - Enrolled and 15 - LOA Status

Declare @StatusHistory TABLE (EnrollID int, DateAdded Datetime, NewStatus int, PrevStatus int)
INSERT @StatusHistory

SELECT 99999,'2008-08-05',13,6 UNION ALL
SELECT 99999,'2008-08-10',15,13 UNION ALL
SELECT 99999,'2008-08-15',13,15 UNION ALL
SELECT 99999,'2008-08-20',15,13 UNION ALL
SELECT 99999,'2008-11-15',13,6

If I have to get student details in november then the Student has sat for 10 days before starting on 11-15-2008 again.
I tried getting the minimum date or maximum date and they are not working.

Could you please help me with this.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-25 : 15:14:56

Something like this?

Declare @StatusHistory TABLE (EnrollID int, DateAdded Datetime, NewStatus int, PrevStatus int)
INSERT @StatusHistory

SELECT 99999,'2008-08-05',13,6 UNION ALL
SELECT 99999,'2008-08-10',15,13 UNION ALL
SELECT 99999,'2008-08-15',13,15 UNION ALL 
SELECT 99999,'2008-08-20',15,13 UNION ALL 
SELECT 99999,'2008-11-15',13,6 

select * from @StatusHistory

declare @starting datetime
select @starting = '11-15-2008'

select datediff(day,
(select min(DateAdded) from @StatusHistory where EnrollID = 99999 and NewStatus = 13 and DateAdded < @starting),
(select max(DateAdded) from @StatusHistory where EnrollID = 99999 and NewStatus = 13 and DateAdded < @starting)
) as days

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

spendyala
Starting Member

15 Posts

Posted - 2008-11-25 : 15:49:56

WebFred,

Thank you for the Response. But it doesnt work that way.

For Ex: Consider this case

Declare @StatusHistory TABLE (EnrollID int, DateAdded Datetime, NewStatus int, PrevStatus int)
INSERT @StatusHistory

SELECT 99999,'2008-08-05',13,6 UNION ALL
SELECT 99999,'2008-08-10',15,13 UNION ALL
SELECT 99999,'2008-08-12',13,15 UNION ALL
SELECT 99999,'2008-08-20',15,13 UNION ALL
SELECT 99999,'2008-11-15',13,6

select * from @StatusHistory

declare @starting datetime
select @starting = '11-15-2008'

select datediff(day,
(select min(DateAdded) from @StatusHistory where EnrollID = 99999 and NewStatus = 13 and DateAdded < @starting),
(select max(DateAdded) from @StatusHistory where EnrollID = 99999 and NewStatus = 13 and DateAdded < @starting)
) as days

The Student sat for 13 days here but your sql is giving me only 7 days.

Sri.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:20:24

Not sure how you get 13 days. he was moved to active status on 2008-08-12 after 2008-08-05 which is after gap of 7 days

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:36:29

As per your initial explanation, this is what you're looking for

Declare @StatusHistory TABLE (EnrollID int, DateAdded Datetime, NewStatus int, PrevStatus int)
INSERT @StatusHistory

SELECT 99999,'2008-08-05',13,6 UNION ALL
SELECT 99999,'2008-08-10',15,13 UNION ALL
SELECT 99999,'2008-08-12',13,15 UNION ALL 
SELECT 99999,'2008-08-20',15,13 UNION ALL 
SELECT 99999,'2008-11-15',13,6 


SELECT t.EnrollID,t.DateAdded,t.NewStatus,DATEDIFF(dd,prev.DateAdded,t.DateAdded) AS DaysSat
FROM @StatusHistory t
OUTER APPLY (SELECT TOP 1 DateAdded
             FROM @StatusHistory
             WHERE EnrollID=t.EnrollID
             AND NewStatus=t.NewStatus
             AND DateAdded <t.DateAdded
             ORDER BY DateAdded DESC) AS Prev

output
--------------------------------------------------
EnrollID	DateAdded	NewStatus	DaysSat
99999	2008-08-05 00:00:00.000	13	NULL
99999	2008-08-10 00:00:00.000	15	NULL
99999	2008-08-12 00:00:00.000	13	7
99999	2008-08-20 00:00:00.000	15	10
99999	2008-11-15 00:00:00.000	13	95

spendyala
Starting Member

15 Posts

Posted - 2008-11-26 : 13:14:48

Hi Visakh,

I think my explaination was not correct enough to get the 13 days result.

Coming to my 2nd example, the student became active on 2008-08-05 and went on leave on 2008-08-10. So for the first time he sat in the School for 5 days.

Then again he became active on 2008-08-12 and went on leave on 2008-08-20, so this time around he sat in the school for 8 days.

So by the third time he became active again on 2008-11-15, he was active earlier in the school for 13 days.

Please let me know if you have any questions.

Thanks for the help!!!

Sri.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 03:37:45

[code]Declare @StatusHistory TABLE (EnrollID int, DateAdded Datetime, NewStatus int, PrevStatus int)
INSERT @StatusHistory

SELECT 99999,'2008-08-05',13,6 UNION ALL
SELECT 99999,'2008-08-10',15,13 UNION ALL
SELECT 99999,'2008-08-12',13,15 UNION ALL
SELECT 99999,'2008-08-20',15,13 UNION ALL
SELECT 99999,'2008-11-15',13,6

;With CTE (EnrollID, DateAdded , NewStatus , PrevStatus,TimeDiff) AS
(
SELECT t.EnrollID, t.DateAdded , t.NewStatus , t.PrevStatus,DATEDIFF(dd,tmp.DateAdded,t.DateAdded)
FROM @StatusHistory t
OUTER APPLY(SELECT TOP 1 DateAdded
FROM @StatusHistory
WHERE EnrollID=t.EnrollID
AND NewStatus=t.PrevStatus
AND DateAdded<t.DateAdded
ORDER BY DateAdded DESC)tmp
)

SELECT t.EnrollID,t.DateAdded,t.NewStatus,COALESCE(Total,0)
FROM CTE t
OUTER APPLY (SELECT SUM(TimeDiff)AS Total
FROM CTE
WHERE EnrollID=t.EnrollID
AND NewStatus=15
AND DateAdded<t.DateAdded) AS Prev
WHERE NewStatus=13

output
--------------------------------------------
EnrollID DateAdded NewStatus Total
99999 2008-08-05 00:00:00.000 13 NULL
99999 2008-08-12 00:00:00.000 13 5
99999 2008-11-15 00:00:00.000 13 13
[/code]

spendyala
Starting Member

15 Posts

Posted - 2008-11-27 : 16:22:50

Hi Vishakh,

Thanks so much for the answer.

Regards,

Sri.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 05:38:13

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources