Maciej Los
Лучший способ достичь этого-использовать CTE (общие табличные выражения)[^]. Пожалуйста, изучите приведенный ниже пример:
--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
Для получения более подробной информации, пожалуйста, смотрите:
С common_table_expression (Transact-SQL)[
^]
Использование Общих Табличных Выражений[
^]