November 14, 2016 at 1:02 pm
TheSQLGuru (11/14/2016)
sqlfriends (11/14/2016)
I attached the sample data. So far we only consider school year 2016.I'm pretty sure you can't put N'NULL' into the ChangeDate columns. Not sure what you used to generate those inserts but it needs some work.
You still need to provide expected outputs for the given rows you provided.
I noticed the string NULL values and just closed the file.
November 14, 2016 at 1:52 pm
Sorry, reattach it.
Thanks
November 14, 2016 at 8:05 pm
You still need to provide expected outputs for the given rows you provided.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 15, 2016 at 4:43 pm
I would like to find out in every past 30 days(a month) the students has 2 days absent, or 3 days absent.
These are any sliding 30 days not only the most recent 30 days that starting from the student has first truant date and the last truant date in this school year.
November 15, 2016 at 5:24 pm
TheSQLGuru (11/14/2016)
You still need to provide expected outputs for the given rows you provided.
^^^ this.
It's specific data, not more waffle, that is being requested.
November 16, 2016 at 8:29 am
sqlfriends (11/15/2016)
I would like to find out in every past 30 days(a month) the students has 2 days absent, or 3 days absent.These are any sliding 30 days not only the most recent 30 days that starting from the student has first truant date and the last truant date in this school year.
DROP TABLE #Students, #SchoolCalendar, #TruantDay
CREATE TABLE #Students (
StudentID INT NOT NULL
)
INSERT #Students(StudentID)
VALUES(1), (2)
CREATE TABLE #SchoolCalendar(
SchoolDay DATE PRIMARY KEY
)
;
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)
)
, cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E a, E b, E c
)
INSERT #SchoolCalendar(SchoolDay)
SELECT DATEADD(DAY, n-1, '20151001')
FROM cteTally
CREATE TABLE #TruantDay(
StudentId int,
Att_Date date,
IsAbsent bit,
IsCleared bit
);
INSERT INTO #TruantDay
VALUES
( 1, '20161001', 1, 0),
( 1, '20161011', 1, 0),
( 1, '20161012', 1, 0),
( 1, '20161015', 1, 0),
( 1, '20161021', 1, 0),
( 1, '20161022', 1, 0),
( 1, '20161025', 1, 0)
INSERT INTO #TruantDay
VALUES
( 2, '20151001', 1, 0),
( 2, '20151011', 1, 0),
( 2, '20151012', 1, 0),
( 2, '20151015', 1, 0),
( 2, '20151021', 1, 0),
( 2, '20151222', 1, 0),
( 2, '20151225', 1, 0);
CREATE CLUSTERED INDEX PK_TruantDay ON #TruantDay(StudentId, Att_Date)
;
WITH absence_counts AS (
SELECT s.StudentID, sc.SchoolDay, td.Att_Date, COUNT(td.Att_Date) OVER(PARTITION BY s.StudentID ORDER BY sc.SchoolDay ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS running_absences
FROM #Students s
CROSS JOIN #SchoolCalendar sc
LEFT OUTER JOIN #TruantDay td
ON s.StudentID = td.StudentId
AND sc.SchoolDay = td.Att_Date
AND td.IsAbsent = 1
AND td.IsCleared = 0
)
SELECT StudentID, Att_Date, running_absences
FROM absence_counts
WHERE absence_counts.running_absences BETWEEN 2 AND 3
AND Att_Date IS NOT NULL
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2016 at 12:26 pm
Thank you Drew.
Let me check into it.
November 17, 2016 at 3:20 pm
It looks the code: PARTITION BY s.StudentID ORDER BY sc.SchoolDay ROWS BETWEEN 30 PRECEDING AND CURRENT ROW
and a cross join with school calendar table really does the work for what I want.
I will play with it a little more, not used this preceding and current row before.
Thanks much
November 18, 2016 at 6:56 am
hi
just what is the last date of 30 day if not
take max(date of the month) let it be LastDateOfTrunt
select *
from stud a inner join stud b
on a.studid=b.studid and
truntday between truntday and LastDateOfTrunt
where count(*) in (2,3)
if give the data for every column i can get u in more detail
November 18, 2016 at 10:12 am
I do attach a data file for truantday in the attachment of the post counting back the eighth one.
Yes, include the last date of truant is helpful too.
November 22, 2016 at 3:49 pm
If I don't have a schoolCalendar table, but Truantday table has all absent days of current school year for students, and I also know a start date and end date of a school year, is that still possible to use the windows function: over () partitition by... order by... row...between...and ...
to count absentdays of any rolling 30 day period
Thanks,
November 22, 2016 at 3:57 pm
It should work. The main reason for using the Calendar is that you're guaranteed no gaps in the dates. It also gives you a handy table to group on. You could use a table-valued function for your calendar, if you wanted to.
November 22, 2016 at 4:04 pm
But each schoolyear we will have to make a new calendar table.
I am wondering since I know for each student their first and last absent school day, there should a way that count absent days every 30 day period from the last absent day, any 30 period back to the first date of his absent date.
But I don't know how.
November 22, 2016 at 5:09 pm
So you create a Calendar table to have as many days as you need, and when the new year rolls around, you add more dates. Sure, you have to specify which days are holidays, but that's a one-time thing. Use a real table... nothing doing. I guess I don't see what the big deal is. This stuff is trivial.
November 23, 2016 at 7:35 am
Also, I haven't seen anything in this topic that addresses whether the "30 days" means 30 school days or 30 calendar days. Those two things are quite different, ... however, both can benefit from a calendar table, as you still need to know if a given date is a school day or not. Until you "table-ize" that information, you're not going to get very far very easily. I had one scenario a number of years ago where I discovered that holidays were consistently computed the exact same way every year, so I could generate a holiday list simply by being supplied the year. However, school years tend to vary in terms of starting and ending dates, and there are not just holidays, but also snow days and "in service days", which have to get married into the mix as they occur. This adds a little more meat to the bone here, but as the effect of a holiday or in-service day or snow day is usually the same, it doesn't necessarily matter that much which of them occurs on a given day. Either way, you have to have a mechanism to recognize these as non-school days, ... especially if the 30 days being bandied about means school days as opposed to calendar days. Creating a calendar table is not exactly rocket science, and if you'd like a really good article on that kind of thing, you should read Jeff Moden's article on the Tally table. It's one of the easiest ways to generate calendar-based data that I know of, and using CTE's to generate the data dynamically, given a starting date and/or ending date is pretty easy. No matter what you do, you'll need at least some kind of table of non-school weekdays, and a means of easily adding days to that "list" over the course of a given school year.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply