BEGIN
declare @day datetime
declare @maxdate datetime
set @maxdate = getdate()
set @day = Convert(Varchar(10),DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0),110)
truncate table tblOpenTicketsPerDay
while  @day <= @maxdate
begin
insert into tblOpenTicketsPerDay
select convert(varchar(20),datename(dw,@day)) day_name, count(*) Open_Tickets from
(select [status], createdate, closedate
FROM OPENROWSET('MSDASQL','DSN=SQLite','Select * from tickets') AS a
where createdate <= @day
except
select [status], createdate, closedate
FROM OPENROWSET('MSDASQL','DSN=SQLite','Select * from tickets') AS a
where closedate <= @day and [status] = 'closed') x
set @day = @day + 1
end
END