如何在SQL / T-SQL中每n周获取日期 [英] How to get dates every n weeks in SQL/T-SQL

问题描述

您好，我有一个包含两列的表格：

第一个：包含日期的Occurence

第二个：从该日期起的周数



我想在我的桌子上做一个选择，并且从第一次出现开始每周二和每n周（例如6个）只有日期。

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.

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
   ...           ...

所以我的任务的第一次出现是在2017年3月7日，我想每3周发生一次，所以得到的回报选择：

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 :

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

如果我想每n周拿一次它必须工作^^



谢谢：）



我尝试了什么：



我试图在week_number上使用modulo但是我不知道如何只获得n周的出现

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

推荐答案

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

这里有很多事情要做。以下是一些需要关注的内容

使用Common表格表格 [ ^ ]

递归查询使用公用表表达式 [ ^ ]



更新：哎呀。 Union all 是正确的语法

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

试试这个：

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

或演示 [ ^ ]

这篇关于如何在SQL / T-SQL中每n周获取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！