Declare @StartDate as Datetime
SET @StartDate='2014-06-26'
Declare @EndDate as Datetime
SET @EndDate='2014-07-26'
Declare @YearMonth as varchar(6)
set @YearMonth='201407'
Declare @CurrDate as Datetime
SET @CurrDate=@StartDate
Declare @ct as int
set @ct=1;
Declare @dcnt as int
set @dcnt=0
while (@startDate<@EndDate)
begin
if (datepart(dw,@startdate)<> 1) and( datepart(dw,@startdate)<>7)
begin
if @dcnt=0
begin
set @CurrDate=@startdate
end
set @dcnt=@dcnt+1
end
if (@dcnt=5) or (@startDate=dateadd(dd,-1,@EndDate))
begin
insert into Dest
SELECT 'User Name','','AVMI0'+ CAST(@ct AS VARCHAR(2))+ @YearMonth,'task'+CAST(@ct AS VARCHAR(2)),@startdate,@CurrDate,@dcnt,@dcnt*8,'','' As [Resolution],DATEADD(s, 2, @CurrDate),@startdate,@dcnt,@dcnt*8,'','' As [Created By],@CurrDate,'Closed(16)',DATEADD(s, 1, @CurrDate),DATEADD(s, 2, @CurrDate),'','','','','','','','','','','','','',@startdate,'','','','','','','','','','','','','',''
set @ct=@ct+1
set @dcnt=0
end
SET @startdate=dateadd(dd,1,@startdate)
end
go
select * from Dest
