March 3, 2010 at 6:32 am
Hi,
I have a table having fields as given below (all columns are bit datatype)
MON TUE WED THU FRI
I need to write a select query to fetch records based on the current day. Does anybody have an idea to implement it.
For example : if the current day is Monday the column ‘MON’ should be true.
Thanks in advance
Saeed
March 3, 2010 at 7:27 am
Hi Saeed,
You could try something like this (air code):
with cte as (
select MyTable.*,
case when MON = 1 then 1
when TUE = 1 then 2
when WED = 1 then 3
when THU = 1 then 4
when FRI = 1 then 5
else 6 as Picker
end
)
select *
from cte
where Picker = datepart(dw,getdate())
This depends on your DATEFIRST setting (you might want to check out this excellent article that shows how to nullify this: http://www.sqlservercentral.com/articles/DateFirst/69203/)
It also assumes that only one of the fields is 1 for any given record. If you can have multiple fields with a 1 populated the you'll need to revise the approach.
Regards, Iain
March 4, 2010 at 7:06 am
Thank you Iain for your hints. I wrote a SP and I have calculated date part first. Based on the date part I have created the filter which I have added to the select query. Query is given below.
DECLARE @sqlquery varchar(1000), @dayFilter varchar(100),@day int
SELECT @day=DATEPART(DW,getdate())
--print @day
IF@day=2 BEGIN SET @dayFilter=' AND T.MON = 1' END
ELSE IF @day=3 BEGIN SET @dayFilter=' AND T.TUE = 1' END
ELSE IF @day=4 BEGIN SET @dayFilter=' AND T.WED = 1' END
ELSE IF @day=5 BEGIN SET @dayFilter=' AND T.THU = 1' END
ELSE IF @day=6 BEGIN SET @dayFilter=' AND T.FRI = 1' END
--print @dayFilter
SET @sqlquery = 'SELECT * from table where condition1 and condition2 ' + @dayFilter
print @sqlquery
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply