Calculating Previous and Next Week

Question

Calculating Previous and Next Week

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
June 18, 2020 at 7:32 am

#3764097
Hi ,I do have Year week as below as Integer and I would like to know if we have any best way to calculate the Previous Year Week and Next Year Week . Do we have any formula to calculate it . Thanks in Advance 🙂
Year Week : 201952 , 201953 , 202001 , 202002 , 202003 ,............202053 , 202101 , 202102...
- This topic was modified 4 years, 5 months ago by Steve Dell.

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

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

ktflash Default port Points: 1400 More actions · Answer 1

Something like this?

create table #TEMP (Date int, Value int)

insert into #TEMP (Date,Value) VALUES
(201901,201901)
,(201902,201902)
,(201903,201903)
,(201904,201904)
,(201905,201905)
,(202001,202001)
,(202002,202002)
,(202003,202003)
,(202004,202004)
,(202005,202005)
,(202101,202101) 
,(202102,202102)
,(202103,202103)
,(202104,202104)
,(202105,202105)


select a.Date,a.Value,b.Value
from #TEMP A
     inner join #TEMP B on b.Date = (a.Date-100)

This reply was modified 4 years, 5 months ago by ktflash.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 2

Hi, The query you have sent me will give Last Year Week Number , I need something like .. if today's week is 202001 then i need previous week as 201953 and Next week as 202002

This reply was modified 4 years, 5 months ago by Steve Dell.

ktflash Default port Points: 1400 More actions · Answer 3

So something like this?

This gets the value from the previous week, if you want the Date you would have to replace the LAG(DATE)

select Date,Value,Previous=LAG(VALUE)OVER (ORDER BY DATE)
from #TEMP

This reply was modified 4 years, 5 months ago by ktflash.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

This is where a calendar table would come in very handy, the one I use is from the below link

https://www.sqlservercentral.com/scripts/date-calendar

You would then want to add an additional index on the year_week column, then do something with lead and lag

;with cte1 as
(select distinct year_week from date_calendar)
,cte2 as
(select year_week as CurrentWeek,
lag(year_week,1,0) over (order by year_week) AS PreviousYearWeek,
lead(year_week,1,0) over (order by year_week) AS CurrentYearWeek
from cte1)
select * from cte2 where year_week = '200001'

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

It's not really a useful thing... there are 14 different "week" patterns (one for each day of the week times 2 because of leap years) and there's the issue of "What do you define as a week"? For example, if you use ISO weeks which also start on Mondays, what do you expect to be done when a year has 53 such weeks and most of the rest only have 52 with some pieces missing?

Until you can define what is supposed to be a "week" according to all of that, it's going to be pretty difficult to create a formula that's actually useful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

Instead of inventing your own personal date formats, why don't you use the ISO 8601 format instead? This is very popular in Scandinavian countries.

yyyyW[05][0-9]-[1-7]. It begins with the year is a four-digit string, followed by the letter W as a separator, a week number within the year 01 thru 52 or 53 depending on the year, a dash as a separator and finally the number of the day within the week (one = Monday, seven = Sunday).

At this point, you could add an ordinal count to your table for whatever range that you need to use (a couple of hundred years is still pretty small) or you can use the week number field within the date, convert the strings to integers and add a case expression to handle weeks that go over a calendar year.

Please post DDL and follow ANSI/ISO standards when asking for help.