SQL Server 日期范围按每月一行拆分

给定一个日期，按月每行拆分。

CREATE TABLE #temp(from_date DATE,to_date DATE);
INSERT INTO #temp (from_date,to_date) VALUES ( '2013-10-25','2013-12-05');
SELECT * FROM #temp;

SELECT  CASE n.number WHEN 0 THEN d.from_date
          ELSE DATEADD(MONTH, n.number,DATEADD(DAY, 1 - DAY(from_date), from_date))
        END [start_date],
        CASE n.number WHEN DATEDIFF(MONTH, d.from_date, d.to_date) THEN to_date
          ELSE DATEADD(DAY, -1,DATEADD(MONTH, 1,DATEADD(MONTH, n.number, DATEADD(DAY, 1 - DAY(from_date),from_date))))
        END [end_date]
FROM    master.dbo.spt_values n
        INNER JOIN #temp AS d ON d.to_date >= DATEADD(MONTH, n.number - 1,d.from_date)
WHERE   type = 'p'

 

原始数据集：

拆分结果集：

 modfiy 2021/12/21 来自园友 是小凯i 的指正，在此感谢：），日期范围是当月或者结束日期 日数字 大于 开始日期 日数字的时候会出现多出一个月的结果，修正后如下：

方法一：

CREATE TABLE #temp(from_date DATE,to_date DATE);
INSERT INTO #temp (from_date,to_date) VALUES ( '2013-10-25','2013-11-20');
SELECT * FROM #temp;

SELECT  CASE n.number WHEN 0 THEN d.from_date
          ELSE DATEADD(MONTH, n.number,DATEADD(DAY, 1 - DAY(from_date), from_date))
        END [start_date],
        CASE n.number WHEN DATEDIFF(MONTH, d.from_date, d.to_date) THEN to_date
          ELSE DATEADD(DAY, -1,DATEADD(MONTH, 1,DATEADD(MONTH, n.number, DATEADD(DAY, 1 - DAY(from_date),from_date))))
        END [end_date]
FROM    master.dbo.spt_values n
        INNER JOIN #temp AS d ON CONVERT(CHAR(6),d.to_date,112) >= CONVERT(CHAR(6),DATEADD(MONTH, n.number,d.from_date),112) AND d.to_date>d.from_date
WHERE   type = 'p'  

 

结果：

方法二：

CREATE TABLE #temp(from_date DATE,to_date DATE);
INSERT INTO #temp (from_date,to_date) VALUES ( '2013-10-25','2013-11-20');
SELECT * FROM #temp;

WITH CTE as 
    (
    SELECT from_date,to_date,from_date as [start_date], 
             CASE WHEN DATEADD(DAY,-DAY(DATEADD(MONTH,1, d.from_date)),DATEADD(MONTH,1, d.from_date))< To_date 
                       THEN DATEADD(DAY,-DAY(DATEADD(MONTH,1, d.from_date)),DATEADD(MONTH,1, d.from_date))
                       ELSE To_Date END as [end_date]
      FROM #temp d
      UNION ALL 
      SELECT from_date,To_date,DATEADD(DAY,1,[end_date]), 
             CASE WHEN DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1,[end_date])))< To_date 
                       THEN DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1,[end_date])))
                       ELSE To_Date END FROM CTE WHERE [end_date] < To_Date
      )
 SELECT [start_date],[end_date] FROM CTE ORDER BY from_date,[end_date];

 

结果同上

 

 

二

已知一个数，得知那些行的合计后等于这个数

CREATE TABLE #t(ID VARCHAR(1),数据 INT)
INSERT INTO #t
select 'a',10 union all
select 'b',90 union all
select 'c',100 union all
select 'd',180 union ALL
select 'e',15 union ALL
select 'f',5;
with ps as
(
select *,path=cast(id as varchar(8000)),path1=cast(数据 as varchar(8000)),total=数据 from #t
union all
select b.id,b.数据,a.path+','+b.id,path1+','+ltrim(b.数据), a.total+b.数据
from ps a join #t b on a.id<b.id and a.total<200
)

select path,path1,total from ps where total=200