Frequently I need to do something in SQL Server that I just don't know how to do properly.
Basically, if I have a table #sample
that is like this:
DEPARTMENT | DATE |
---|---|
ACCOUNTING | A |
FINANCE | A |
PAYROLL | A |
ACCOUNTING | B |
PAYROLL | A |
FINANCE | A |
PAYROLL | B |
PAYROLL | C |
I need to query that table to get an output that is this:
DEPARTMENT | DATE | DUPECHECK |
---|---|---|
ACCOUNTING | A | 1 |
ACCOUNTING | B | 2 |
FINANCE | A | 1 |
FINANCE | A | 1 |
PAYROLL | A | 1 |
PAYROLL | A | 1 |
PAYROLL | B | 2 |
PAYROLL | C | 3 |
In Oracle Hyperion Brio at my old job I used to create a column that value 1 for every row and writing a function Cume on that column with a break value of the column I needed to count.
In Excel, I could create a concatenated column that has my break values, and then do a countif(concat:concat,concat1)
copy that formula down.
I feel like it should be something similar to this, but that's not it at all:
ROW_NUMBER() OVER (PARTITION BY #sample.Department ORDER BY #sample.Date) DUPECHECK
Help me out?
CodePudding user response:
You can use the dense_rank().
DENSE_RANK() OVER(PARTITION BY #sample.Department ORDER BY #sample.Date)DUPECHECK