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 5, 2013 1:10 PM
 Grasshopper Group: General Forum Members Last Login: Friday, December 19, 2014 9:23 AM Points: 18, Visits: 66
 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 5, 2013 2:00 PM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:44 PM Points: 16,134, Visits: 16,835
Post #1416089
 Posted Tuesday, February 5, 2013 2:29 PM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 9:42 PM Points: 2,153, Visits: 7,251
 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 BursteinBest practices for getting help on SQLServerCentralNeed to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)Need a pattern-based splitter? Try PatternSplitCMNeed to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K. "I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Post #1416106
 Posted Tuesday, February 5, 2013 2:42 PM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 9:42 PM Points: 2,153, Visits: 7,251
 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 BursteinBest practices for getting help on SQLServerCentralNeed to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)Need a pattern-based splitter? Try PatternSplitCMNeed to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K. "I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Post #1416112
 Posted Tuesday, February 5, 2013 3:46 PM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 3:56 PM Points: 3,852, Visits: 6,566
 `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)Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #1416135
 Posted Tuesday, February 5, 2013 5:49 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1416182
 Posted Wednesday, February 6, 2013 10:14 AM
 Grasshopper Group: General Forum Members Last Login: Friday, December 19, 2014 9:23 AM Points: 18, Visits: 66
 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