Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

consecutive days count irrespective of weekends, holidays Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 1:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 8, 2014 9:47 AM
Points: 16, Visits: 61
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
1234 20121031 1
1234 20121102 1
1234 20121101 1
1234 20121105 0
1234 20121107 1
1234 20121108 1
1234 20121109 0
1234 20121113 0
1234 20121115 0
1234 20121114 0
1234 20121116 0
1234 20121119 0
1234 20121120 0
1234 20121126 0
1234 20121127 0
1234 20121128 0
1234 20121129 0
1234 20121130 0
1234 20121203 0
1234 20121204 0
1234 20121205 0
1234 20121206 0
1234 20121207 0
1234 20121210 0
1234 20121211 0
1234 20121212 0
1234 20121213 0
1234 20121214 1
1234 20121217 1
1234 20121218 1
1234 20121219 1
-------------------

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 startdate enddate daysabsent day week
1234 20120904 20120907 4 4 36
1234 20120914 20120914 1 14 37
1234 20121022 20121022 1 22 43
1234 20121105 20121105 1 5 45
1234 20121109 20121109 1 9 45
1234 20121113 20121116 4 13 46
1234 20121119 20121120 2 19 47
1234 20121126 20121130 5 26 48
1234 20121203 20121207 5 3 49
1234 20121210 20121213 4 10 50
1234 20130125 20130125 1 25 4


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 !!
Post #1416057
Posted Tuesday, February 5, 2013 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 13,095, Visits: 11,927
It seems that a calendar table would help you considerably. You can read about them here. http://www.sqlservercentral.com/articles/T-SQL/70482/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1416089
Posted Tuesday, February 5, 2013 2:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 556, Visits: 2,581
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
( 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, DaysAbs
FROM answer
GO



-- Alan Burstein



Read 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 5, 2013 2:42 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 556, Visits: 2,581
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.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



-- Alan Burstein



Read 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 5, 2013 3:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 1,969, Visits: 2,906
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1416135
Posted Tuesday, February 5, 2013 5:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 3,610, Visits: 5,223
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 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 6, 2013 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 8, 2014 9:47 AM
Points: 16, Visits: 61
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse