November 12, 2016 at 12:30 pm
I have a truantday table that has students who has absent, each row is one student, one truant day starting from beginning of school year to current date.
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 the any 30 days that starting from the student has first truant date and the last truant date in this school year, during this period, any 30 days.
Thanks
November 12, 2016 at 12:54 pm
sqlfriends (11/12/2016)
I have a truantday table that has students who has absent, each row is one student, one truant day starting from beginning of school year to current date.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 the any 30 days that starting from the student has first truant date and the last truant date in this school year, during this period, any 30 days.
Thanks
With nearly 2,000 points, you should know how to ask questions here by now.
Provide DDL, sample data (as INSERT statements) and desired results, please.
November 12, 2016 at 1:41 pm
I agree with Phil.
But here is some psuedocode that may help
select student
from truantstudents
where ts.dayoutofschool > (write a predicate for 30 days prior to today)
group by student
having count(*) in (2,3)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2016 at 3:12 pm
You might wants to select the number of absences and also handle the case where a student has more than 3 absences. According to your original post, students with 5 absences wouldn't appear on the report. If students are going to be disciplined for this, you might need to generate a list of dates they were absent.
November 14, 2016 at 9:50 am
Isn't this essentially the same question that you had in Recursive CTE performance improvement. You received an answer to this question there.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 14, 2016 at 10:02 am
drew.allen (11/14/2016)
Isn't this essentially the same question that you had in Recursive CTE performance improvement. You received an answer to this question there.Drew
Just wonder if the OP didn't understand those fine answers?
😎
OP, care to elaborate on what exactly is the problem and what the answers from the previous thread are missing?
November 14, 2016 at 11:22 am
Yes , that was posted by me.
But I donot like to use recursive CTE, it is slow.
And I wonder if there are any other suggestions.
Thanks
Here is the table definition:
CREATE TABLE [dbo].[TruantDay](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[StudentId] [INT] NULL,
[SchoolId] [INT] NOT NULL,
[SchoolYearId] [INT] NOT NULL,
[Att_Date] [DATETIME] NOT NULL,
[IsAbsent] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IA_Def] DEFAULT ((0)),
[IsCleared] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IC_Def] DEFAULT ((0)),
[ChangeDate] [DATETIME] NULL,
[ChangedBy] [VARCHAR](40) NULL,
[CreateDate] [DATETIME] NOT NULL,
[CreatedBy] [VARCHAR](40) NOT NULL
)
November 14, 2016 at 11:51 am
sqlfriends (11/14/2016)
Yes , that was posted by me.But I donot like to use recursive CTE, it is slow.
And I wonder if there are any other suggestions.
Thanks
Here is the table definition:
CREATE TABLE [dbo].[TruantDay](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[StudentId] [INT] NULL,
[SchoolId] [INT] NOT NULL,
[SchoolYearId] [INT] NOT NULL,
[Att_Date] [DATETIME] NOT NULL,
[IsAbsent] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IA_Def] DEFAULT ((0)),
[IsCleared] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IC_Def] DEFAULT ((0)),
[ChangeDate] [DATETIME] NULL,
[ChangedBy] [VARCHAR](40) NULL,
[CreateDate] [DATETIME] NOT NULL,
[CreatedBy] [VARCHAR](40) NOT NULL
)
Let me paraphrase: you didn't like the suggested answers, so you posted the question again, hoping for something different.
This is just plain rude.
If you don't like what people are saying in thread (a), have a rational discussion about it in thread (a), rather than starting thread (b) and wasting others' time.
November 14, 2016 at 11:52 am
sqlfriends (11/14/2016)
Yes , that was posted by me.But I donot like to use recursive CTE, it is slow.
And I wonder if there are any other suggestions.
Thanks
Here is the table definition:
CREATE TABLE [dbo].[TruantDay](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[StudentId] [INT] NULL,
[SchoolId] [INT] NOT NULL,
[SchoolYearId] [INT] NOT NULL,
[Att_Date] [DATETIME] NOT NULL,
[IsAbsent] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IA_Def] DEFAULT ((0)),
[IsCleared] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IC_Def] DEFAULT ((0)),
[ChangeDate] [DATETIME] NULL,
[ChangedBy] [VARCHAR](40) NULL,
[CreateDate] [DATETIME] NOT NULL,
[CreatedBy] [VARCHAR](40) NOT NULL
)
could we have some sample data and expected results as well please......if you could cover off all permutations in your sample data then that will help us.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 14, 2016 at 12:02 pm
sqlfriends (11/14/2016)
Yes , that was posted by me.But I donot like to use recursive CTE, it is slow.
And I wonder if there are any other suggestions.
None of the suggestions used a recursive CTE. There was one suggestion with a GROUP BY and another with a regular CTE.
Also, sometimes recursive CTEs are the best solutions. Don't assume that just because it uses a recursive CTE that it will perform badly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 14, 2016 at 12:02 pm
Also my question is a little different from previous post, donot want to make confusion, so start a new one.
I will create some sample data
November 14, 2016 at 12:03 pm
J Livingston SQL (11/14/2016)
sqlfriends (11/14/2016)
Yes , that was posted by me.But I donot like to use recursive CTE, it is slow.
And I wonder if there are any other suggestions.
Thanks
Here is the table definition:
CREATE TABLE [dbo].[TruantDay](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[StudentId] [INT] NULL,
[SchoolId] [INT] NOT NULL,
[SchoolYearId] [INT] NOT NULL,
[Att_Date] [DATETIME] NOT NULL,
[IsAbsent] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IA_Def] DEFAULT ((0)),
[IsCleared] [BIT] NOT NULL CONSTRAINT [col_TruantDay_IC_Def] DEFAULT ((0)),
[ChangeDate] [DATETIME] NULL,
[ChangedBy] [VARCHAR](40) NULL,
[CreateDate] [DATETIME] NOT NULL,
[CreatedBy] [VARCHAR](40) NOT NULL
)
could we have some sample data and expected results as well please......if you could cover off all permutations in your sample data then that will help us.
Considering that I asked for that almost 20 days ago, I wouldn't expect getting useful sample data and expected results.
The OP mentions not liking the recursive CTE, but none of the answers on the other thread used rCTEs. Also, there was no detail on why the posted solutions didn't work.
November 14, 2016 at 12:08 pm
sqlfriends (11/14/2016)
Also my question is a little different from previous post, donot want to make confusion, so start a new one.I will create some sample data
I realize that this question was slightly different, but you should be able to easily tweak one of the queries in other thread to come up with your answer. That's why I included the word "essentially" in my initial response.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 14, 2016 at 12:37 pm
I attached the sample data. So far we only consider school year 2016.
November 14, 2016 at 12:58 pm
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply