sql - 如何根据用户在 SQL 中输入的内容将日期时间拆分为周/月

Question

我正在创建一个将使用条形图的报告，该条形图将根据用户在开始日期和结束日期中输入的内容进行拆分。

如果用户输入的开始日期和结束日期小于 8 周，则该日期将被拆分为几周，但如果是 8 周或更长，则将其拆分为几个月。

例如如果用户输入像 01/11/2013 - 30/11/2013 这样的日期，那么它将被拆分为 01/11/2013 - 03/11/2013, 04/11/2013 - 10/11/2013, 11/11/2013 - 17/11/2013, 18/11/2013 - 24/11/2013, 25/11/2013 - 30/11/2013

但如果把日期设为 01/05/2013 - 30/11/2013，它将拆分为 01/05/2013 - 31/05/2013、01/06/2013 - 30/06/2013 等等。

我的参数有@StartDate 和@EndDate，我环顾四周并尝试了许多不同的方法，但无法使其正常工作，任何帮助都会很棒!

谢谢

托马斯·詹姆斯

Answer 1

给你..

declare @startDate datetime='09/01/2013'
declare @endDate datetime='11/30/2013'
DECLARE @WEEKCOUNT INT
declare @magic int

SELECT @WEEKCOUNT = DATEDIFF(WEEK,@startDate,@endDate)
SELECT @magic = case when @WEEKCOUNT<8 then @WEEKCOUNT else (month(@endDate)-month(@startDate))+1 end


;WITH CTESplit
as
(
    select 1 as weekcount,
    CASE WHEN @WEEKCOUNT < 8 THEN DATEADD(dd, -(DATEPART(dw, @startDate))+1, @startDate) 
    ELSE DATEADD(dd, -(DAY(@startDate)-1), @startDate) END  [WeekStart], 
    CASE WHEN @WEEKCOUNT < 8 THEN DATEADD(dd, 7-(DATEPART(dw, @startDate))+1, @startDate) 
    else DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@startDate)+1,0)) end [WeekEnd]


    union all

    select a.weekcount+1 as weekcount,
    CASE WHEN @WEEKCOUNT < 8 THEN DATEADD(dd, -(DATEPART(dw, @startDate+(7*(a.weekcount+1))))+2, @startDate+(7*(a.weekcount+1))) 
    ELSE DATEADD(dd, -(DAY(@startDate+(30*(a.weekcount+1)))-1), @startDate+(30*(a.weekcount+1))) END  [WeekStart], 
    CASE WHEN @WEEKCOUNT < 8 THEN DATEADD(dd, 7-(DATEPART(dw, @startDate+(7*(a.weekcount+1))))+1, @startDate+(7*(a.weekcount+1))) 
    else DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@startDate+(30*(a.weekcount+1)))+1,0)) end [WeekEnd]
    from CTESplit a where (a.weekcount+1)<=@magic



)

select * from CTESplit