|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2009-07-28 : 13:48:40
|
| The code below is intended to count the on time deliveries per month, beginning in july of 2008. In sql mgmt studio, the code works, and drop out 13 records with the aggregate data. when I drop the same code into a ssis dataflow task, by putting the code in the sql option for the ole db source object, it only completes the first loop..dropping only the july '08 record into the target table....HELP!----------------------------------------------------------------------- This code will start in FY09 and count the ontime delivery per month, and calculate the percentage of ontime deliveries.declare @monthstart datetime,@monthend datetime,@ReportPeriod varchar(15),@StartDate datetime, @loop intset @StartDate = '7/2/2008'set @loop = datediff(mm,@StartDate, getdate())+1 -- 13 loopsWhile @loop >0beginset @MonthStart = DATEADD(mm, DATEDIFF(mm,0,@StartDate), 0)set @MonthEnd = DATEADD(s,-1,DATEADD(m,1,@MonthStart))--select @monthstart, @monthend,@reportdatedeclare @x float,@y float;select @x = count(*) -- OnTime ,@ReportPeriod = substring(CONVERT(VARCHAR(11), @monthstart, 106),4,11)FROM HPD_HelpDesk WHERE dateadd(s,resolved_time,'19700101') between @MonthStart and @MonthEndand datediff(hh,dateadd(s,Create_Time,'19700101'),dateadd(s,resolved_time,'19700101'))/24.<=5select @y = count(*) -- Total FROM HPD_HelpDesk WHERE dateadd(s,Create_Time,'19700101')>='2008-07-01 00:00:00' and dateadd(s,resolved_time,'19700101') between @MonthStart and @MonthEndSELECT @ReportPeriod as ReportPeriod ,@x as OnTime ,@y as Total ,case when @y = 0 then 0 else @x/@y end as PercentOntime ,getdate() AS Entered set @StartDate = @StartDate + 31 set @loop=@loop -1end----------------------------------------------------------------sql mgmt studio will write out 13 records in this format:ReportPeriod OnTime Total PercentOnTime EnteredJul 2008 8585 9256 .9275 ... 2009-07-28 12:42:05Aug 2008 .............Sep 2008Oct 2008....... |
|
does the while begin loop work in ssis?