3

I have a date dimension table in which I need to add a new column in which I define the iteration of the day of the week within the month (2 for the second Mon/Tue/Wed/Thu/Fri/Sat/Sun etc).

Is it possible to do this be making calculations solely on the date column of the table, which is of type 'date'?

5

It's actually surprisingly easy using DAY(), as 1-7 will be 1, 8-14 will be 2, 15-21 will be 3, 22-28 will be 4 and 29-31 will be 5. Doesn't matter what the name of the day is, that will always hold true.

select case when DAY(yourdate) <=7  then 1
            when DAY(yourdate) <=14 then 2
            when DAY(yourdate) <=21 then 3
            when DAY(yourdate) <=28 then 4
            else 5
        end as occurance
from yourtable; 

(case short-circuits)

|improve this answer|||||
4

Alternatively you could calculate the result using a formula:

SELECT
  (DAY(YourDate) + 6) / 7 AS Iteration
FROM
  dbo.YourTable
;

In cases where both operands of a division operation are integers, the result is also an integer, and a rounded down one at that.

So, DAY(YourDate) = 1 will give you 1, because

(1 + 6) / 7 = 7 / 7 = 1

A 2 will yield

 (2 + 6) / 7 = 8 / 7 = 1

– again 1.

If it is 7, you still get 1:

(7 + 6) / 7 = 13 / 7 = 1

But 8 will result in 2:

(8 + 6) / 7 = 14 / 7 = 2

And so on.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.