0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SET NOCOUNT ON
    GO
  
  declare @t table(Customer_ID int, start_interval int, end_interval int, week_or_day varchar(6))
    
    
    declare @start_date datetime, @end_date datetime
    declare @weekMin int, @dayMin int,@hourMin int
    
    set @hourMin = 60
    set @dayMin = @hourMin*24
    set @weekMin = @dayMin*7
    
    set @start_date='1990-01-01 00:00'
    
    
    declare @s1 int, @f1 int;
    -- carsamba 18:30 carsamba 20:00
    set @s1= @dayMin*2+@hourMin*18+30
    set @f1= @dayMin*2+@hourMin*20+ 0
    
    declare @s2 int, @f2 int;
    -- sali 09:30 carsamba 23:50
    set @s2= @dayMin*1+@hourMin*9+30
    set @f2= @dayMin*2+@hourMin*23+50
    
    declare @s3 int, @f3 int;
    -- gunluk 8:25, 14:55
    set @s3 = @hourMin*8+25
    set @f3 = @hourMin*14+55
    
    insert @t values(1 ,@s1,@f1,'week') 
    insert @t values(2 ,@s2,@f2,'week')
    insert @t values(3 ,@s3,@f3,'day')

    select * from @t
    --select * from @t where DATENAME(dw, StartDateTime) = 'Sunday' OR DATENAME(dw, StartDateTime) = 'Friday'
    
    declare @curr_time datetime, @curr_time_inmins int, @remWeek int, @remDay int
    set @curr_time='2012-07-03 10:14'
    set @curr_time_inmins = DATEDIFF(minute, @start_date,@curr_time)
    set @remWeek = @curr_time_inmins%@weekMin
    set @remDay = @curr_time_inmins%@dayMin
    
    select DISTINCT * from @t where (@remWeek>start_interval AND @remWeek<end_interval AND week_or_day='week') OR
    (@remDay>start_interval AND @remDay<end_interval AND week_or_day='day')    
    /*
    set @end_date='2010-02-11'
    
    select DATENAME(weekday, @start_date)
    SELECT DATEDIFF(minute, @start_date,@end_date)
    select @hourMin 'hourmin'
    select @dayMin
    select @weekMin
    
    
  DECLARE @x INT
  */

view current version

edited jun 26 12 jon.doe8207

created jun 25 12 anonymous