Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 consecutive days count irrespective of weekends, holidays Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, February 05, 2013 1:10 PM
 Grasshopper Group: General Forum Members Last Login: Tuesday, February 25, 2014 10:48 AM Points: 16, Visits: 55
 Hello,I am working with SQL 2008r2. I have a situation where I need to count >15 consecutive school days absent by a student,irrespective of weekends, holidays.The attendance table is having one record for a student for each school day( weekends,HOLIDAYS not included in this table)--------------Table structure :Studentid DateID attendaceind1234 20121031 11234 20121102 11234 20121101 11234 20121105 01234 20121107 11234 20121108 11234 20121109 01234 20121113 01234 20121115 01234 20121114 01234 20121116 01234 20121119 01234 20121120 01234 20121126 01234 20121127 01234 20121128 01234 20121129 01234 20121130 01234 20121203 01234 20121204 01234 20121205 01234 20121206 01234 20121207 01234 20121210 01234 20121211 01234 20121212 01234 20121213 01234 20121214 11234 20121217 11234 20121218 11234 20121219 1-------------------1= Present ; 0 = absentNeed to count 20 consecutive school days absent.i tried to group the days absent. this is what I getStudentid startdate enddate daysabsent day week1234 20120904 20120907 4 4 361234 20120914 20120914 1 14 371234 20121022 20121022 1 22 431234 20121105 20121105 1 5 451234 20121109 20121109 1 9 451234 20121113 20121116 4 13 461234 20121119 20121120 2 19 471234 20121126 20121130 5 26 481234 20121203 20121207 5 3 491234 20121210 20121213 4 10 501234 20130125 20130125 1 25 4Starting from 20121113 untill 20121213 , these are all consecutive school days(including weekends/holidays) But what I want is , Desired resultstudentid startdate enddate daysabsent1234 20121113 20121213 20All your help is greatly appreciated.Thanks in advance!!awaiting help !!
Post #1416057
 Posted Tuesday, February 05, 2013 2:00 PM
 SSChampion Group: General Forum Members Last Login: 2 days ago @ 2:24 PM Points: 11,990, Visits: 11,007
Post #1416089
 Posted Tuesday, February 05, 2013 2:29 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, April 17, 2014 2:02 PM Points: 500, Visits: 2,291
 This should do the trick: `-- SetupIF OBJECT_ID('tempdb..#x') IS NOT NULL DROP TABLE #x;CREATE TABLE #x ( Studentid int, DateID bigint unique, attendaceind bit)INSERT INTO #x SELECT 1234, 20121031, 1 UNION ALL SELECT 1234, 20121102, 1 UNION ALL SELECT 1234, 20121101, 1 UNION ALL SELECT 1234, 20121105, 0 UNION ALL SELECT 1234, 20121107, 1 UNION ALL SELECT 1234, 20121108, 1 UNION ALL SELECT 1234, 20121109, 0 UNION ALL SELECT 1234, 20121113, 0 UNION ALL SELECT 1234, 20121115, 0 UNION ALL SELECT 1234, 20121114, 0 UNION ALL SELECT 1234, 20121116, 0 UNION ALL SELECT 1234, 20121119, 0 UNION ALL SELECT 1234, 20121120, 0 UNION ALL SELECT 1234, 20121126, 0 UNION ALL SELECT 1234, 20121127, 0 UNION ALL SELECT 1234, 20121128, 0 UNION ALL SELECT 1234, 20121129, 0 UNION ALL SELECT 1234, 20121130, 0 UNION ALL SELECT 1234, 20121203, 0 UNION ALL SELECT 1234, 20121204, 0 UNION ALL SELECT 1234, 20121205, 0 UNION ALL SELECT 1234, 20121206, 0 UNION ALL SELECT 1234, 20121207, 0 UNION ALL SELECT 1234, 20121210, 0 UNION ALL SELECT 1234, 20121211, 0 UNION ALL SELECT 1234, 20121212, 0 UNION ALL SELECT 1234, 20121213, 0 UNION ALL SELECT 1234, 20121214, 1 UNION ALL SELECT 1234, 20121217, 1 UNION ALL SELECT 1234, 20121218, 1 UNION ALL SELECT 1234, 20121219, 1 GODECLARE @startdate bigint=20121113, @endDate bigint=20121213;;WITH answer AS( SELECT StudentID, COUNT(*) AS DaysAbs FROM #x WHERE DateID>=@startdate AND DateID<=@endDate AND attendaceind=0 GROUP BY Studentid)SELECT StudentID, @startdate AS startdate, @endDate AS endDate, DaysAbsFROM answerGO` -- Alan BursteinRead this article for best practices on asking questions.Need to split a string? Try this (Jeff Moden)Need a pattern-based string spitter? Try this (Dwain Camps)My blog
Post #1416106
 Posted Tuesday, February 05, 2013 2:42 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, April 17, 2014 2:02 PM Points: 500, Visits: 2,291
 Using the sample code I created before you can also get days in/days not in using this:`DECLARE @startdate bigint=20121101, @endDate bigint=20121130;;WITH notthere AS( SELECT StudentID, COUNT(*) AS DaysAbs FROM #x WHERE DateID>=@startdate AND DateID<=@endDate AND attendaceind=0 GROUP BY Studentid),wasthere AS( SELECT StudentID, COUNT(*) AS DaysIn FROM #x WHERE DateID>=@startdate AND DateID<=@endDate AND attendaceind=1 GROUP BY Studentid)SELECT nt.StudentID, @startdate AS startdate, @endDate AS endDate, nt.DaysAbs, wt.DaysInFROM notthere ntOUTER APPLY wasthere wt`I did this query for 11/2012 since there was a better mix of data. This will get you: `StudentID startdate endDate DaysAbs DaysIn----------- -------------------- -------------------- ----------- -----------1234 20121101 20121130 13 4` -- Alan BursteinRead this article for best practices on asking questions.Need to split a string? Try this (Jeff Moden)Need a pattern-based string spitter? Try this (Dwain Camps)My blog
