CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Sam
Sam

Reputation: 1908

How to select records that fall between start time and end time where end time is for next day

I am working on the discount to allow discount to be applied when ordering on specific time.

Everything is working fine, but I have a weird request from customers. My customer has a midnight special. They want to create a special with up to 50% off (based on $$$ amount order) when their customer placing their order within 2 hours window (from 11pm - 1am).

As you can see, 1am is for the next day. So, it is from 11pm today to 1am next day

Currently here is my stored procedures (it will select the best discount based on total spend):

CREATE PROCEDURE [uspGetBestDiscount]
(
    ...
    ...
    ...
    @TotalSpend FLOAT = 0,
    @OrderDate DATETIME = NULL,     --NULL to use today date.
    @DiscountRate FLOAT = NULL OUT,
    @DiscountId INT = NULL OUT
)
AS
BEGIN
    IF (@OrderDate IS NULL) SET @OrderDate = GETDATE();
    DECLARE @OrderTime TIME = CAST(@OrderDate AS TIME)

    SET @DiscountId = NULL; SET @DiscountRate = NULL;
    SELECT TOP 1 @DiscountId = [DiscountId], @DiscountRate = [DiscountRate] 
    FROM [tblDiscount]
    WHERE (@TotalSpend >= [MinSpend]) AND
          (@OrderTime >= CAST([StartTime] AS TIME)) AND
          (@OrderTime <= CAST([EndTime] AS TIME)) AND
          ...
          ...
          ...
    ORDER BY [DiscountRate] DESC;
END;

Everything is working fine as long as StartTime <= EndTime. Any idea how to make EndTime as next day when StartTime > EndTime?

Thanks

Upvotes: 0

Views: 64

Answers (1)

kmoser
kmoser

Reputation: 9273

WHERE (@TotalSpend >= [MinSpend]) AND
(
    (
        /* E.g. 8pm - 10pm: */
        (
            CAST([StartTime] AS TIME)
                <
            CAST([EndTime] AS TIME)
        )
            AND
        (
            (@OrderTime >= CAST([StartTime] AS TIME))
                AND /* Note: AND */
            (@OrderTime <= CAST([EndTime] AS TIME))
        )
    )
        OR
    (
        /* E.g. 11pm - 1am: */
        (
            CAST([StartTime] AS TIME)
                >
            CAST([EndTime] AS TIME)
        )
            AND
        (
            @OrderTime >= CAST([StartTime] AS TIME)
                OR /* Note: OR */
            @OrderTime <= CAST([EndTime] AS TIME)
        )
    )
)
...

Upvotes: 1

Related Questions