I am running SQL Server 2012
and I have a table named DateDimension
in my database.
My DateDimension
Table is a table with 2 columns: Column date
contain daily dates running from '2014-01-01
' to '2019-12-31
' and Column WeekGroup
is of nvarchar(100)
datatype.
An extract is given below:
date WeekGroup
2014-01-01 Wk 30 Dec 2013-05 Jan 2014
2014-01-02 Wk 30 Dec 2013-05 Jan 2014
2014-01-03 Wk 30 Dec 2013-05 Jan 2014
2014-01-04 Wk 30 Dec 2013-05 Jan 2014
2014-01-05 Wk 30 Dec 2013-05 Jan 2014
2014-01-06 Wk 06 Jan 2014-12 Jan 2014
...
I now have the following query:
SELECT
[date],
[WeekGroup],
RANK() OVER (ORDER BY [date] ASC) AS 'Rank'
FROM DateDimension
WHERE date BETWEEN '2014-01-01' AND '2019-12-31'
ORDER BY [date] ASC
My output is as follows:
date WeekGroup Rank
2014-01-01 Wk 30 Dec 2013-05 Jan 2014 1
2014-01-02 Wk 30 Dec 2013-05 Jan 2014 2
2014-01-03 Wk 30 Dec 2013-05 Jan 2014 3
2014-01-04 Wk 30 Dec 2013-05 Jan 2014 4
2014-01-05 Wk 30 Dec 2013-05 Jan 2014 5
2014-01-06 Wk 06 Jan 2014-12 Jan 2014 6
...
I would like to fine tune the above query so that I get the following output, with distinct
WeekGroup
and a corresponding Rank
:
WeekGroup Rank
Wk 30 Dec 2013-05 Jan 2014 1
Wk 06 Jan 2014-12 Jan 2014 2
...
How can I do this on an nvarchar
variable (WeekGroup
)?
Leave date
out and aggregate by the week group:
SELECT WeekGroup,
RANK() OVER (ORDER BY MIN([date]) ASC) AS [Rank]
FROM DateDimension
WHERE date BETWEEN '2014-01-01' AND '2019-12-31'
GROUP BY WeekGroup
ORDER BY MIN([date]) ASC;