Last post Dec 10, 2015 03:09 AM by MelanieFork52
Jul 18, 2006 11:25 AM|LSUgirl|LINK
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there
are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks in advance, Jessica [:^)]
Jul 18, 2006 01:28 PM|ndinakar|LINK
Jul 18, 2006 02:27 PM|LSUgirl|LINK
Thanks for the info. I've sort of worked out that logic. I was just hoping someone had already had this problem so I didn't have to reinvent the wheel.
LSU - Yes, in Baton Rouge. I live in Austin, Tx now.
Jul 18, 2006 04:21 PM|ndinakar|LINK
Jul 19, 2006 04:56 AM|Motley|LINK
Don't need a loop, but it'll be a pretty complex statement. The approach I would take also depends on how peformant you really need the query to run. The faster you need it to run, the more complex the query will be.
This would be my first approach to the problem:
First break the problem into steps. Assuming tables:
Table h (date datetime) -- "Holiday table"
Table a (studentid int,date datetime) -- "Absentee table"
1) We we need a resultset that contains the dates each person did not attend class.
2) Then we need a resultset consisting of each consecutive range of dates missed.
3) We then need to calculate how many non-weekend, non-holiday days is in each range.
4) We need to see if we have any range that is >=6.
I will leave the rest to you. That will give you a resultset that you should be able to work with from there. It'll list each "break" the students have taken that is longer than 6 missed days. You can remove all the fields except
studentid and make it a "SELECT DISTINCT studentid" if you just want a list of students that have missed 6 or more consecutive classes. This also assumes the following:
The holiday table does not list holidays that are also on a weekend. If it DOES list them, then the code will no longer work. You'll need to remove them either through a view, or replacing each instance of the table h with a subquery
that removes holidays that are on the weekend (WHERE datepart(dw,date)=7 or datepart(dw,date)=1). Both date fields (in the a and h tables) are dates ONLY with no time component. If times are also mentioned then you'll need to strip the time portion either
through a view, or replacing date with DATEADD(d,DATEDIFF(d,0,date),0).
Jul 19, 2006 05:06 AM|Motley|LINK
I don't claim this code is the fastest. It isn't. It was coded as what was easiest/simplest for me. There is undoubtably many optimizations that could be made, especially when determining the min/max range of a student's break, but without using views
the code quickly becomes very unwieldy (in length), and even more unreadable. Normally I would have broken this out to views that call views to simplify the logic, but I didn't do so incase you wanted/needed to be able to paste this in as a sqlcommand in
a .NET application.
One more optimization you may be able to make. If you are SURE that the students will never have an entry in the a table for a holiday, you can replace all the UNION statements with UNION ALL which should greatly speed the query.
Another assumption: Student's can't be marked absent for a weekend day. If it's possible they may be, then again, you'll need to reference a view that removes those, subquery it out, or add additional WHERE logic to remove them.
Last assumption: Weekend days are saturday and sunday and weeks start on Sunday and end on Saturday. That isn't the case in all cultures. I assumed this wasn't going to run in a culture that has weekends of sunday/monday or friday/saturday, or weeks that
don't begin with Sunday and end on Saturday. Some cultures have their weeks start on Monday.
Dec 07, 2015 06:38 AM|DimuthuRanmal|LINK
wow.. this worked just fine for me. thanks alot
Dec 10, 2015 03:09 AM|MelanieFork52|LINK
listen friend, because I
too have not learned about
this. that is why I
try to learn fromthis forum. Hopefully I
can get a lot of science and
beneficial of the learning forum
My website : http://www.jenistanaman.com/