Execute day/week/month/year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Execute day/week/month/year

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2004-10-12 : 04:51:59

How can I loop through multiple parameters running the same procedure for each parameter.

I have a procedure that accepts the following values:


Create Procedure LAP_aggResults
	@StartDate smalldatetime,
	@PeriodType char(1),  --Y/M/W/D |year/month/week/day
	@OptionalFilterValueID UNIQUEIDENTIFIER

Part 1
I want to pass in a date and my outside loop is Y/M/W/D with an inner loop of @OptionalFilterValueID which I select from a lookup table. ie For each OptionalFilterValueID from my lookup table the procedure must run with the PeriodType as Y then M etc.

Part2
I want to backdate this procedure for this year, as if it had been running daily since the beginning of the year. I don't want to just nest part 1 in another loop for each day this year as it will then calculate the Year to-date, month to-date & week to-date unneccessarily for each day. It should only do the calculation once for the year value for each OptionalFilterValueID, 10 time for the month values...

Thanks
Scott

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 05:38:56

well sprocs can't be called for each line.
maybe you could replace it with user defined function if it only does some calculations...
well you could build a csv of
@sql = 'exec LAP_aggResults ''15.2.2004'', ''a'', ''newid()'';exec LAP_aggResults ''15.3.2004'', ''ab', ''newid()''' ...
exec(@sql)

but that is limited to 8000 chars so you'd need a loop if it's more data....

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 08:43:43

the answer in a set-based language like SQL isn't to run a stored proc over and over, once for each day: it's to rewrite the stored procedure to act on the set of all days at once (if possible).

for example: you have a stored proc that takes a date parameter, and it updates the status of all Orders with an OrderDate on that date to "Expired".

Create Proc SetExpired(@ExpDate) as
update Orders set Status="Expired" where OrderDate = @ExpDate

Instead of calling this proc over and over for each Date to set a month of orders to be expired, you re-write the stored proc to accept a begin date and an end date:

Create Proc SetExpired(@BeginExpDate, @EndExpDate) as
update Orders set Status="Expired" where OrderDate between @BeginExpDate and @EndExpDate

Hopefully you get the idea. but the key is think of the problem in a different way, and to try to rewrite your stored procedure if possible. Post your procedure here if you need assistance with this.

- Jeff

Scott
Posting Yak Master

145 Posts

Posted - 2004-10-12 : 09:20:16

It's pretty hairy so here goes:


Alter Procedure LAP_aggResults
	@StartDate smalldatetime,
	@PeriodType char(1), 
	@IndustryID int,
	@OptionalFilterValueID UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000'
As
Declare @OFWhere varchar(1000)
If @OptionalFilterValueID = '00000000-0000-0000-0000-000000000000'
BEGIN
	set @OFWhere = ''
END
else
BEGIN
	Declare @OptionalFilter varchar(1000), @OptionalFilterID varchar(1000)
	select @OptionalFilter = OFValue, @OptionalFilterID = OptionalFilterID from OptionalFilterValue where OptionalFilterValueID = @OptionalFilterValueID
	select @OFWhere = 
		case @OptionalFilterID
			when '4D15FC62-60E2-4EDD-9C8C-9DDC9978FC61' then ' and ConfigType = '+@OptionalFilter+' '
			when '6FB9E30B-22F2-4268-82B2-CD85431A66E4' then ' and GrossMass between '+cast((cast(@OptionalFilter as int)-10)*1000 as varchar)+' and '+cast(cast(@OptionalFilter as int)*1000 as varchar)+' '
		end
END

Declare @Industry varchar(20)
Select @Industry = IndustryName from Industry where IndustryID = @IndustryID

Declare @FullWeighbridge varchar(100), @FullAggResults varchar(100)
set @FullWeighbridge = 'LAP_'+@Industry+'.dbo.FullWeighbridge'
set @FullAggResults = 'LAP_'+@Industry+'.dbo.AggResults'

Declare @Period varchar(1000)
select @Period = 
	case @PeriodType
		when 'Y' then 'datepart(year, GrossDate) = datepart(year, '''+cast(@StartDate as varchar)+''')'
		when 'M' then '(datepart(year, GrossDate) = datepart(year, '''+cast(@StartDate as varchar)+''')) and (datepart(month, GrossDate) = datepart(month, '''+cast(@StartDate as varchar)+'''))'
		when 'W' then '(datepart(year, GrossDate) = datepart(year, '''+cast(@StartDate as varchar)+''')) and (datepart(week, GrossDate) = datepart(week, '''+cast(@StartDate as varchar)+'''))'
		when 'D' then '(datepart(year, GrossDate) = datepart(year, '''+cast(@StartDate as varchar)+''')) and (datepart(month, GrossDate) = datepart(month, '''+cast(@StartDate as varchar)+''')) and (datepart(day, GrossDate) = datepart(day, '''+cast(@StartDate as varchar)+'''))'
	end

Declare @Where varchar(1000)
set @Where = ' where '+@Period+' '+@OFWhere

Declare @OFValue varchar(150)
set @OFValue = @OptionalFilterValueID

Declare @Proc5 varchar(1000), @TotTop5 decimal(9,2)
create table #temp (TotTop int) 
set @Proc5 = 'Insert into #temp Select Count(*) from '
set @Proc5 = @Proc5+'(Select top 5 percent GrossMass from '+@FullWeighbridge+' '+@Where
set @Proc5 = @Proc5+'order by GrossMass desc) a'
--print 'Proc5 ' + @Proc5
Select @TotTop5 = TotTop from #temp

Declare @Proc varchar(8000)
set @Proc = 'Insert into '+@FullAggResults+' '
set @Proc = @Proc+'select '''+convert(varchar, @StartDate, 112)+''', ''D'', '''+@OFValue+''', ''TOP'', '
set @Proc = @Proc+'(Select Avg(GrossMass) from( '
set @Proc = @Proc+'	select top 5 percent GrossMass from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+'		and GrossMass <= isnull(MaxLegal, 56000)*1.05 order by GrossMass desc) as v1) as topGross '
set @Proc = @Proc+',(Select Avg(TareMass) from( '
set @Proc = @Proc+'	select top 5 percent TareMass from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+'		and GrossMass <= isnull(MaxLegal, 56000)*1.05 order by TareMass desc) as v2) as topTare '
set @Proc = @Proc+',(Select Avg(Payload) from( '
set @Proc = @Proc+'	select top 5 percent (GrossMass-TareMass) Payload from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+'		and GrossMass <= isnull(MaxLegal, 56000)*1.05 order by Payload desc) as v3) as topPayload '
set @Proc = @Proc+',(Select stdev(Payload) from( '
set @Proc = @Proc+'	select top 5 percent (GrossMass-TareMass) Payload from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+'		order by Payload desc) as v4) as stdevPayload '
set @Proc = @Proc+',(Select Avg(MillQueue) from( '
set @Proc = @Proc+'	select top 5 percent cast(datediff(hour,arrivaldate,grossdate)as decimal(9,2)) MillQueue from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+'		order by MillQueue asc) as v5) as avgMillQueue '
set @Proc = @Proc+',(Select Avg(Offload) from( '
set @Proc = @Proc+'	select top 5 percent cast(datediff(mi,grossdate,taredate)as decimal(9,2)) Offload from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+'		order by Offload asc) as v6) as avgOffload '
set @Proc = @Proc+',(Select count(*) from( '
set @Proc = @Proc+'	select top 5 percent WBID from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+'		) as v5) as NoDeliveries '
set @Proc = @Proc+',(select cast(Count(*) as decimal(9,2))/'+cast(@TotTop5 as varchar)+' Pctover from '
set @Proc = @Proc+'(Select top 5 percent GrossMass from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+' and (GrossMass between isnull(MaxLegal, 56000)*1.015 and isnull(MaxLegal, 56000)*1.05) '
set @Proc = @Proc+'order by GrossMass desc) a) as Pctover '
set @Proc = @Proc+',(select cast(Count(*) as decimal(9,2))/'+cast(@TotTop5 as varchar)+' PctUnder from '
set @Proc = @Proc+'(Select top 5 percent GrossMass from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+' and (GrossMass <= isnull(MaxLegal, 56000)*0.985) '
set @Proc = @Proc+'order by GrossMass desc) a) as PctUnder '
set @Proc = @Proc+',(select cast(Count(*) as decimal(9,2))/'+cast(@TotTop5 as varchar)+' PctSevereOverloads from '
set @Proc = @Proc+'(Select top 5 percent GrossMass from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+' and (GrossMass >= isnull(MaxLegal, 56000)*1.05) '
set @Proc = @Proc+'order by GrossMass desc) a) as PctSevereOverloads '
set @Proc = @Proc+',(select cast(Count(*) as decimal(9,2))/'+cast(@TotTop5 as varchar)+' PctOnTargetLoads from '
set @Proc = @Proc+'(Select top 5 percent GrossMass from '+@FullWeighbridge+' '+@Where
set @Proc = @Proc+' and (GrossMass between isnull(MaxLegal, 56000)*0.985 and isnull(MaxLegal, 56000)*1.015) '
set @Proc = @Proc+'order by GrossMass desc) a) as PctOnTargetLoads '
set @Proc = @Proc+',(Select Avg(ExtentOver) from( '
set @Proc = @Proc+'	select top 5 percent (GrossMass-(isnull(MaxLegal, 56000)*1.015)) ExtentOver from '+@FullWeighbridge+' '+@Where+' and GrossMass >= (isnull(MaxLegal, 56000)*1.015) '
set @Proc = @Proc+'		order by ExtentOver asc) as v7) as avgExtentOver '
set @Proc = @Proc+',(Select Avg(ExtentUnder) from( '
set @Proc = @Proc+'	select top 5 percent ((isnull(MaxLegal, 56000)*0.985)-GrossMass) ExtentUnder from '+@FullWeighbridge+' '+@Where+' and GrossMass <= (isnull(MaxLegal, 56000)*0.985) '
set @Proc = @Proc+'		order by ExtentUnder asc) as v8) as avgExtentUnder '
print 'Proc ' + @Proc
exec(@Proc)

Declare @Proc2 varchar(8000)
set @Proc2 = 'Insert into '+@FullAggResults+' '
set @Proc2 = @Proc2+'Select '''+convert(varchar, @StartDate, 112)+''', ''D'', '''+@OFValue+''', ''AVG'', 	avg(GrossMass) AvgGross, '
set @Proc2 = @Proc2+'	avg(TareMass) AvgTare, '
set @Proc2 = @Proc2+'	avg(GrossMass-TareMass) Payload, '
set @Proc2 = @Proc2+'	stdev(GrossMass-TareMass) StDevPayload, '
set @Proc2 = @Proc2+'	avg(cast(datediff(mi,arrivaldate,grossdate) as decimal(9,2)))/60 AvgMillQueue, '
set @Proc2 = @Proc2+'	avg(cast(datediff(mi,grossdate,taredate) as decimal(9,2)))/60 AvgOffload, '
set @Proc2 = @Proc2+'	Count(WBID) NoDeliveries, '
set @Proc2 = @Proc2+'	sum(case when GrossMass between (isnull(MaxLegal, 56000)*1.015) and (isnull(MaxLegal, 56000)*1.05) then 1 else 0 end)/cast(count(WBID) as decimal(9,2)) PctOver, '
set @Proc2 = @Proc2+'	sum(case when GrossMass <= (isnull(MaxLegal, 56000)*0.985) then 1 else 0 end)/cast(count(WBID) as decimal(9,2)) PctUnder, '
set @Proc2 = @Proc2+'	sum(case when GrossMass >= (isnull(MaxLegal, 56000)*1.05) then 1 else 0 end)/cast(count(WBID) as decimal(9,2)) PctSevereOverloads, '
set @Proc2 = @Proc2+'	sum(case when GrossMass between (isnull(MaxLegal, 56000)*0.985) and (isnull(MaxLegal, 56000)*1.015) then 1 else 0 end)/cast(count(WBID) as decimal(9,2)) PctOnTargetLoads, '
set @Proc2 = @Proc2+'	avg(case when GrossMass > (isnull(MaxLegal, 56000)*1.015) then GrossMass - (isnull(MaxLegal, 56000)*1.015) else null end) ExtentOver, '
set @Proc2 = @Proc2+'	avg(case when GrossMass < (isnull(MaxLegal, 56000)*0.985) then (isnull(MaxLegal, 56000)*1.015) - GrossMass else null end) ExtentUnder '
set @Proc2 = @Proc2+'from LAP_Timber.dbo.FullWeighbridge '
set @Proc2 = @Proc2+@Where+' '
print 'Proc2 ' + @Proc2
exec(@Proc2)

Most of the top part of the procedure is to build up the where clause and the appropriate variables for the Dynamic SQL so it can run on various databases.

The dynamic sql Proc produces a whole bunch of agregated results for the top 5% of deliveries. Proc3 produces average values across all deliveries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources