I have been asked to create a stored procedure that displays rental item quantities across a variable date range. I have a table with the following schema:
--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);
Users will enter a date range to search, and the procedure should find all dates that fall within this range, and then also factor in a cutoff time to where a customer would be charged another day on their rentals.
Example Scenario: The global Cutoff Time is set to 12:00PM. I have 10 widgets rented on 4/01/2016 at 3:00PM. This basically I means I actually rented them on 4/02/2016 since it is past the Cutoff Time on 04/01. I rent 2 more on 4/10/2016 at 1:00PM, so essentially 4/11/2016. I return 5 widgets on 4/15/2016 at 11:00 AM, and 2 more at 11:30 AM. I want to return all widgets on 4/27/2016, but I arrive past the Cutoff Time of 12:00PM, so rather than get charged for 4/02-4/27, I am actually going to be charged for 4/02-4/28.
IMPORTANT NOTE: If I had quantities previously rented before 04/01, which is the start of the report range, I would need to include those in the report. For example, if I had 12 rentals on 3/31, 4/1 and on would be adding 12 to their totals. In other words, any previous quantities needed to be calculated into the sum that pulls in with the inputted report @BeginDate and @EndDate parameters. So 04/01 would read 12, 04/02 would read 22, etc.
As you can see, I do not need users to enter their rentals each day, I just have them set a start date and time for their rentals with a quantity, and the next time they enter a date/time combination it would be re-summed.
Current Code: I want to join this query with a list of calendar dates for the entire month, and set their quanties accordingly.
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
I want to join the data with this CTE and set a quantity for every date:
;WITH T([Date]) AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(DAY,1,T.[Date]) FROM T WHERE T.[Date] < @EndDate
)
SELECT * FROM T
Expected Final Output: When complete, the report would end up looking something like this, although it will be pivoted and include the day of the week in the name:
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
I have the pivoting code along with the dynamic sql necessary for final output already complete (I can post this if requested), but I am lost on how to properly group this data by pre-cutoff/post-cutoff and change the day accordingly. How should I handle this situation? Thank you for any advice/help!
Edit 1: Fixed incorrect sample data.
--Inputs for your function
DECLARE @BeginDate DATE = '04/01/2016',
@EndDate DATE = '04/28/2016',
@ContractDetailID INT = 1;
--Defined in the function
DECLARE @CutoffTime TIME = '12:00 PM';
DECLARE @PriorSum DECIMAL(20,8) = 0;
DECLARE @RowCount INT = DATEDIFF(dd,@BeginDate,@Enddate) +1;
--Get Any quantities before Begin Date
SELECT @PriorSum=COALESCE(SUM(rcd.Quantity),0)
from RentalContractDates rcd
WHERE CAST(CASE when CAST(rcd.RentalDate as TIME) > @CutoffTime THEN DATEADD(dd,1,rcd.RentalDate) ELSE rcd.RentalDate end as date) < @BeginDate
AND @ContractDetailID = rcd.ContractDetailId
--Create the Days for the report
;WITH RecursiveRowGenerator (Row#, Iteration) AS (
SELECT 1, 1
UNION ALL
SELECT Row# + Iteration, Iteration * 2
FROM RecursiveRowGenerator
WHERE Iteration * 2 < CEILING(SQRT(@RowCount+1))
UNION ALL
SELECT Row# + (Iteration * 2), Iteration * 2
FROM RecursiveRowGenerator
WHERE Iteration * 2 < CEILING(SQRT(@RowCount+1))
)
, SqrtNRows AS (
SELECT *
FROM RecursiveRowGenerator
UNION ALL
SELECT 0, 0
)
, Rowtbl as (
SELECT top (@RowCount+1) A.Row# * POWER(2,CEILING(LOG(SQRT(@RowCount+1))/LOG(2))) + B.Row# as RowNum
FROM SqrtNRows A, SqrtNRows B
ORDER BY A.Row#, B.Row#
)
,
DateTable as (
select top (@RowCount) DATEADD(dd,RowNum,@BeginDate) AS ReportDate
from Rowtbl
where RowNum <= @RowCount
)
,
--Merge the days for the report with the actual rental data
GBD AS
( SELECT
@ContractDetailID as ContractDetailID,
DT.ReportDate AS 'RentalDate',
CASE when CAST(rcd.RentalDate as TIME) > @CutoffTime THEN 'AFTER CUTOFF' ELSE 'BEFORE CUTOFF' END AS 'Cutoff',
COALESCE(rcd.Quantity,0) AS Quantity
FROM DateTable DT
LEFT JOIN RentalContractDates rcd on
DT.ReportDate = CAST( CASE when CAST(rcd.RentalDate as TIME) > @CutoffTime THEN DATEADD(dd,1,rcd.RentalDate) ELSE rcd.RentalDate END as DATE)
AND @ContractDetailID = rcd.ContractDetailId
WHERE DT.ReportDate Between @BeginDate and @EndDate
)
--Final Select
SELECT gbd1.ContractDetailId,
gbd1.RentalDate,
(select SUM(gbd2.Quantity) from GBD GBD2 where GBD1.rentaldate >= GBD2.RentalDate) + @PriorSum AS RunningTotal
FROM GBD gbd1
GROUP BY gbd1.ContractDetailId,gbd1.RentalDate
ORDER BY gbd1.RentalDate asc
Edit: The recursive row generator is by Aaron Friel and is my go to solution for generating rows t-sql select get all Months within a range of years. It generates rows for all the dates so we can then left join either a quantity or 0 if there is no record for that date. Generating Dates from 2000 to 2016 is very cheap using this generator. Joining all the quantities and previous sum to those dates is mildly expensive. Aggregating them so you have a running total even for dates with no record in RentalContractDates is the expensive part.
The part that handles the cut off time issue is:
gbd.RentalDate = CAST(CASE WHEN CAST(rcd.RentalDate AS TIME) > @CutoffTime THEN DATEADD(dd, 1, rcd.RentalDate)
ELSE rcd.RentalDate END AS DATE)
It converts rentaldate to a time only, compares to the cutoffTime, adds a day if past and then converts to date only.