InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 13:25:54 GMT20http://www.sqlservercentral.com/Forums/Topic1416057-392-1.aspxFirst 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!! Wed, 06 Feb 2013 10:14:12 GMTbunniehttp://www.sqlservercentral.com/Forums/Topic1416057-392-1.aspxUsing Alan.B's set up data, here's another way (except using DATE data type):[code="sql"]-- 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[/code]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.Tue, 05 Feb 2013 17:49:46 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1416057-392-1.aspx[code="sql"]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[/code]Tue, 05 Feb 2013 15:46:47 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1416057-392-1.aspxUsing the sample code I created before you can also get days in/days not in using this:[code="sql"]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[/code]I did this query for 11/2012 since there was a better mix of data. This will get you: [code="plain"]StudentID startdate endDate DaysAbs DaysIn----------- -------------------- -------------------- ----------- -----------1234 20121101 20121130 13 4[/code]Tue, 05 Feb 2013 14:42:05 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1416057-392-1.aspxThis should do the trick: [code="sql"]-- 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[/code]Tue, 05 Feb 2013 14:29:07 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1416057-392-1.aspxIt seems that a calendar table would help you considerably. You can read about them here. [url=http://www.sqlservercentral.com/articles/T-SQL/70482/]http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]If you need help with the query please first read the article in my signature about best practices when posting questions.Tue, 05 Feb 2013 14:00:27 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1416057-392-1.aspxHello,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 , [b]Desired result[/b]studentid startdate enddate daysabsent1234 20121113 20121213 20All your help is greatly appreciated.Thanks in advance!!awaiting help !!Tue, 05 Feb 2013 13:10:56 GMTbunnie