Post #1416112
 Posted Tuesday, February 05, 2013 3:46 PM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 4:53 PM Points: 1,746, Visits: 2,553
 `SELECT Studentid, MIN(dateid) AS First_Absence_Date, MAX(dateid) AS Last_Absence_Date, COUNT(*) AS Total_Consecutive_AbsencesFROM ( SELECT Studentid, attendanceind, dateid, ROW_NUMBER() OVER (PARTITION BY Studentid ORDER BY dateid) - ROW_NUMBER() OVER (PARTITION BY Studentid, attendanceind ORDER BY dateid) AS Grp# FROM #x x --<<-- change to your table name) AS derivedWHERE attendanceind = 0GROUP BY Studentid, grp#HAVING COUNT(*) >= 15ORDER BY Studentid, First_Absence_Date` SQL DBA,SQL Server MVP('07, '08, '09)I'm not fat, I'm gravity challenged.
Post #1416135
 Posted Tuesday, February 05, 2013 5:49 PM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 6:57 PM Points: 3,590, Visits: 5,099
 Using Alan.B's set up data, here's another way (except using DATE data type):`-- SetupIF OBJECT_ID('tempdb..#x') IS NOT NULL DROP TABLE #x;CREATE TABLE #x ( Studentid int, DateID DATE unique, attendaceind bit)INSERT INTO #x SELECT 1234, '20121031', 1 UNION ALL SELECT 1234, '20121102', 1 UNION ALL SELECT 1234, '20121101', 1 UNION ALL SELECT 1234, '20121105', 0 UNION ALL SELECT 1234, '20121107', 1 UNION ALL SELECT 1234, '20121108', 1 UNION ALL SELECT 1234, '20121109', 0 UNION ALL SELECT 1234, '20121113', 0 UNION ALL SELECT 1234, '20121115', 0 UNION ALL SELECT 1234, '20121114', 0 UNION ALL SELECT 1234, '20121116', 0 UNION ALL SELECT 1234, '20121119', 0 UNION ALL SELECT 1234, '20121120', 0 UNION ALL SELECT 1234, '20121126', 0 UNION ALL SELECT 1234, '20121127', 0 UNION ALL SELECT 1234, '20121128', 0 UNION ALL SELECT 1234, '20121129', 0 UNION ALL SELECT 1234, '20121130', 0 UNION ALL SELECT 1234, '20121203', 0 UNION ALL SELECT 1234, '20121204', 0 UNION ALL SELECT 1234, '20121205', 0 UNION ALL SELECT 1234, '20121206', 0 UNION ALL SELECT 1234, '20121207', 0 UNION ALL SELECT 1234, '20121210', 0 UNION ALL SELECT 1234, '20121211', 0 UNION ALL SELECT 1234, '20121212', 0 UNION ALL SELECT 1234, '20121213', 0 UNION ALL SELECT 1234, '20121214', 1 UNION ALL SELECT 1234, '20121217', 1 UNION ALL SELECT 1234, '20121218', 1 UNION ALL SELECT 1234, '20121219', 1 GODECLARE @startdate DATE='20121114', @endDate DATE='20121213';--;WITH Absences AS ( SELECT StudentID, StartDT=MIN(DateID), EndDT=MAX(DateID) ,Absences=COUNT(1-attendaceind) FROM ( SELECT StudentID, DateID, attendaceind ,n=DATEDIFF(day, DateID ,ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY DateID)) FROM #x WHERE attendaceind = 0) a GROUP BY StudentID, n-- )--SELECT StudentID, StartDT=MIN(StartDT), EndDT=MAX(EndDT)-- ,Absences=SUM(Absences)--FROM (-- SELECT StudentID, StartDT, EndDT-- ,Absences=CASE WHEN StartDT >= @startdate AND EndDT <= @enddate THEN Absences-- WHEN StartDT < @startdate AND EndDT <= @enddate -- THEN DATEDIFF(day, @startdate, EndDT)-- WHEN StartDT >= @startdate AND EndDT > @enddate -- THEN DATEDIFF(day, StartDT, @enddate)-- ELSE DATEDIFF(day, @startdate, @enddate) END-- FROM Absences-- WHERE EndDT >= @startdate AND StartDT <= @enddate) a--GROUP BY StudentIDDROP TABLE #x`The initial code provided groups consecutive absences. If you uncomment the rest of the code it then groups again within the provided start and end dates summing up the absences that occur as part of a consecutive group of absences.When you run the first set of code, notice how one record starts at 11-13? The start date range of 11-14 requires the absence count to be adjusted in the final grouping and that is done with the CASE statement.All that's left is to add a HAVING clause to filter out only records appearing that are greater whatever your threshold is.I'm not sure which of the provided solutions will work best for you but surely there's something here you can work with. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1416182
 Posted Wednesday, February 06, 2013 10:14 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, February 25, 2014 10:48 AM Points: 16, Visits: 55
 First let me appolozige for posting the code in a clumsy manner, though I tried creating spaces, it did end up like that after I posted it. Will try to be clear next time I post.Sean Lange - Thank you!Alan.B -- Thank you for both the codes, I like the one with days out & days in .ScottPletcher -- Thanks a ton!! dwain.c -- Thanks for the code, I was able to get the code till the first grouping you mentioned , the commented portion of the code is where I am stuck.Thanks for the explanation.For now I am going with Scott Pletcher's code.Thank you all once again!!
Post #1416633

 Permissions