• Status: Solved
• Priority: Medium
• Security: Public
• Views: 334

# Count Question

Given Table structure:
StudentID, AttDate, AttCode, PeriodNum

I'm looking for T-SQL to return the count of the number of days for each distinct AttCode where it was used on a day for more than 3 periods.

So.....
StudentID   AttDate     AttCode   PeriodNum
12345         4/7/2013   ACT          1
12345         4/7/2013   ACT          2
12345         4/9/2013   PE            1
12345         4/9/2013   PE            2
12345         4/9/2013   PE            3
12345         4/9/2013   PE            4
12345         4/9/2013   PE            5
12345         4/10/2013   ACT            2
12345         4/10/2013   ACT            3
12345         4/10/2013   ACT            4
12345         4/10/2013   ACT            5
12345         4/14/2013   PE            3
12345         4/14/2013   PE            4
12345         4/14/2013   PE            5
12345         4/14/2013   PE            6

The results I'd like would be:
AttCode     TotalDays
ACT            1
PE              2

The first instance of ACT wouldn't count since there were only two rows for that day for that code.

I'm passing in the StudentID as part of the procedures WHERE clause so it doesn't need to be returned.

js
0
jswanberg
1 Solution

Whether or not this is for a classroom assignment, would you please add what you have already tried, and the results?
0

Commented:
Row_number () over ()
0

;With c as (
SELECT AttDate,ATTCode, rn = ROW_NUMBER() over ( partition by AttDate, AttCode Order by PerodNum)
From YourTable
)

SELECT AttCode, Count(Distinct AttCode) cnt
FROM c
WHERE rn > 3
GROUP BY AttDate, AttCode
0

Commented:
Taking a leap of faith this isn't homework, another version:

select attCode, count(*) from (
select attCode
from table
group by attcode, attdate
having count(*) > 3
)
group by attCode
/
0

Author Commented:
Worked great - knew it was going to be a simple nested select.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.