|
SELECT P.Period, d1.VariableLabel, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D1.CalculationVariableID), '') as value FROM VW_LMS_BusinessPeriods AS P Left Join #DetailTempTable AS D1 on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d1.EffectiveDate) and d1.BusinessClassID = P.BusinessClassID and P.BusinessID = @BUS_ID and d1.VariableLabel in ( SELECT CV.VariableLabel FROM VW_LMS_CalculationVariables AS CV INNER JOIN VW_LMS_BusinessPeriods AS P ON CV.BusinessClassID = P.BusinessClassID AND dbo.forceDate(P.EffectiveDate) = dbo.forceDate(CV.EffectiveDate) WHERE P.BusinessID = @BUS_ID )
|
This is more what I had in mind:
|
SELECT P.Period , IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D1.CalculationVariableID), '') as [Additional Rate] , IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D2.CalculationVariableID), '') as [Base Rate] , IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D3.CalculationVariableID), '') as [BID Assessed?] , IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D4.CalculationVariableID), '') as [Original Tax Due] , IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D5.CalculationVariableID), '') as [TaxTotal] FROM VW_LMS_BusinessPeriods AS P Left Join #DetailTempTable AS D1 on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d1.EffectiveDate) and d1.BusinessClassID = P.BusinessClassID and d1.VariableLabel = 'Additional Rate' Left Join #DetailTempTable AS D2 on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d2.EffectiveDate) and d2.BusinessClassID = P.BusinessClassID and d2.VariableLabel = 'Base Rate' Left Join #DetailTempTable AS D3 on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d3.EffectiveDate) and d3.BusinessClassID = P.BusinessClassID and d3.VariableLabel = 'BID Assessed?' Left Join #DetailTempTable AS D4 on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d4.EffectiveDate) and d4.BusinessClassID = P.BusinessClassID and d4.VariableLabel = 'Original Tax Due' Left Join #DetailTempTable AS D5 on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d5.EffectiveDate) and d5.BusinessClassID = P.BusinessClassID and d5.VariableLabel = 'TaxTotal' WHERE P.BusinessID = @BUS_ID |
Note that the above should actually be changed to not run forceDate(x) on the #DetailTempTable table since it would be more efficient to run it during the INSERT INTO step. Also note that you should be using a variable based table instead of a temporary table. temporary tables have a physical presence and are logged. Variable based tables have neither. They are also cleaned up automatically as soon as you leave scope like every other declared variable.
|
declare @DetailTempTable TABLE ( BusinessClassID INT , VariableLabel Varchar(50) , CalculationVariableID INT , EffectiveDate DateTime ) INSERT INSERT @DetailTempTable (BusinessClassID, VariableLabel, CalculationVariableID, EffectiveDate) .... |
I truely hope this helps and would be very interested if you obtain any performance comparison between this and your current approach. If you already tried a cursor or similar approach and have performance information on that it would be of interest to me as well. Our database has had a couple stored procedures that contained a cursor or even --- eck --- a nested cursor that took on the order of 3 - 5 minutes to run (even the fairly simple single cursor one). I recently recoded them to perform data gathering into a variable based table and then massage the results using up to around 8 queries against it and cut the run times down to 2-4 seconds. I'd be interested in knowing whether the trick holds up here as well.
Good luck.