把日子分成几个间隔 [英] splitting days into intervals

问题描述

大家好，



我需要在我的项目中使用一些sql部分。我想将天数分成若干间隔

我有startdate，enddate和interval作为输入。根据间隔划分startdate和enddate之间的天数。



例如



如果

Hello everyone,

I need to some sql part in my project. I want to divide days into certain intervals
I have startdate,enddate and interval as inputs. Divide the days between startdate and enddate based on interval.

for example

if

startdate        enddate        interval
04/01/2012    20/02/2012    7

the output should be like this

04/01/2012  10/01/2012
11/01/2012  17/01/2012
18/01/2012  24/01/2012
25/01/2012  31/01/2012
01/02/2012  07/02/2012
08/02/2012  14/02/2012
15/02/2012  20/02/2012

请帮帮我........

please help me........

推荐答案

谢谢你提出的好问题。你不常在这里找到它。

你可以使用Common Table Expression来表示：

Thanks for the nice question. It is not very often you find it here.
You can use Common Table Expression for this:

declare @StartDate datetime = '03-Jan-2012', 
  @EndDate datetime = '20-Feb-2012', 
  @Interval int = 7

;with cte as(
  SELECT @StartDate as StartDate, DateAdd(d, @Interval - 1, @StartDate) as EndDate
  UNION ALL
  SELECT DateAdd(d, @Interval, c.StartDate) as StartDate, DateAdd(d, @Interval * 2 - 1, c.StartDate) as EndDate
  FROM cte c WHERE DateAdd(d, @Interval * 2 - 1, c.StartDate) < @EndDate
)
SELECT * FROM cte
OPTION (MAXRECURSION 0)

请检查以下查询。我试图满足你的要求。如果它不符合您的要求，请告诉我。

Please check the following query. I tried to fulfill your requirement. Let me know if it is not fulfil your requirement.

declare @StartDate datetime = '03-Jan-2012', @EndDate datetime = '20-Feb-2012', @Interval int = 6;
declare @Flag bit = 0;
while @StartDate < @EndDate
begin	
	if @Flag = 0
	begin
		set @Flag = 1;
		set @StartDate += 1;		
	end
	else
	begin
		set @StartDate += @Interval;
		set @Flag = 0;		
	end
	print @StartDate   			
end 

以上查询打印您在问题中显示的日期。

The above query print the date which you show in your question.

这篇关于把日子分成几个间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！