如何在2个日期点之间返回空日期 [英] How to return empty dates between 2 date points

问题描述

大家好。



我正在为办公室创建一个工作流程数据库，因此我们需要查看一周中每一天的计划工作。 br />


我将在下面简要介绍的存储过程仅显示我们有数据的日期。但是，如果我也希望看到日期，即使没有这样的日期数据，我还需要进行哪些更改。



我正在使用动态SQL所以当你进入本周的第二天时，列和日期会自动向上移动。



我希望这是有道理的。

非常感谢。

Hi Everyone one.

I am creating a workflow database for the office and hence we need to see what work is planned for each day of the week.

The stored proc I will shortly give below will only show the date for which we have data. However, what changes do I need to make if I also want to see dates even if there is no data for such a date.

I am using dynamic SQL so the columns and hence dates automatically shift up as you progress to the next day in the week.

I hope that makes sense.

many thanks.

ALTER PROCEDURE [dbo].[spTotalRunsPerWeek]
AS

CREATE TABLE #ResultsDateSet
(
  DateRun VARCHAR(MAX)
)

INSERT INTO #ResultsDateSet
Select convert(varchar(10), EstimatedCompletionDate, 111)
from tbRun r
left outer join tbProject p on p.ProjectID = r.ProjectID
left outer join tbFK_UserProject fk on fk.ProjectID = p.ProjectID
left outer join tbUser u on u.UserID = fk.UserID
left outer join tbRole ro on ro.RoleID = fk.RoleID
left outer join tbExecution e on e.ExecutionID = p.ExecutionID
WHERE UserName <> 'NA' and ro.RoleID = 2
and EstimatedCompletionDate >= DATEADD(day,-1,GETDATE())
and EstimatedCompletionDate <= DATEADD(day,14,GETDATE())
group by convert(varchar(10), EstimatedCompletionDate, 111)
order by convert(varchar(10), EstimatedCompletionDate, 111)

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
  COALESCE(
     @PivotColumnHeaders + ',[' + cast(DateRun as varchar) + ']',
     '[' + cast(DateRun as varchar)+ ']'
   )
 FROM #ResultsDateSet
 ORDER BY DateRun

DECLARE @PivotTableSQL NVARCHAR(MAX)
 SET @PivotTableSQL = N'
 
CREATE TABLE #Results
(
  UserName VARCHAR(MAX),
  ProjectName VARCHAR(MAX),
  ExecutionName VARCHAR(MAX),
  DateRun VARCHAR(MAX),
  TotalRuns INT
)

INSERT INTO #Results
Select u.FirstName + '' '' + u.Surname as UserName, ProjectName, ExecutionName,
       convert(varchar(10), EstimatedCompletionDate, 111),
Count(r.RunNumber)
from tbRun r
left outer join tbProject p on p.ProjectID = r.ProjectID
left outer join tbFK_UserProject fk on fk.ProjectID = p.ProjectID
left outer join tbUser u on u.UserID = fk.UserID
left outer join tbRole ro on ro.RoleID = fk.RoleID
left outer join tbExecution e on e.ExecutionID = p.ExecutionID
WHERE UserName <> ''NA'' and ro.RoleID = 2
group by u.FirstName, u.Surname, ProjectName, ExecutionName,convert(varchar(10), EstimatedCompletionDate, 111)
order by u.FirstName, u.Surname, ProjectName, ExecutionName,convert(varchar(10), EstimatedCompletionDate, 111)

SELECT *
FROM #Results
PIVOT (sum(TotalRuns)for DateRun in (' + @PivotColumnHeaders + ')) As Result

DROP TABLE #Results
'

EXECUTE(@PivotTableSQL)

DROP TABLE #ResultsDateSet

推荐答案

可能不是你的后续但你可以使用RCT（递归公用表表达式）来创建一个多重你需要的每一行的行，然后右外连接到它



参见示例b elow

might not be what your after but you could use a RCT(recursive common table expresion) to create a multiple rows for everydate you need and then right outer join to it

see example below

declare @tables table (dates datetime);
with dates as (
    SELECT CAST('2012-01-01' as datetime) as addtome
    UNION ALL
    SELECT DATEADD(day,1,addtome) FROM dates where DATEPART(year,addtome) = 2012
    )


INSERT INTO @tables SELECT addtome FROM dates OPTION (MAXRECURSION 366)

然后在你的select语句中添加如下所示的连接

then in your select statement add a join as below

RIGHT OUTER JOIN @tables t on t.date =

正如Dec90所写，看起来您需要日期范围内的所有日期。



枚举日期范围内所有日期的其他解决方案是使用循环：

As Dec90 wrote, it looks like you need all dates in date range.

Other solution to enum all dates in date range is to use loop:

DECLARE @begDate DATETIME
DECLARE @endDate DATETIME
DECLARE @curDate DATETIME

DECLARE @dTable TABLE(aDate DATETIME)

SET @begDate = '2013-01-01'
SET @endDate = GETDATE()

SET @curDate = DATEADD(day,-1,@begDate)
WHILE (@curDate < @endDate)
BEGIN
	SET @curDate = DATEADD(day,1,@curDate)
	INSERT INTO @dTable VALUES(@curDate)
END

SELECT *
FROM @dTable

这篇关于如何在2个日期点之间返回空日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！