Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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
Asked:
jswanberg
1 Solution
 
DavidSenior Oracle Database AdministratorCommented:
Whether or not this is for a classroom assignment, would you please add what you have already tried, and the results?
0
 
jogosCommented:
Row_number () over ()
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
;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
 
slightwv (䄆 Netminder) 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
 
jswanbergAuthor Commented:
Worked great - knew it was going to be a simple nested select.  Thanks!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now