Partition Function COUNT() OVER possible using DISTINCT

by

There is a very simple solution using dense_rank()

dense_rank() over (partition by [Mth] order by [UserAccountKey]) 
+ dense_rank() over (partition by [Mth] order by [UserAccountKey] desc) 
- 1

This will give you exactly what you were asking for: The number of distinct UserAccountKeys within each month.

More Related Contents:

  1. Calculating distance between two points (Latitude, Longitude)
  2. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  3. T-SQL split string based on delimiter
  4. WHERE Clause vs ON when using JOIN
  5. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  6. Using IF ELSE statement based on Count to execute different Insert statements
  7. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  8. Is it possible to write single query on two table which are not connected to each other?
  9. How to create a specific SQL Server stored procedure? [closed]
  10. Should I use != or for not equal in T-SQL?
  11. How to update two tables in one statement in SQL Server 2005?
  12. Create a date from day month and year with T-SQL
  13. How should I pass a table name into a stored proc?
  14. Creating an index on a table variable
  15. Parse comma-separated string to make IN List of strings in the Where clause
  16. Pivot using SQL Server 2000
  17. Sequence vs identity
  18. Cannot use UPDATE with OUTPUT clause when a trigger is on the table
  19. How to update and order by using ms sql
  20. How to store image in SQL Server database tables column [duplicate]
  21. Reference an alias elsewhere in the SELECT list
  22. Can I set ignore_dup_key on for a primary key?
  23. Count Returning blank instead of 0
  24. SQL Server replace, remove all after certain character
  25. How to find column names for all tables in all databases in SQL Server
  26. Inserting rows into a table with one IDENTITY column only [duplicate]
  27. SQL Row_Number() function in Where Clause
  28. When using GETDATE() in many places, is it better to use a variable?
  29. Passing dynamic order by in stored procedure
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment