How to get dates every n weeks in SQL/T-SQL

Question

0.00/5 (No votes)

See more:

Hello, i have a table with two columns :
First : Occurence, containing dates
Second : the number of the week from the date

I'd like to do a Select on my table, and get only dates every tuesday and every n weeks(6 for example) from the first occurence.

SQL

Ex: 
OCCURENCE     WEEK_NUMBER
07/03/2017         8        --First tuesday
14/03/2017         9        -- Second tuesday of the month
21/03/2017        10        --Third tuesday of the month
28/03/2017        11        --Fourth
   ...           ...

So the first occurence of my task is on the 07/03/2017 and i would like to get every occurence every 3 weeks , so get as return of the select :

SQL

OCCURENCE     WEEK_NUMBER
07/03/2017         8          --First occurence
28/03/2017        11          -- Third occurence
   ...           ...

It must work if i want to get every n weeks ^^

Thanks :)

What I have tried:

I tried to use modulo on week_number but i don't know how to get only occurences for the n week

Posted 7-Mar-17 4:23am

Vincent Ducroquet

Updated 7-Mar-17 6:22am

v3

Add a Solution

Comments

Andy Lanng 7-Mar-17 10:32am

What sql language? T-SQL, P-SQL, MySQL...

Vincent Ducroquet 7-Mar-17 10:35am

SQL-Server or T-SQL

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Answer 1 · 2017-03-07T04:45:00

SQL

declare @weeks int
set @weeks = 3

--Common Table Expressions
;with mycte as (
	-- get the lower and upper bounds to limit the recursion
	select min([table_date]) as startPoint, max([table_date]) as endPoint, [id]
	from [table]
	group by id
), mycte2 as (
	--recursive cte gets all dates that are @weeks after the startPoint and are lessthan or equal to the endPoint
	select startPoint, endPoint, id
	from mycte
	--These are very powerful and fast
	union all select DATEADD(week,@weeks,startPoint), endPoint, id
	from mycte2
	where DATEADD(week,@weeks,startPoint) <= endPoint
)
--finally, select all the items that fit into the id, and date and date + n*m weeks
select * from [table] t
inner join mycte2 m on t.id = m.id and t.[table_date] = m.date

There is a lot going on here. Here are some things to look into
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]

UPDATE: Whoops. "Union all" is the correct syntax

Peter Leow · Answer 2 · 2017-03-07T06:22:00

Solution 3

Try this:

SELECT * FROM tablename WHERE DATEDIFF(week, CONVERT(DATE,'07/03/2017',103), CONVERT(DATE,occurence,103) ) % 3 = 0

or demo[^]

Posted 7-Mar-17 6:22am

Peter Leow