reset counter by year / month / day

Question

Post reply

reset counter by year / month / day

DBayliss

Mr or Mrs. 500

Points: 530
More actions
June 20, 2016 at 7:09 am

#311199

Hi all,
is there a way to set a counter in a script that resets every year/month that counts every change in day? see below or enclosed example. I hope this makes sense.
Any help would be greatly appreciated.
Date Counter
01/04/20161
01/04/20161
08/04/20162
08/04/20162
13/04/20163
13/04/20163
14/04/20164
15/04/20165
18/04/20166
--------------------------------------
04/05/20161
04/05/20161
07/05/20162
-------------------------------------
02/06/20161
10/06/20162
13/06/20163
14/06/20164
15/06/20165
15/06/20165
16/06/20166
17/06/20167
20/06/20168

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

This?

DENSE_RANK() OVER(PARTITION BY MONTH(yourcolumn) ORDER BY yourcolumn)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DBayliss Mr or Mrs. 500 Points: 530 More actions · Answer 2

DBayliss

Mr or Mrs. 500

Points: 530

June 20, 2016 at 7:24 am

#1885839

Perfect - thank you very much.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Don't forget to add the year as well.

An alternative to get both in a single function is to convert them to a string: CONVERT(char(6), yourDateColumn, 112)

I'm not sure which approach would be better as date functions are very fast.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 77014 More actions · Answer 5

Luis Cazares (6/20/2016)
Don't forget to add the year as well.
An alternative to get both in a single function is to convert them to a string: CONVERT(char(6), yourDateColumn, 112)
I'm not sure which approach would be better as date functions are very fast.

I actually prefer grouping on the first of the month. (End of month would also work, especially with the EOMONTH() function that was added in SQL2012(?)).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA