CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
Mithun Sreedharan
Mithun Sreedharan

Reputation: 51282

How to find the current time slot matches with any of the table field?

I've 34 boolean fields in DailyAvailability table 

TimeSlot1, TimeSlot2, .....TimeSlot34

curreponding to 34 time slots 

07:00AM-07:30AM, 07:30AM-08:00AM, .... , 11:30PM-12:00PM

How can I get the records that matches with the present time slot say 3:30PM-4:00PM (need to construct it from GETDATE() or with DATEDIFF)

Issue is that the TimeSlot1 are simple boolean fields and has no actually mapped to time.

Current query is 

SELECT *
FROM DailyAvailability  
WHERE Present  = 1 
    AND AvailDate = LEFT(CONVERT(VARCHAR(20), GETDATE(),120),10) 
    AND ... // TimeSlotX = 1

How can I implement the last AND in the WHERE part?

Dynamic SQL?

Upvotes: 2

Views: 387

Answers (2)

podiluska
podiluska

Reputation: 51494

If it is at all possible, I'd recommend changing your table design. As you're discovering, this structure is hard to query. It's also inflexible. Solving this particular issue is only going to lead to more issues later.

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24076

try this:

select  (((datepart(HOUR,getdate())-6)*2)-1)+
         case when datepart(MI,getdate())>30 then 1 
              else 0 end [timeSlot_number]

This Query will give the current time slot number, Starting with 7-7:30 AM as TimeSlot 1

Upvotes: 2

Related Questions