开发者

SQL Where Clause Logic

开发者 https://www.devze.com 2023-03-14 19:57 出处:网络
I have a table that contains an id field along with 5 fields, one for each week-day from Monday to Friday, where datatype is bit.

I have a table that contains an id field along with 5 fields, one for each week-day from Monday to Friday, where datatype is bit.

The table looks something like:

+---+--------+---------+-----------+----------+--------+
|id | Monday | Tuesday | Wednesday | Thursday | Friday |
+---+--------+---------+-----------+----------+--------+
| 1 |      1 |       0 |         0 |        0 |      0 |
+---+--------+---------+-----------+----------+--------+
| 2 |      1 |       0 |         0 |        0 |      0 |
+---+--------+---------+-----------+----------+--------+
| 3 |      0 |   开发者_开发问答    0 |         0 |        1 |      0 |
+---+--------+---------+-----------+----------+--------+
| 4 |      1 |       0 |         0 |        0 |      1 |
+---+--------+---------+-----------+----------+--------+

Depending on the day I'm trying to return the rows that have the bit set to true for that day. I was thinking of doing this with a where clause but can't get it to work.

I think there is something wrong in my logic, any help is greatly appreciated!

DECLARE @tDay as INTEGER
SET @tDay = datepart(weekday, getdate())

SELECT     id, monday, tuesday, wednesday, thursday, friday
FROM         Days
WHERE   CASE WHEN @tDay = 2 then @tDay 
        Else Days.monday
        End = 1
        AND 
        CASE WHEN @tDay = 3 then @tDay 
        Else Days.tuesday
        End = 1 


This will work if SET DATEFIRST is 7. You need to adjust if you have some other day as first day of the week.

DECLARE @tDay as INTEGER
SET @tDay = datepart(weekday, getdate())

select id, monday, tuesday, wednesday, thursday, friday
from Days
where case @tDay
        when 2 then monday
        when 3 then tuesday
        when 4 then wednesday
        when 5 then thursday
        when 6 then friday
      end = 1


SELECT     id, monday, tuesday, wednesday, thursday, friday
FROM         Days
WHERE
        CASE @tDay
          WHEN 2 THEN monday
          WHEN 3 THEN tuesday 
          WHEN 4 THEN wednesday
          WHEN 5 THEN thursday
          WHEN 6 THEN friday
          ELSE NULL
        END  = 1
0

精彩评论

暂无评论...
验证码 换一张
取 消