CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Abhishek Borse
Abhishek Borse

Reputation: 13

How to assign shift to time from SQL Server

I want to create a stored procedure for API. When user inputs 'morning', then it will get data according to that given time.

In SQL Server, table we have a FromTime column.

DECLARE @isdaytime varchar(20) = 'morning'

SELECT
    md.doctor_id, hdr.ScheduleId, md.first_name, md.last_name,
    det.FromTime, det.ToTime 
FROM
    mst_doctor md 
INNER JOIN
    DoctorScheduleHdr hdr ON hdr.DoctorId = md.doctor_id 
INNER JOIN
    DoctorScheduleDet det ON hdr.ScheduleId = det.ScheduleId
WHERE
    CASE 
        WHEN (@isdaytime = 'morning') 
            THEN det.FromTime BETWEEN CAST('09:00:00.0000000' AS time) AND CAST('11:59:00.0000000' AS time) 
    CASE 
        WHEN (@isdaytime = 'afternoon') 
            THEN det.FromTime BETWEEN CAST('12:00:00.0000000' AS time) AND CAST('14:59:00.0000000' AS time) 
    CASE 
        WHEN (@isdaytime = 'evening') 
            THEN det.FromTime BETWEEN CAST('15:00:00.0000000' AS time)  AND CAST('20:59:00.0000000' AS time)

Upvotes: 0

Views: 113

Answers (1)

Zhorov
Zhorov

Reputation: 29993

You need to use the appropriate boolean condition in the WHEREclause:

...
where 
   ((@isdaytime = 'morning') and (det.FromTime between cast('09:00:00.0000000'as time) and cast('11:59:00.0000000'as time))) or
   ((@isdaytime = 'afternoon') and (det.FromTime between cast('12:00:00.0000000'as time) and cast('14:59:00.0000000'as time))) or 
   ((@isdaytime = 'evening') and (det.FromTime between cast('15:00:00.0000000'as time) and cast('20:59:00.0000000'as time)))

As an additional note, in T-SQL CASE is not a "Control-of-Flow" language element, it's an expression.

Upvotes: 2

Related Questions