问题描述
您好,我有一个包含两列的表格:
第一个:包含日期的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
这里有很多事情要做。以下是一些需要关注的内容
[]
[]
更新:哎呀。 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周获取日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!