Get all StudentIds for each consecutive date
-
I have a table wherein each student submits their assignment.
I need to write a query to get all the students who submitted their assignments each day.
So from the table shown, StudentId 1 and 2 submitted their assignments on all 3 days.
The output should be 1 and 2.
Any hint will be helpful.
-
An outer join to some form of a calendar table or sequence generator along with a count of days vs students appearing in the table should do the trick.
If you want a coded answer, use one of many methods to provide readily consumable data, please. The article at the first link in my signature line below identifies one method. At the very least, make the data copyable instead of using a graphic. Thanks for helping us help you. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think all that is needed for this is a GROUP BY and HAVING count(*) = 3. If you need it to be dynamic - then you need to determine the total number of submission dates and use that in the HAVING clause.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
You do need an outer join with the dates students are expected to have turn-ins in order to accommodate a very naughty class that has conspired with each other where no one turns in an assignment.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
As requested, I am adding the sql for creating the data.
Apologies for not doing it.
declare @student table
(
StudentId int
,SubmissionDate date
)
insert into @student(StudentId,SubmissionDate) values(1,'10/01/2018')
insert into @student(StudentId,SubmissionDate) values(2,'10/01/2018')
insert into @student(StudentId,SubmissionDate) values(3,'10/01/2018')
insert into @student(StudentId,SubmissionDate) values(4,'10/01/2018')
insert into @student(StudentId,SubmissionDate) values(1,'10/02/2018')
insert into @student(StudentId,SubmissionDate) values(2,'10/02/2018')
insert into @student(StudentId,SubmissionDate) values(3,'10/02/2018')
insert into @student(StudentId,SubmissionDate) values(1,'10/03/2018')
insert into @student(StudentId,SubmissionDate) values(2,'10/03/2018')
insert into @student(StudentId,SubmissionDate) values(4,'10/03/2018')
select * from @student - Jeff Moden wrote:
You do need an outer join with the dates students are expected to have turn-ins in order to accommodate a very naughty class that has conspired with each other where no one turns in an assignment.
You don't need that to answer *this* question. I agree that you would need that to identify which students did not submit the assignments due and which dates were missed.
But to just identify the students that did submit on every day - all you need is the number of days to match. Any student that has a distinct count for each day equal to the number of days being checked, submitted the assignment on each day.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:Jeff Moden wrote:
But to just identify the students that did submit on every day - all you need is the number of days to match. Any student that has a distinct count for each day equal to the number of days being checked, submitted the assignment on each day.
EDIT... I pulled my answer here... Jeffrey Williams is correct... you don't need a sequence of dates to solve the question that was asked.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yeah, but in the actual question the OP asks for those students that turned in the assignment for all dates.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:
Yeah, but in the actual question the OP asks for those students that turned in the assignment for all dates.
Sorry... I was in the process of changing my reply when you replied and I don't have the original anymore but you do.
Anyway, I agree that to answer only the precise question that was asked, the solution code would not require a series of dates.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok, Jeffrey... the Op posted some good test data. I'll give you the honors for providing the solution.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
jignesh209 ,
Jeffrey must've gotten busy. Apologies for the delay. Here's the code that will answer the question that you asked.
SELECT StudentId
FROM #Student
GROUP BY StudentID
HAVING COUNT(*) = 3
;Here's the result set from that code using the given test data...
Are you sure that's all you'd want to know from this data?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-- swePeso solution
WITH cteData(StudentId, delta, rnk)
AS (
SELECT StudentId,
DATEDIFF(DAY, '19000101', SubmissionDate),
ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY SubmissionDate) AS rnk
FROM @student
)
SELECT DISTINCT StudentId
FROM cteData
GROUP BY StudentId,
delta - rnk
HAVING COUNT(*) >= 3;N 56°04'39.16"
E 12°55'05.25" - SwePeso wrote:
-- swePeso solution
WITH cteData(StudentId, delta, rnk)
AS (
SELECT StudentId,
DATEDIFF(DAY, '19000101', SubmissionDate),
ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY SubmissionDate) AS rnk
FROM @student
)
SELECT DISTINCT StudentId
FROM cteData
GROUP BY StudentId,
delta - rnk
HAVING COUNT(*) >= 3;Now there's a different kind of embedded date table based on Date Serial Numbers. Nicely done Peter.
You're probably going to get chastised for not needing such a thing, as well. 😀 😀 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just in case anyone wonders what I'm going on about, the original question is a "data analysis" question.
Yep. It can be solved without using a series of dates (and Peter's solution makes a "hidden" sequencing of dates). But just answering the question only solves one problem and, when it comes to "data analysis", I've found the you need to leave the door open for when whomever the code is created for says "Great... now can you show me the students that don't have consecutive completions and can you show me any days that are missing"? And how about when the next email the Op gets that says "Great... the table I'm working with actually has thousands of rows across multiple a lot more than just 3 dates..." ?
There are also other things to consider. The original post has submittal dates with dates AND times indicating something other than the DATE datatype. While the posted data has no times, should you actually trust that or help protect the users from what happens if and when times are suddenly involved because of currently unknown requirements. That's happened to a whole lot of people even after they asked the question of "Will times ever be included?" and the response was "No, never". Peter's code does handle that issue nicely because of the the sequence based on dates that he uses. The code I posted out of frustration about the insistence that no date sequence is need will fail if times are involved.
So... yeah... it can be done without a date or other sequence... why would anyone cripple the possibilities that way? And, this isn't Stack Overflow where such possibilities are suppressed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. This would make a great DA whiteboard interview question... if the candidate only comes up with the solution to the exact problem, you might want to consider someone else. 😉
The question I always ask myself in regards to the person asking the question in real life for such analysis questions is the same question I asked in the "simple" coded response I made... "Are you sure that's all you'd want to know from this data?"
The answer is usually "No... can you also do the following..."?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply