生成一系列日期并与另一个日期和计数重叠范围进行比较 [英] 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屋!
查看全文