SQL Server: Need query to get records by comparing one of it's field with different values -

need query records comparing 1 of it's field different values

here table

repeated 1 means daily 2 means weekly 3 means monthly

i need query records going tele caste , being tele casting comapring current date , time.

thank

do mean this?

--case daily: need check times select      *      schedules     repeated = 1     --this compare if starts within next hour, may have bugs not tested     --logic if start time - 1 hour less start time more must start within next hour     , dateadd(day, -datediff(day, 0, dateadd(hour,startdate),-1) ), dateadd(hour,startdate),-1) ) < dateadd(day, -datediff(day, 0, now()), now())       , dateadd(day, -datediff(day, 0, startdate), startdate)   > dateadd(day, -datediff(day, 0, now()), now())   union  --case weekly: day of week same, if check times select      *      schedules     repeated = 2     , datepart(dw,startdate) = datepart(dw,now)     , dateadd(day, -datediff(day, 0, dateadd(hour,startdate),-1) ), dateadd(hour,startdate),-1) ) < dateadd(day, -datediff(day, 0, now()), now())     , dateadd(day, -datediff(day, 0, startdate), startdate)   > dateadd(day, -datediff(day, 0, now()), now())    union  --case monthly day of month same, if compare times select      *      schedules     repeated = 3     , datepart(day,startdate) = datepart(day,now)     , dateadd(day, -datediff(day, 0, dateadd(hour,startdate),-1) ), dateadd(hour,startdate),-1) ) < dateadd(day, -datediff(day, 0, now()), now())     , dateadd(day, -datediff(day, 0, startdate), startdate)   > dateadd(day, -datediff(day, 0, now()), now())   

and monthly based on date of month , number of weeks (e.g. 4) or week of month + weekday (e.g. second tuesday of month)

edit: think should work monthly cycle means on day of month every month.

the above modified use separate time column using field time comparisons e.g.:

...     repeated = 3     , datepart(day,startdate) = datepart(day,now)     , dateadd(day, -datediff(day, 0, dateadd(hour,starttime),-1) ), dateadd(hour,starttime),-1) ) < dateadd(day, -datediff(day, 0, now()), now())     , dateadd(day, -datediff(day, 0, starttime), starttime)   > dateadd(day, -datediff(day, 0, now()), now())   

explanation:

the normal way process question human be:

• whats start date
• whats repeating cycle
• does startdate * multiple of repeating cycle period = today
• if on now

this procedural, step based solution

when think of set based problem need know set of programs on now

i allowed add sets (union) cannot step through rows considering each one.. split 3 sets , add them together:

• daily shows on every day need check times
• weekly shows on every week on same day, if weekly if days of week matches compare times
• monthly shows on same day of month every months, if day of month compare times

hope helps.. sorry if explanation crap, trying :d