February 5, 2013 at 1:10 pm
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 attendaceind
1234201210311
1234201211021
1234201211011
1234201211050
1234201211071
1234201211081
1234201211090
1234201211130
1234201211150
1234201211140
1234201211160
1234201211190
1234201211200
1234201211260
1234201211270
1234201211280
1234201211290
1234201211300
1234201212030
1234201212040
1234201212050
1234201212060
1234201212070
1234201212100
1234201212110
1234201212120
1234201212130
1234201212141
1234201212171
1234201212181
1234201212191
-------------------
1= Present ; 0 = absent
Need to count 20 consecutive school days absent.
i tried to group the days absent. this is what I get
Studentid startdateenddatedaysabsentdayweek
123420120904201209074436
1234201209142012091411437
1234201210222012102212243
123420121105201211051545
123420121109201211091945
1234201211132012111641346
1234201211192012112021947
1234201211262012113052648
123420121203201212075349
1234201212102012121341050
123420130125201301251254
Starting from 20121113 untill 20121213 , these are all consecutive school days(including weekends/holidays)
But what I want is ,
Desired result
studentid startdate enddate daysabsent
1234 20121113 20121213 20
All your help is greatly appreciated.
Thanks in advance!!
awaiting help !!
February 5, 2013 at 2:00 pm
It seems that a calendar table would help you considerably. You can read about them here. 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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 2:29 pm
This should do the trick:
-- Setup
IF 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
GO
DECLARE @startdate bigint=20121113,
@endDate bigint=20121213;
;WITH answer AS
(SELECTStudentID, 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, DaysAbs
FROM answer
GO
-- Itzik Ben-Gan 2001
February 5, 2013 at 2:42 pm
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
(SELECTStudentID, COUNT(*) AS DaysAbs
FROM #x
WHERE DateID>=@startdate AND DateID<=@endDate
AND attendaceind=0
GROUP BY Studentid
),
wasthere AS
(SELECTStudentID, 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.DaysIn
FROM notthere nt
OUTER 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
-- Itzik Ben-Gan 2001
February 5, 2013 at 3:46 pm
SELECT
Studentid,
MIN(dateid) AS First_Absence_Date,
MAX(dateid) AS Last_Absence_Date,
COUNT(*) AS Total_Consecutive_Absences
FROM (
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 derived
WHERE
attendanceind = 0
GROUP BY
Studentid, grp#
HAVING
COUNT(*) >= 15
ORDER BY
Studentid, First_Absence_Date
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2013 at 5:49 pm
Using Alan.B's set up data, here's another way (except using DATE data type):
-- Setup
IF 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
GO
DECLARE @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 StudentID
DROP 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2013 at 10:14 am
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!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply