[thelist] DTS Task

[Brian Cummiskey]

Luther, Ron wrote:
> Rob Smith asked:
> 
> 
>>>I've got a web report on order fulfillment that the big dogs want on a
> 
> 
>>>weekly basis. Rather than me go into the report every Monday morning
> 
> and 
> 
>>>update the time range in the query, 


You should be able to do this in the DTS.


DECLARE @startdate datetime
DECLARE @enddate datetime
DECLARE @dayOfWeek INT


SET @dayOfWeek = DATEPART(dw, GETDATE())
	IF @dayOfWeek = 1 --Monday, need to update the range
		set @startdate = dateadd(dd,datediff(dd,0,getdate()),0)
		set @enddate = dateadd(dd,7, at startdate)
	IF @dayOfWeek = 2
		set @startdate = dateadd(dd,datediff(dd,0,getdate()-1),0)
		set @enddate = dateadd(dd,7, at startdate)
	IF @dayOfWeek = 3
		set @startdate = dateadd(dd,datediff(dd,0,getdate()-2),0)
		set @enddate = dateadd(dd,7, at startdate)

.
.
.
and so on for the other 4 days


use the @startdate and @enddate var's in your procedure as your dynamic 
range.



I think this should get you headed in the right direction.