Sql-server – Getting 30 minute slots between time

datetimesql servertime

I have a Start Datetime and a End Datetime.

Eg: 10:00 am - 12:00 pm

I have to create 4 slots between the, each ranging for 30 minutes.

Eg:  
10:00 am-10:30 am
10:30 am-11:00 am
11:00 am-11:30 am
11:30 am-12:00 pm

This 30 minutes can vary and is not a constant. I have tried a few things but they don't seem to work. Can someone please help. Thank you.

I have tried this but i only get the slot difference not the slotfrom-slotto

with cte as  
(  
select   convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)) as DATE   
union all    
select   c.DATE+'00:20:00' as QuarterTime  
from cte c   
where c.date<dateadd(minute,1422,convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)))
)
select * from cte

Best Answer

My idea is not far from yours.

DECLARE @dtStart AS DATETIME ='20151028 10:00:00'
        ,@dtEnd AS DATETIME = '20151028 12:00:00'
        ,@iInterval AS INT = 30;  --30 min interval


WITH aCTE
AS(
    SELECT 
        @dtStart AS StartDateTime,
        DATEADD(MINUTE,@iInterval,@dtStart) AS EndDateTime
    UNION ALL
    SELECT 
        DATEADD(MINUTE,@iInterval,StartDateTime),
        DATEADD(MINUTE,@iInterval,EndDateTime)
    FROM aCTE
    WHERE
        DATEADD(MINUTE,@iInterval,EndDateTime) <= @dtEnd
)

SELECT 
    -- 10:00:00 AM 
    CONVERT(VARCHAR(10),StartDateTime,108) 
    + ' ' + RIGHT(CONVERT(VARCHAR(30), StartDateTime, 9), 2) 
    + ' - ' +
    -- 10:30:00 AM
    CONVERT(VARCHAR(10),EndDateTime,108) 
    + ' ' + RIGHT(CONVERT(VARCHAR(30), EndDateTime, 9), 2) AS Result
FROM aCTE

And the output is:

Result
10:00:00 AM - 10:30:00 AM
10:30:00 AM - 11:00:00 AM
11:00:00 AM - 11:30:00 AM
11:30:00 AM - 12:00:00 PM

Related Solutions

Sql-server – How to avoid using variables in WHERE clause

Parameter sniffing is your friend almost all of the time and you should write your queries so that it can be used. Parameter sniffing helps building the plan for you using the parameter values available when the query is compiled. The dark side of parameter sniffing is when the values used when compiling the query is not optimal for the queries to come.

The query in a stored procedure is compiled when the stored procedure is executed, not when the query is executed so the values that SQL Server has to deal with here...

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

is a known value for @endDate and an unknown value for @startDate. That will leave SQL Server to guessing on 30% of the rows returned for the filter on @startDate combined with whatever the statistics tells it for @endDate. If you have a big table with a lot of rows that could give you a scan operation where you would benefit most from a seek.

Your wrapper procedure solution makes sure that SQL Server sees the values when DateRangeProc is compiled so it can use known values for both @endDate and @startDate.

Both your dynamic queries leads to the same thing, the values are known at compile-time.

The one with a default null value is a bit special. The values known to SQL Server at compile-time is a known value for @endDate and null for @startDate. Using a null in a between will give you 0 rows but SQL Server always guess at 1 in those cases. That might be a good thing in this case but if you call the stored procedure with a large date interval where a scan would have been the best choice it may end up doing a bunch of seeks.

I left "Use the DATEADD() function directly" to the end of this answer because it is the one I would use and there is something strange with it as well.

First off, SQL Server does not call the function multiple times when it is used in the where clause. DATEADD is considered runtime constant.

And I would think that DATEADD is evaluated when the query is compiled so that you would get a good estimate on the number of rows returned. But it is not so in this case.
SQL Server estimates based on the value in the parameter regardless of what you do with DATEADD (tested on SQL Server 2012) so in your case the estimate will be the number of rows that is registered on @endDate. Why it does that I don't know but it has to do with the use of the datatype DATE. Shift to DATETIME in the stored procedure and the table and the estimate will be accurate, meaning that DATEADD is considered at compile time for DATETIME not for DATE.

So to summarize this rather lengthy answer I would recommend the wrapper procedure solution. It will always allow SQL Server to use the values provided when compiling the the query without the hassle of using dynamic SQL.

PS:

In comments you got two suggestions.

OPTION (OPTIMIZE FOR UNKNOWN) will give you an estimate of 9% of rows returned and OPTION (RECOMPILE) will make SQL Server see the parameter values since the query is recompiled every time.

Sql-server – How to dynamically generate rows based on value in a column

This is an example of when a "Number" table becomes very handy! If you are not familiar with this concept, read this blog post from Adam Machanic: You REQUIRE a Numbers Table

The numbers table is used to join to the Appointment Slots column (in this case) to duplicate each row AppointmentSlots number of times.

-- A simple recreation of your CTE data...
CREATE  TABLE #AppointmentPeriods
(
    AppointmentLocation CHAR(1),
    AppointmentStartTime TIME,
    AppointmentEndTime TIME,
    AppointmentSlotsAvailable SMALLINT
)

INSERT  INTO #AppointmentPeriods VALUES
    ('A', '09:00', '09:15', 2),
    ('A', '09:15', '09:30', 2),
    ('A', '09:30', '09:45', 2),
    -- ....
    ('A', '16:30', '16:45', 2),
    ('A', '16:45', '17:00', 2),
    ('B', '09:00', '09:15', 1),
    ('B', '09:15', '09:30', 1),
    ('B', '09:30', '09:45', 1),
    -- ....
    ('B', '16:30', '16:45', 1),
    ('B', '16:45', '17:00', 1)


-- Numbers table
CREATE  TABLE #Numbers (NumberValue SMALLINT)

INSERT  INTO #Numbers
-- TOP value should be changed so it is greater than the
-- maximum number of potential appointment slots any location can have
SELECT  TOP 20
        ROW_NUMBER() OVER (ORDER BY object_id)
FROM    sys.objects


SELECT  #AppointmentPeriods.*, NumberValue AS AppointmentSlotNumber
FROM    #AppointmentPeriods
        INNER JOIN #Numbers
            ON AppointmentSlotsAvailable >= NumberValue

Query output:

Location | StartTime | EndTime | AppointmentSlots | SlotNumber
A        | 09:00     | 09:15   | 2                | 1
A        | 09:00     | 09:15   | 2                | 2
A        | 09:15     | 09:30   | 2                | 1
A        | 09:15     | 09:30   | 2                | 2
B        | 16:30     | 16:45   | 1                | 1
B        | 16:45     | 17:00   | 1                | 1

PS: There are many ways to generate a numbers table: SO: What is the best way to create and populate a numbers table?

Best Answer

Related Solutions

Sql-server – How to avoid using variables in WHERE clause

Sql-server – How to dynamically generate rows based on value in a column

Related Question