Insert all dates between a date range in a table with other data

Question

For a training class, user insert info like startdt, enddt, time range, requester name in the main table cal_tr_requests. In case of training with more than one day, the enddt is after startdt. I have created another table cal_tr_Multidate to insert all the dates with classId and time range from main table. So if the cal_tr_requests enter this:

ClassID -- req_start_Date---req_end_date ---req_time--requsetor_name
1234567    2016-08-30        2016-08-31    8:00-10:00  test

then the cal_tr_Multidates table will enter:

ClassID --- ReserveDt ---- ReserveTm
1234567     2016-08-30     8:00-10:00
1234567     2016-08-31     8:00-10:00

I am having problem to create the stored procedure with the loop. This is what I have:

Alter PROCEDURE [dbo].[AddTrainingRoomRq_test]

@req_start_date DateTime,
@req_end_date DateTime,
@req_times text,
@requestor_name text

AS
BEGIN

Declare 

@ClassID int,
@Multidates int
--Create the ClassID as a random number
Set @ClassID =  (SELECT CAST(RAND() * 1000000000 AS INT) AS [RandomNumber])

INSERT INTO cal_tr_requests
           (ClassID, req_start_date, req_end_date, req_times, requestor_name, date_of_req)
VALUES        (@ClassID,@req_start_date,@req_end_date,@req_times,@requestor_name,getdate())

set @Multidates = (select datediff(d, req_start_date, req_end_date)
                    from cal_tr_requests
                    where ClassID =  @ClassID)

if @Multidates = 0
Insert into Cal_tr_Multidates (ClassID, ReserveDt,ReserveTm)
               Values(@ClassID, @req_start_date, @req_times)

else if  @Multidates > 0
 While @Multidates = 0
 begin

--do the insert for all dates with classid and reserveTm

End
END

Can someone please point me to right direction? Thanks in advance.

I realize this if for a class but you really should not be using the text datatype. If your instructor is using this you need to correct them. If it is your choice you need to immediately, if not sooner, forget the text datatype exists. It has been deprecated for more than a decade and it is a real pain to work with. https://msdn.microsoft.com/en-us/library/ms187993.aspx — Sean Lange, Aug 29 '16 at 18:49

Dave C · Accepted Answer · 2016-08-29T20:53:39.957

Here is a sample recursive CTE to handle the dates:

DECLARE @req_start_date DateTime,
        @req_end_date DateTime

SET @req_start_date = '2016-08-15'
SET @req_end_date = '2016-08-31'

;WITH X AS 
    (
        SELECT @req_start_date AS VAL
        UNION ALL
        SELECT DATEADD(DD,1,VAL) FROM X
        WHERE VAL < @req_end_date
    )

SELECT *
FROM X
OPTION(MAXRECURSION 0)

And here it is in your proc (get rid of the text type as Sean said above), and there's no need for the @multidates if/else block as the CTE will only return 1 row when start=end date:

ALTER PROCEDURE [dbo].[AddTrainingRoomRq_test]
(
@req_start_date DateTime,
@req_end_date DateTime,
@req_times VARCHAR(100),
@requestor_name VARCHAR(100)
)
AS
BEGIN
    DECLARE @ClassID INT

    --Create the ClassID as a random number
    Set @ClassID =  (SELECT CAST(RAND() * 1000000000 AS INT) AS [RandomNumber])

    INSERT INTO cal_tr_requests (ClassID, req_start_date, req_end_date, req_times, requestor_name, date_of_req)
    VALUES (@ClassID,@req_start_date,@req_end_date,@req_times,@requestor_name,getdate())

    ;WITH X AS 
    (
        SELECT @req_start_date AS VAL
        UNION ALL
        SELECT DATEADD(DD,1,VAL) FROM X
        WHERE VAL < @req_end_date
    )

    INSERT INTO Cal_tr_Multidates (ClassID, ReserveDt,ReserveTm)    
    SELECT @ClassID, VAL, @req_times
    FROM X
    OPTION(MAXRECURSION 0)

END

Oh, thank you so much!! I had the recursive CTE to get the dates but was not able to insert them with other two parameters. This solves my problem. Thanks again! — user6771075, Aug 30 '16 at 12:38

score 1 · Answer 2 · answered Aug 29 '16 at 17:32

My purpose here is not to give you a complete solution for your specific problem, but to demonstrate a principal that you should be able to implement to solve your challenge.

A lot of the t-sql solutions posted on this site do work, but they are very often not scalable. I could fix your loop to make it work, but it would not be a good approach to take with t-sql to solve this problem. Scalable t-sql is set-based, not procedural.

Here is an example of a set-based approach to getting all the dates between two other dates:

DECLARE @dateBegin DATE = '20160830';
DECLARE @dateEnd DATE = '20160905';

WITH E1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))     AS t(N))
    ,E2 AS (SELECT N = 1 FROM E1 AS a, E1 AS b)
    ,E4 AS (SELECT N = 1 FROM E2 AS a, E2 AS b)
    ,E8 AS (SELECT N = 1 FROM E4 AS a, E4 AS b)
    ,cteTally AS (SELECT N = 0 UNION ALL
                SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM     E8)
SELECT includedDates = DATEADD(DAY, cte.N, '20000101')
FROM cteTally AS cte
WHERE cte.N >= DATEDIFF(DAY, '20000101', @dateBegin)
    AND cte.N <= DATEDIFF(DAY, '20000101', @dateEnd);

Most of the code you see here is to build the inline tally table. You could do the same thing with a static numbers table in your database, which is nice for reuse but will not make too much of a difference in terms of performance.

In any case, what we are doing here will avoid looping (which in not performant for SQL Server) and instead we are using one simple set of data to determine our dates.

Good luck.

Insert all dates between a date range in a table with other data

2 Answers2

Linked