1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
| /*
create table matable
(
nom varchar(50),
date_ent datetime,
date_sort datetime
);
insert into matable (nom,date_ent,date_sort)
values ('toto','2011-01-05','2011-01-05');
insert into matable (nom,date_ent,date_sort)
values ('tata','2011-01-04','2011-01-06');
insert into matable (nom,date_ent,date_sort)
values ('titi','2011-01-04','2011-01-04');
insert into matable (nom,date_ent,date_sort)
values ('tutu','2011-01-06','2011-01-08');
*/
CREATE TABLE #tmp_date
(
date_int int NOT NULL,
date_date datetime,
nb int);
declare @int int;
SET @int=0;
while @int < 365
BEGIN
INSERT INTO #tmp_date (date_int,date_date,nb)
select date1,date2,COUNT(*) from (
SELECT cast(REPLACE(CONVERT(varchar(10),cast('2011-01-01' AS datetime),102),'.','') AS int)+@int as date1,
DATEADD(day,@int,cast('2011-01-01' AS datetime)) as date2
FROM matable
WHERE date_ent <=DATEADD(day,@int,cast('2011-01-01' AS datetime))
AND date_sort >=DATEADD(day,@int,cast('2011-01-01' AS datetime))
) a
group by date1,date2;
SET @int=@int + 1;
END;
SELECT date_date,nb FROM #tmp_date ;
drop table #tmp_date; |
Partager