Меня попросили создать хранимую процедуру, которая отображает количество арендуемых предметов в переменном диапазоне дат. У меня есть таблица со следующей схемой:
--Note that this is condensed, and in reality has proper constraints
--and more columns. Many dates from this table are tied to a single
--ContractDetail (separate table) by ContractDetailId.
CREATE TABLE RentalContractDates
(
RentalDateId INT IDENTITY(1,1) NOT NULL, --PK
ContractDetailId INT NOT NULL, --FK
RentalDate DATETIME NOT NULL,
Quantity DECIMAL(20,8) NULL
);
INSERT INTO RentalContractDates (ContractDetailId, RentalDate, Quantity)
VALUES (1, '04/01/2016 3:00 PM', 10),
(1, '04/10/2016 1:00 PM', 2),
(1, '04/15/2016 11:00 AM', -5),
(1, '04/15/2016 11:30 AM', -2),
(1, '04/27/2016 2:00 PM', -5);
Пользователи будут вводить диапазон дат для поиска, и процедура должна найти все даты, попадающие в этот диапазон, а затем также учитывать время отсечки, когда с клиента будет взиматься плата за другой день аренды.
Пример сценария. Глобальное время отсечки установлено на 12:00. Я взял напрокат 10 виджетов 01.04.2016 в 15:00. По сути, я имею в виду, что фактически арендовал их 02.04.2016, так как 01.04 истекло время отсечки. Я арендую еще 2 10.04.2016 в 13:00, то есть, по сути, 11.04.2016. Я возвращаю 5 виджетов 15.04.2016 в 11:00 и еще 2 в 11:30. Я хочу вернуть все виджеты 27 апреля 2016 г., но я прибываю позже предельного времени 12:00, поэтому вместо того, чтобы платить за 02-4/27, я на самом деле буду платить за 02 апреля -4/28.
ВАЖНОЕ ПРИМЕЧАНИЕ. Если бы у меня было количество арендованных помещений до 01 апреля, то есть начала диапазона отчета, мне нужно было бы включить их в отчет. Например, если бы у меня было 12 прокатов 31 марта, 1 апреля и далее, к их общему количеству было бы добавлено 12. Другими словами, любые предыдущие количества необходимо было вычислить в сумме, которая будет получена с помощью введенных параметров отчета @BeginDate и @EndDate. Таким образом, 01.04 будет читаться как 12, 02.04 будет читаться как 22 и т. д.
Как видите, мне не нужно, чтобы пользователи вводили данные о своей аренде каждый день, я просто задаю им дату и время начала аренды с количеством, и в следующий раз, когда они вводят комбинацию даты/времени, она будет повторно введена. подвел итог.
Текущий код: я хочу соединить этот запрос со списком календарных дат на весь месяц и соответствующим образом установить их количество.
DECLARE @BeginDate DATETIME = '04/01/2016',
@EndDate DATETIME = '04/28/2016';
DECLARE
@CutoffTime TIME = '12:00 PM';
SET @BeginDate = @BeginDate + @CutoffTime;
SET @EndDate = @EndDate + @CutoffTime;
SELECT gbd.ContractDetailId,
gbd.RentalDate,
gbd.Cutoff,
gbd.Quantity,
'Running Total' = SUM(Quantity) OVER (PARTITION BY ContractDetailId, RentalDate, Cutoff ORDER BY RentalDate)
FROM (
SELECT
r.ContractDetailId,
'RentalDate' = CONVERT(Date, RentalDate),
r2.Cutoff,
r.Quantity
FROM RentalContractDates r
INNER JOIN
(
SELECT
rcd.ContractDetailId,
'Cutoff' = CASE WHEN CONVERT(TIME, RentalDate) >= @CutoffTime THEN 'AFTER CUTOFF' ELSE 'BEFORE CUTOFF' END
FROM
RentalContractDates rcd
) r2
ON r2.ContractDetailId = r.ContractDetailId
WHERE
r.RentalDate Between @BeginDate and @EndDate
GROUP BY r.ContractDetailId, CONVERT(DATE, RentalDate), r2.Cutoff, Quantity
) gbd
ORDER BY RentalDate, Cutoff DESC
Я хочу объединить данные с этим CTE и установить количество для каждой даты:
;WITH T([Date]) AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(DAY,1,T.[Date]) FROM T WHERE T.[Date] < @EndDate
)
SELECT * FROM T
Ожидаемый окончательный результат. После завершения отчет будет выглядеть примерно так, хотя он будет развернут и будет включать день недели в название:
ContractDetailId RentalDate Quantity
----------------------------------------------------------------
1 04/01/2016 0 -- 0, because rentals were input after cutoff.
1 04/02/2016 10
1 04/03/2016 10 -- Continues until 4/10
1 04/10/2016 10
1 04/11/2016 12 -- Continues until 4/15
1 04/15/2016 5 -- I returned 5 and then 2, so this should sum since both were before the cutoff time.
-- Continues until 4/27.
1 04/27/2016 5 -- 5, because -5 was entered past cutoff on 4/27.
1 04/28/2016 0
У меня есть поворотный код вместе с динамическим sql, необходимым для окончательного вывода, уже завершенным (я могу опубликовать это, если потребуется), но я не понимаю, как правильно сгруппировать эти данные по предварительной/послерезке и соответственно изменить день. Как мне поступить в этой ситуации? Спасибо за любой совет/помощь!
Редактировать 1: исправлена неверная выборка данных.
12*60=720
минуты из каждого исходногоdatetime
и после этого игнорируйте временную часть:CAST(DATEADD(minute, -720, RentalDate) AS date)
. - person Vladimir Baranov   schedule 27.04.2016