locked
SQL help - how to figure out consecutive workdays with holidays RRS feed

  • Question

  • User1545658684 posted

    Hi everyone,

    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 [:^)]

     

    Tuesday, July 18, 2006 11:25 AM

All replies

  • User-1786411686 posted
    You could create a table variable and loop through the records and do some date manipulation.
    For Example:  The following SQL will tell you what day it is, given a date. Since you already have a list of dates and another list of holidays, you can compare with either and find out if its a weekend or holiday. if its neither and is consecutive with the previous date you could increment a flag and if the flag reaches 6 then the student has missied 6 consecutive weekdays.
     
    Select DateName(Weekday, '2006-04-09') As 'DayOfWeek'
     
     
    PS: LSU = Louisiana State University from Baton Rouge?
    Tuesday, July 18, 2006 1:28 PM
  • User1545658684 posted

    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.

    jessica

    Tuesday, July 18, 2006 2:27 PM
  • User-1786411686 posted
    See if you can google. Perhaps there's some code that you can use readily.
     
     
    Tuesday, July 18, 2006 4:21 PM
  • User-1225738063 posted

    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.

    Step 1

    SELECT studentid,date

    FROM a

    UNION

    SELECT studentid,h.date FROM (SELECT distinct studentid FROM a) z CROSS JOIN h

    Step 2

    SELECT studentid,date AS Start,(SELECT TOP 1 date

    FROM (SELECT studentid,date

    FROM a

    UNION

    SELECT studentid,h.date FROM (SELECT distinct studentid FROM a) z CROSS JOIN h) t2

    WHERE CASE WHEN DATEPART(dw,date)=6 THEN DATEADD(d,3,date) ELSE DATEADD(d,1,date) END NOT IN (SELECT date

    FROM a WHERE studentid=t2.studentid UNION

    SELECT date FROM h) AND t2.studentid=t1.studentid AND t2.date>=t1.date ORDER BY date ASC) AS Finish

    FROM (SELECT studentid,date

    FROM a

    UNION

    SELECT studentid,h.date FROM (SELECT distinct studentid FROM a) z CROSS JOIN h) t1

    WHERE CASE WHEN DATEPART(dw,date)=2 THEN DATEADD(d,-3,date) ELSE DATEADD(d,-1,date) END NOT IN (SELECT date

    FROM a WHERE studentid=t1.studentid UNION

    SELECT date FROM h)

    STEP 3

    SELECT studentid,Start,Finish,datediff(d,Start,Finish)+1 AS ConsecutiveDays,datediff(week,Start,Finish)*2 AS WeekendDays,(SELECT COUNT(*) FROM h WHERE date>=Start and date<=Finish) AS Holidays

    FROM (

    SELECT studentid,date AS Start,(SELECT TOP 1 date

    FROM (SELECT studentid,date

    FROM a

    UNION

    SELECT studentid,h.date FROM (SELECT distinct studentid FROM a) z CROSS JOIN h) t2

    WHERE CASE WHEN DATEPART(dw,date)=6 THEN DATEADD(d,3,date) ELSE DATEADD(d,1,date) END NOT IN (SELECT date

    FROM a WHERE studentid=t2.studentid UNION

    SELECT date FROM h) AND t2.studentid=t1.studentid AND t2.date>=t1.date ORDER BY date ASC) AS Finish

    FROM (SELECT studentid,date

    FROM a

    UNION

    SELECT studentid,h.date FROM (SELECT distinct studentid FROM a) z CROSS JOIN h) t1

    WHERE CASE WHEN DATEPART(dw,date)=2 THEN DATEADD(d,-3,date) ELSE DATEADD(d,-1,date) END NOT IN (SELECT date

    FROM a WHERE studentid=t1.studentid UNION

    SELECT date FROM h)

    ) t3

    STEP 4

    SELECT studentid,Start,Finish,datediff(d,Start,Finish)+1 AS ConsecutiveDays,datediff(week,Start,Finish)*2 AS WeekendDays,(SELECT COUNT(*) FROM h WHERE date>=Start and date<=Finish) AS Holidays,

    datediff(d,Start,Finish)+1-datediff(week,Start,Finish)*2-(SELECT COUNT(*) FROM h WHERE date>=Start and date<=Finish) AS ConsecutiveClassesMissed

    FROM (

    SELECT studentid,date AS Start,(SELECT TOP 1 date

    FROM (SELECT studentid,date

    FROM a

    UNION

    SELECT studentid,h.date FROM (SELECT distinct studentid FROM a) z CROSS JOIN h) t2

    WHERE CASE WHEN DATEPART(dw,date)=6 THEN DATEADD(d,3,date) ELSE DATEADD(d,1,date) END NOT IN (SELECT date

    FROM a WHERE studentid=t2.studentid UNION

    SELECT date FROM h) AND t2.studentid=t1.studentid AND t2.date>=t1.date ORDER BY date ASC) AS Finish

    FROM (SELECT studentid,date

    FROM a

    UNION

    SELECT studentid,h.date FROM (SELECT distinct studentid FROM a) z CROSS JOIN h) t1

    WHERE CASE WHEN DATEPART(dw,date)=2 THEN DATEADD(d,-3,date) ELSE DATEADD(d,-1,date) END NOT IN (SELECT date

    FROM a WHERE studentid=t1.studentid UNION

    SELECT date FROM h)

    ) t3

    WHERE datediff(d,Start,Finish)+1-datediff(week,Start,Finish)*2-(SELECT COUNT(*) FROM h WHERE date>=Start and date<=Finish)>=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).

     

     

     

    Wednesday, July 19, 2006 4:56 AM
  • User-1225738063 posted

    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.

    Wednesday, July 19, 2006 5:06 AM
  • User-839310871 posted

    wow.. this worked just fine for me. thanks alot

    Monday, December 7, 2015 6:38 AM
  • User-205763058 posted

    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/

    Thursday, December 10, 2015 3:09 AM