在循环问题时，从周期计算天数

Question

Table Order

OrderID | FromDate   | ToDate    
4523691 | 2015-01-23 | 2015-04-22     
4523692 | 2015-05-07 | 2015-06-23  
4523693 | 2015-02-09 | 2015-05-08

已降验的结果

| OrderID  |  Year  |  Month  |  Days  |      
| 4523691  |  2015  |   1     |   9 |   
| 4523691  |  2015  |   2     |   28 |   
| 4523691  |  2015  |   3     |   31 |   
| 4523691  |  2015  |   4     |   22 |   
| 4523692  |  2015  |   5     |   25 |   
| 4523692  |  2015  |   6     |   23 |   
| 4523693  |  2015  |   2     |   20 |   
| 4523693  |  2015  |   3     |   31 |   
| 4523693  |  2015  |   4     |   30 |   
| 4523693  |  2015  |   5     |   8 | 

如果使用每个订单 ID 的 where 语句运行脚本，则该脚本将正常工作。所以这就是我需要帮助的，以不受订单 ID 限制地运行查询。 删除限制将导致以下错误 = Msg 512，级别 16，状态 1，第 5 行 子查询返回了 1 个以上的值。当子查询跟在 =、！=、<、<= 、>、>= 后面或子查询用作表达式时，不允许这样做。

DECLARE @FromDate as datetime  
DECLARE @Todate as date  
DECLARE @Month as date  
SET @FromDate = (select fromdate from Order where orderid = '4523693')
SET @ToDate = (select todate from Order where orderid = '4523693')  
SET @Month = @FromDate  
WHILE (eomonth(@Month) <= eomonth(@ToDate))  
BEGIN  
SELECT 
OrderID
,year(dateadd(month, 0, eomonth(@Month)))
,month(dateadd(month, 0, eomonth(@Month)))
,case
when eomonth(@Month) = eomonth(fromdate) then datediff(d, fromdate, eomonth(fromdate))+1       
when eomonth(@Month) = eomonth(todate) then datediff(day,DATEADD(m, DATEDIFF(m, 0, todate), 0) , todate)+1
else DATEPART(dd, DATEADD(dd, DATEPART(dd, DATEADD(mm, 1, dateadd(month, 0, eomonth(@Month)))) * -1, DATEADD(mm, 1, dateadd(month, 0, eomonth(@Month)))))
end as 'Days'
FROM Order
WHERE dateadd(month, 0, eomonth(fromdate)) <= eomonth(todate)
AND FROMDATE IS NOT NULL
AND ORDERID = '4523693'
SET @Month = dateadd(month, 1, eomonth(@Month))
END

Answer 1

这是一个解决方案。我保留了一个日期 CTE 片段，用于需要涵盖gaps in date and time的查询。如果将所有日期倒入临时存储中，则查询将变得更加自然，并且也更具可读性。

DECLARE @Orders TABLE(OrderID INT,FromDate DATETIME,ToDate DATETIME)
INSERT @Orders VALUES (100,'01/23/2015','04/22/2015'),(200,'05/07/2015','06/23/2015'),(300,'02/09/2015','05/08/2015')
DECLARE @StartDate DATETIME = (SELECT MIN(FromDate) FROM @Orders)
DECLARE @EndDate DATETIME = (SELECT MAX(ToDate) FROM @Orders)
;WITH Calendar as 
( 
SELECT CalendarDate = @StartDate, CalendarYear = DATEPART(YEAR, @StartDate), CalendarMonth = DATEPART(MONTH, @StartDate) 
UNION ALL 
SELECT CalendarDate = DATEADD(MILLISECOND, -2, DATEADD(DAY, 1, DATEDIFF(dd, 0, DATEADD(DAY, 1, CalendarDate)))), CalendarYear = DATEPART(YEAR, CalendarDate), CalendarMonth = DATEPART(MONTH, DATEADD(DAY, 1, CalendarDate))        FROM Calendar WHERE DATEADD (DAY, 1, CalendarDate) <= @EndDate 
)
SELECT 
OrderID,
CalendarYear,
CalendarMonth,
Days = COUNT(*)
FROM
Calendar C
LEFT JOIN @Orders O ON C.CalendarDate BETWEEN O.FromDate AND O.ToDate 
GROUP BY
OrderID,CalendarYear,CalendarMonth,O.FromDate,O.ToDate
ORDER BY
O.OrderID,O.FromDate,O.ToDate
OPTION (MAXRECURSION 1000)

Answer 2

SET @FromDate = (select fromdate from Order where orderid = '4523693')
SET @ToDate = (select todate from Order where orderid = '4523693')  
Change this to 
SET @FromDate = (select top 1 fromdate from Order where orderid = '4523693' order by fromdate desc )
SET @ToDate = (select  top 1 todate from Order where orderid = '4523693' order by todate desc)