生成一系列日期并与另一个日期和计数重叠范围进行比较 [英] Generate a range of date and compare with another range of date and count overlap

问题描述

我有一份报告（上表），我在那里通过PfLocationId计算给定日期的PfLocationIds数。 StartTime和EndTime是用户输入。我想使用存储过程构建此报告。如何在SQL Server中执行此操作？



图像说明此处 [ ^ ]



我尝试了什么：



用于转动，但不能通过重叠日期时间范围

I have a report (above table) where I count number of PfLocationIds on given day by PfLocationId. StartTime and EndTime are user inputs. I want build this report using stored procedure. How can I do this in SQL Server?

Image description here[^]

What I have tried:

used to pivot, but can't grop by overlap date time range

推荐答案

实现这一目标的最佳方法是使用 CTE（公用表格表达式） [ ^ ]。请研究以下示例：

The best way to achieve that is to use CTE (Common Table Expressions)[^]. Please, study below example:

--i'm using temporary table as source table
DECLARE @src TABLE(RentId INT IDENTITY(1,1), PfLocationId INT, StartTime DATE, EndTime DATE);
INSERT INTO @src 
VALUES(1, '2016-01-07', '2016-01-13'),
(1, '2016-01-05', '2016-01-10'),
(2, '2016-01-08', '2016-01-08'),
(2, '2016-01-11', '2016-01-12'),
(3, '2016-01-10', '2016-01-13')

--define range of dates to generate report
DECLARE @startdate DATE = '2016-01-07'
DECLARE @enddate DATE = DATEADD(DD, 7, @startdate)

--create temporary table to store the result of CTE
IF OBJECT_ID('#tmp', 'U') IS NOT NULL DROP TABLE #tmp

CREATE TABLE #tmp(CurrentDate DATE, PfLocationId INT, NoOfRents INT)

--use recursive query to grab data into temporary table
;WITH DateRange AS
(
	--initial data
	--'Occurence' field with default value of 1 will be used for further calculations 
	SELECT @startdate AS CurrentDate, T.RentId, T.PfLocationId, T.StartTime, T.EndTime, 1 AS Occurence
	FROM @src AS T
	WHERE StartTime>=@startdate OR EndTime<=@enddate 
	--recursive part: increase CurrentDate 
	UNION ALL
	SELECT DATEADD(DD, 1, CurrentDate) AS CurrentDate, RentId, PfLocationId, StartTime, EndTime, 1 AS Occurence
	FROM DateRange
	WHERE DATEADD(DD, 1, CurrentDate) <= EndTime 
)
INSERT INTO #tmp (CurrentDate, PfLocationId, NoOfRents)
SELECT DR.CurrentDate, DR.PfLocationId, SUM(DR.Occurence) AS NoOfRents
FROM DateRange AS DR
WHERE DR.CurrentDate>=DR.StartTime
GROUP BY DR.CurrentDate, DR.PfLocationId

--declare string variable to get all dates
DECLARE @dates VARCHAR(2000) = ''

SET @dates = STUFF((
    SELECT DISTINCT '], [' + CONVERT(VARCHAR(10), CurrentDate) 
    FROM #tmp 
	--ORDER BY CurrentDate 
    FOR XML PATH('')
    ), 1, 2, '') + ']';

--declare variable for pivot data
DECLARE @sql VARCHAR(MAX) = ''
SET @sql = 'SELECT PfLocationId, ' + @dates +
' FROM #tmp AS DT ' +
' PIVOT(MAX(NoOfRents) FOR CurrentDate IN(' + @dates + ')) AS PT'

--execute pivot query
EXEC(@sql)

--drop tempoaray table
DROP TABLE #tmp 

有关详细信息，请参阅：

WITH common_table_expression（Transact-SQL） [ ^ ]

使用公用表格式 [ ^ ]

这篇关于生成一系列日期并与另一个日期和计数重叠范围进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！