Q&A for administrators and users of the CiviCRM Constituent Relationship Management software
SET NOCOUNT ON GO declare @t table(id int, baslangic datetime, bitis datetime, tekrar bit, gunluk bit, aralik_bas int, aralik_son int,zaman_id int) INSERT INTO @t VALUES (63, '2012-6-26 11:35:57', '2013-12-30 23:59:00', 1, 0, 1000, 8000, 1); INSERT INTO @t VALUES (16, '2012-6-5 11:44:15', '2013-12-30 23:59:00', 1, 0, 2010, 4310, 2); INSERT INTO @t VALUES (12, '2012-5-29 11:46:03', '2013-12-30 23:59:00', 1, 1, 565, 895, 3); INSERT INTO @t VALUES (16, '2012-7-4 10:14:15', '2013-11-10 23:59:00', 0, 0, 0, 0, 5); INSERT INTO @t VALUES (16, '2012-1-2 11:44:15', '2013-10-05 23:59:00', 0, 0, 0, 0, 7); 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' --select * from @t declare @curr_time datetime, @curr_time_inmins int, @remWeek int, @remDay int set @curr_time=CURRENT_TIMESTAMP set @curr_time_inmins = DATEDIFF(minute, @start_date,@curr_time) set @remWeek = @curr_time_inmins%@weekMin set @remDay = @curr_time_inmins%@dayMin SELECT * FROM @t WHERE ( (@curr_time>baslangic AND @curr_time<bitis AND tekrar=0) OR (@curr_time>baslangic AND @curr_time<bitis AND @remWeek>=aralik_bas AND @remWeek<=aralik_son AND tekrar=1 AND gunluk = 0) OR (@curr_time>baslangic AND @curr_time<bitis AND @remDay>=aralik_bas AND @remDay<=aralik_son AND tekrar=1 AND gunluk = 1)) AND zaman_id=7