Count (Distinct ([value)) OVER (Partition by) in SQL Server 2008

Question

I wrote this and successfully executed in Oracle

COUNT (DISTINCT APEC.COURSE_CODE) OVER ( PARTITION BY s.REGISTRATION_NUMBER ,APEC.APE_ID ,COV.ACADEMIC_SESSION ) APE_COURSES_PER_ACADEMIC_YEAR 

I am trying to achieve the same result in SQL Server (our source database uses Oracle, but our warehouse uses SQL Server).

I know that the distinguishing element is not supported by window functions in SQL Server 2008 - can anyone suggest an alternative?

Answer 1

Here is what I recently met. I got this from post . So far, this works very well for me.

 DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields ASC) + DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields DESC) - 1 AS DistinctCount 

Answer 2

Alas, you cannot do count(distinct) over in SQL Server. You can do this with a subquery. The idea is to list the values ​​in each course code (and obey other break conditions). Then just count the values ​​where the sequence number is 1:

 select sum(case when cc_seqnum = 1 then 1 else 0 end) as APE_COURSES_PER_ACADEMIC_YEAR from (select . . . , row_number () OVER (PARTITION BY s.REGISTRATION_NUMBER, APEC.APE_ID, COV.ACADEMIC_SESSION, APEC.COURSE_CODE ORDER BY (SELECT NULL) ) as cc_seqnum from . . . ) t 

You have a complex request. I would suggest replacing count(distinct) with row_number() and make your current request a subquery or CTE for the final request.