問題描述

我有這個(gè)查詢，我想要一個(gè)不使用遞歸 CTE 的替代查詢.任何人都可以編輯和更新查詢

I have this query and I want an alternative for this query without using recursive CTE. Can Anybody please edit and update the query

DECLARE @d TABLE(value int ,from_date DATE, to_date DATE);
INSERT @d VALUES (7,'2013-11-05','2013-11-25'),(8,'2013-07-05','2013-12-25'),(7,'2013-02-07','2013-04-14');
WITH cte AS
(SELECT value,from_date
      , to_date
      , from_date AS mo_from_date
      , DATEADD(day, day(from_date)* -1 + 1, from_date) AS bom_date
   FROM @d
UNION ALL
SELECT value, from_date
     , to_date
     , DATEADD(month,1,bom_date)
     , DATEADD(month,1,bom_date)
  FROM cte
 where DATEADD(month,1,mo_from_date) < to_date
)
SELECT value,mo_from_date
     , CASE when to_date < DATEADD(month,1,bom_date) THEN
           to_date
       ELSE
           DATEADD(day, -1, DATEADD(month,1,bom_date))
       END AS mo_to_date
  FROM cte

輸出:

value   mo_from_date    mo_to_date
7   2013-11-05  2013-11-25
8   2013-07-05  2013-07-31
8   2013-08-01  2013-08-31
8   2013-09-01  2013-09-30
8   2013-10-01  2013-10-31
8   2013-11-01  2013-11-30
8   2013-12-01  2013-12-25
9   2013-02-07  2013-02-28
10  2013-03-01  2013-03-31
11  2013-04-01  2013-04-14

我嘗試了以下查詢，但它不起作用，任何人都可以更新此查詢

I have tried below query but its not working,can anybody update this query

;with MonthList as (
    select 
        DATEADD(month, M, '12/1/1899') as 'FirstDay',
        dateadd(day, -1, dateadd(month, M + 1, '12/1/1899')) as 'LastDay',
        DATEADD(month, M + 1, '12/1/1899') as 'FirstDayNextMonth'
    from (
        select top 3000 ROW_NUMBER() over (order by s.name) as 'M'
        from master..spt_values s) s
)

select
   t.value, ml.FirstDay as from_date, ml.LastDay as to_date
from
    @d t
    inner join MonthList ml
        on  t.from_date < ml.FirstDayNextMonth
            and t.to_date >= ml.FirstDay

推薦答案

這是一種使用內(nèi)聯(lián)計(jì)數(shù)的方法:

This is a method using an inline tally:

DECLARE @d TABLE(value int ,from_date DATE, to_date DATE);
INSERT @d VALUES (7,'2013-11-25','2013-11-05'),(8,'2013-07-25','2013-12-05');

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (SELECT MAX(DATEDIFF(MONTH,from_date,to_date)) FROM @d)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 months, add more cross joins for more months
SELECT d.[value],
       CASE WHEN DATEADD(DAY,1,EOMONTH(DATEADD(MONTH,T.I,d.from_date),-1)) < d.from_date THEN d.from_date ELSE DATEADD(DAY,1,EOMONTH(DATEADD(MONTH,T.I,d.from_date),-1)) END AS mo_from_date,
       CASE WHEN EOMONTH(DATEADD(MONTH,T.I,d.from_date)) > d.to_date THEN d.to_date ELSE DATEADD(MONTH,T.I,d.from_date) END AS mo_to_date 
FROM @d d
     JOIN Tally T ON DATEDIFF(MONTH,from_date,to_date) >= T.I;

不過，我個(gè)人建議你給自己一個(gè)日歷表，那么問題就簡(jiǎn)單多了:

I do, personally, however, suggest you get yourself a Calendar Table, then the problem is far more simple:

SELECT d.[value],
       CASE WHEN d.from_date > MIN(CT.CalendarDate) THEN d.from_date ELSE MIN(CT.CalendarDate) END AS mo_from_date,
       CASE WHEN d.to_date < MAX(CT.CalendarDate) THEN d.to_date ELSE MAX(CT.CalendarDate) END AS mo_to_date
FROM @d d
     JOIN dbo.CalendarTable CT ON d.from_date <= CT.CalendarDate --To assumes that the to_date being before the
                              AND d.to_date >= CT.CalendarDate   --the from date is an error.
GROUP BY d.[value],
         d.from_date,
         d.to_date,
         MONTH(CT.CalendarDate),
         YEAR(CT.CalendarDate);

兩種解決方案都假設(shè) from 日期應(yīng)該在 to 日期之前之前；即使它在您的數(shù)據(jù)中似乎不正確.如果您的 from 日期可以是 after 您的 to 日期，我建議您有數(shù)據(jù)問題，因?yàn)槿绾?strong>開始strong> 之后結(jié)束?

Both solutions assume that the from date should be before the to date though; even though that it appears to not be true in your data. If your from date can be after your to date, I would suggest you have data issues, as how can something start after it has ended?

這篇關(guān)于將日期范圍拆分為幾個(gè)月(使用 CTE)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！