November 24, 2008 at 6:24 pm
I am moving a database from Access to SQL Server and ran into some difficulties with queries that refer to derived columns. The queries have some complex calculations and refer to lots of derived columns, these are very easy to do in access because you can simply refer to previously derived columns. I managed to recreate the queries in SQL, but they ended up being so complex (with lots of reduntant code) that they are almost impossible to update (or understand). I tried the scalar user defined functions but the arguments lists got too long and unwieldy.
I have re-produced one of the access queries using table valued user defined functions. Initially I had problems with this method and solved them by incrementally looping through the data and performing the calculations. To reduce the number of time the source data is queried and declared a local table in the function, imported all the source data (from a query linking several tables) and loop through the local table instead. So basically I am doing the following in the function -
1. Specifying the structure of the table to be returned (ie. all the calculated data)
2. Declaring local variables to hold the source data - for use in calculations
3. Declaring local variables to hold the calculated data
4. Declaring a local table to hold the imported source data
5. Importing the source data into the local table using a query
6. Commencing an incremental loop (loops through the local table)
7. Assigning the source data in the local table to the local variables
8. Calculating the required data using the local source variables (storing result in local variables)
9. Inserting the calculated data in the local variables into the final table to be returned
10. Looping back to go to the next record
This seems like a very tedious way to accomplish something that was very simple in access. So I'd like to know the following -
- Is using table valued user defined functions the easiest way to do what I need
- Will this method be slow for bigger data sets
- Is it possible to use the calculated fields in the function without incrementally looping through the data
- Is importing the underlying data into a local table (with a primary key) the best way to process
- Is there an easier way to use a local table without having to specify the structure.
I have another 10 queries similar to this one to do, so I want to make sure I'm on the right track. Here's the contents of the functions I have done already -
-----------------------------------------------------------------
-- This function returns calculated data for Bridges
-----------------------------------------------------------------
ALTER FUNCTION [spatial].[BR_Calcs_Fn]()
RETURNS
-----------------------------------------------------------------
-- Table structure for calculated data to be returned
-----------------------------------------------------------------
@BR_Calcs_Tbl TABLE
(
CALC_ID_PK int PRIMARY KEY NOT NULL,
BR_JOBS_ID_PK_CALC int,
BR_Traffic_Vol_Score decimal(24,10),
BR_Detour_Length_Score decimal(24,10),
Priority_Score decimal(24,10),
Benifit_Maintenance decimal(24,10),
VPD_to_Detour decimal(24,10),
Time_factor_sub decimal(24,10),
Time_Factor decimal(24,10),
Operating_Factor decimal(24,10),
Safety_Factor decimal(24,10),
Detour_Cost_per_Day decimal(24,10),
Benifit_Commercial_Vehical_Detour_Cost decimal(24,10),
Benifit_Floodway_Safety_Cost decimal(24,10),
Benifit_Obstruction_Safety_Cost decimal(24,10),
BENIFIT decimal(24,10),
Upgraded_Bridge_Area decimal(24,10),
Construction_Cost decimal(24,10),
Design_Etc_Cost decimal(24,10),
Total_Cost decimal(24,10),
COST decimal(24,10),
BCR decimal(24,10),
Total_Score decimal(24,10),
Renual_Amount decimal(24,10)
)
AS
BEGIN
DECLARE
-----------------------------------------------------------------
-- Declaration of local variables to store source data --
-----------------------------------------------------------------
@TEMP_ID_PK int,
@BR_JOBS_ID_PK_TEMP int,
@BR_Traffic_Volume_VPD decimal(24,10),
@BR_Detour_length_required_km decimal(24,10),
@BR_Condition_Score decimal(24,10),
@BR_Closure_Score decimal(24,10),
@BR_Flood_Safety_Score decimal(24,10),
@BR_Load_Limit_Score decimal(24,10),
@BR_Pot_maintenance_Score decimal(24,10),
@BR_Width_Score decimal(24,10),
@BR_Existing_Length_m decimal(24,10),
@BR_Existing_Width_m decimal(24,10),
@BR_FACTORS_evaluation_period decimal(24,10),
@BR_Condition_maintenance_m2 decimal(24,10),
@BR_Pot_maintenance_Factor decimal(24,10),
@BR_Com_Veh_percentage decimal(24,10),
@BR_Load_Limit_CV_in_load_range decimal(24,10),
@BR_Time_to_take_Detour_mins decimal(24,10),
@BR_Detour_Accident_Cost decimal(24,10),
@BR_Flood_Events_FLoodways_5yrs decimal(24,10),
@BR_Flood_Safety_multiplier decimal(24,10),
@BR_Floodway_Accident_Cost decimal(24,10),
@BR_FACTORS_pot_Obstruction_factor decimal(24,10),
@BR_Obstruction_Dist_multiplier decimal(24,10),
@BR_Upgrade_Area_Multiplier decimal(24,10),
@BR_FACTORS_Construction_Cost_m2 decimal(24,10),
@BR_FACTORS_Design_etc_Cost decimal(24,10),
@BR_Override_Cost decimal(24,10),
@BR_FACTORS_Renual_Cost_m2 decimal(24,10),
-----------------------------------------------------------------
-- Declaration of local variables to store calculated data --
-----------------------------------------------------------------
@BR_Traffic_Vol_Score decimal(24,10),
@BR_Detour_Length_Score decimal(24,10),
@Priority_Score decimal(24,10),
@Benifit_Maintenance decimal(24,10),
@VPD_to_Detour decimal(24,10),
@Time_factor_sub decimal(24,10),
@Time_Factor decimal(24,10),
@Operating_Factor decimal(24,10),
@Safety_Factor decimal(24,10),
@Detour_Cost_per_Day decimal(24,10),
@Benifit_Commercial_Vehical_Detour_Cost decimal(24,10),
@Benifit_Floodway_Safety_Cost decimal(24,10),
@Benifit_Obstruction_Safety_Cost decimal(24,10),
@BENIFIT decimal(24,10),
@Upgraded_Bridge_Area decimal(24,10),
@Construction_Cost decimal(24,10),
@Design_Etc_Cost decimal(24,10),
@Total_Cost decimal(24,10),
@COST decimal(24,10),
@BCR decimal(24,10),
@Total_Score decimal(24,10),
@Renual_Amount decimal(24,10),
@i int
-----------------------------------------------------------------
-- Declaration of local table to temporarily store source data
-----------------------------------------------------------------
DECLARE
@Temp_Source_Data TABLE
(
TEMP_ID_PK int IDENTITY(1,1) PRIMARY KEY NOT NULL,
BR_JOBS_ID_PK_TEMP int,
BR_Traffic_Volume_VPD decimal(24,10),
BR_Detour_length_required_km decimal(24,10),
BR_Condition_Score decimal(24,10),
BR_Closure_Score decimal(24,10),
BR_Flood_Safety_Score decimal(24,10),
BR_Load_Limit_Score decimal(24,10),
BR_Pot_maintenance_Score decimal(24,10),
BR_Width_Score decimal(24,10),
BR_Existing_Length_m decimal(24,10),
BR_Existing_Width_m decimal(24,10),
BR_FACTORS_evaluation_period decimal(24,10),
BR_Condition_maintenance_m2 decimal(24,10),
BR_Pot_maintenance_Factor decimal(24,10),
BR_Com_Veh_percentage decimal(24,10),
BR_Load_Limit_CV_in_load_range decimal(24,10),
BR_Time_to_take_Detour_mins decimal(24,10),
BR_Detour_Accident_Cost decimal(24,10),
BR_Flood_Events_FLoodways_5yrs decimal(24,10),
BR_Flood_Safety_multiplier decimal(24,10),
BR_Floodway_Accident_Cost decimal(24,10),
BR_FACTORS_pot_Obstruction_factor decimal(24,10),
BR_Obstruction_Dist_multiplier decimal(24,10),
BR_Upgrade_Area_Multiplier decimal(24,10),
BR_FACTORS_Construction_Cost_m2 decimal(24,10),
BR_FACTORS_Design_etc_Cost decimal(24,10),
BR_Override_Cost decimal(24,10),
BR_FACTORS_Renual_Cost_m2 decimal(24,10)
)
-----------------------------------------------------------------
-- Insert source data to temporary table, query only run once
-----------------------------------------------------------------
INSERT @Temp_Source_Data
(
BR_JOBS_ID_PK_TEMP,
BR_Traffic_Volume_VPD,
BR_Detour_length_required_km,
BR_Condition_Score,
BR_Closure_Score,
BR_Flood_Safety_Score,
BR_Load_Limit_Score,
BR_Pot_maintenance_Score,
BR_Width_Score,
BR_Existing_Length_m ,
BR_Existing_Width_m,
BR_FACTORS_evaluation_period,
BR_Condition_maintenance_m2,
BR_Pot_maintenance_Factor,
BR_Com_Veh_percentage,
BR_Load_Limit_CV_in_load_range,
BR_Time_to_take_Detour_mins,
BR_Detour_Accident_Cost,
BR_Flood_Events_FLoodways_5yrs,
BR_Flood_Safety_multiplier,
BR_Floodway_Accident_Cost,
BR_FACTORS_pot_Obstruction_factor,
BR_Obstruction_Dist_multiplier,
BR_Upgrade_Area_Multiplier,
BR_FACTORS_Construction_Cost_m2,
BR_FACTORS_Design_etc_Cost,
BR_Override_Cost,
BR_FACTORS_Renual_Cost_m2
)
SELECT
BR_JOBS_ID_PK,
BR_Traffic_Volume_VPD,
BR_Detour_length_required_km,
BR_Condition_Score,
BR_Closure_Score,
BR_Flood_Safety_Score,
BR_Load_Limit_Score,
BR_Pot_maintenance_Score,
BR_Width_Score,
BR_Existing_Length_m,
BR_Existing_Width_m,
BR_FACTORS_evaluation_period,
[BR_Condition_maintenance_m2-yr],
BR_Pot_maintenance_Factor,
BR_Com_Veh_percentage,
[BR_Load_Limit_%CV_in_load_range],
BR_Time_to_take_Detour_mins,
BR_FACTORS_Detour_Accident_Cost,
BR_Flood_Events_FLoodways_5yrs,
BR_Flood_Safety_multiplier,
BR_Factors_Floodway_Accident_Cost,
BR_FACTORS_pot_Obstruction_factor,
BR_Obstruction_Dist_multiplier,
BR_FACTORS_Upgrade_Area_Multiplier,
BR_FACTORS_Construction_Cost_m2,
[BR_FACTORS_Design_etc-Cost],
BR_Override_Cost,
BR_FACTORS_Renual_Cost_m2
FROM
(spatial.CAPITAL_WORKS_JOBS LEFT JOIN
spatial.[Capital Works type] ON spatial.CAPITAL_WORKS_JOBS.Job_Type_FK = spatial.[Capital Works type].Capital_Works_Type_ID) RIGHT JOIN
(((((((((spatial.BR_JOBS LEFT JOIN
spatial.BR_FACTORS ON spatial.BR_JOBS.BR_factors_link_FK = spatial.BR_FACTORS.BR_FACTORS_ID_PK) LEFT JOIN
spatial.BR_Obstruction_Distance ON spatial.BR_JOBS.BR_Obstruction_Dist_FK = spatial.BR_Obstruction_Distance.BR_Obstruction_Dist_ID_PK) LEFT JOIN
spatial.BR_Condition_Existing ON spatial.BR_JOBS.BR_Existing_Manual_Condition_FK = spatial.BR_Condition_Existing.BR_Condition_ID_PK) LEFT JOIN
spatial.BR_Potential_Maintenance ON spatial.BR_JOBS.BR_Potential_Maintenance_Cost_FK = spatial.BR_Potential_Maintenance.BR_Pot_Maintenance_ID_PK) LEFT JOIN
spatial.BR_Width_of_Existing ON spatial.BR_JOBS.BR_Width_Existing_FK = spatial.BR_Width_of_Existing.BR_Width_ID_PK) LEFT JOIN
spatial.BR_Load_Limit ON spatial.BR_JOBS.BR_Load_Limit_FK = spatial.BR_Load_Limit.BR_Load_Limit_ID_PK) LEFT JOIN
spatial.BR_Closure ON spatial.BR_JOBS.BR_Closure_Duration_FK = BR_Closure.BR_Closure_ID_PK) LEFT JOIN
spatial.BR_Flood_Safety_Risk ON spatial.BR_JOBS.BR_Flood_Safety_Risk_FK = spatial.BR_Flood_Safety_Risk.BR_Flood_Safety_ID_PK) LEFT JOIN
spatial.BR_Commercial_Vehicals ON spatial.BR_JOBS.BR_Percentage_Commercial_Vehicals_FK = spatial.BR_Commercial_Vehicals.BR_Com_Veh_ID_PK) ON spatial.CAPITAL_WORKS_JOBS.CAPITAL_WORKS_JOBS_ID_PK = spatial.BR_JOBS.BR_JOBS_ID_PK
ORDER BY
BR_JOBS_ID_PK
-----------------------------------------------------------------
-- Begin incremental loop
-----------------------------------------------------------------
SET @i = 1
WHILE @i <= (SELECT MAX(TEMP_ID_PK) FROM @Temp_Source_Data)
BEGIN
-----------------------------------------------------------------
-- Assign source data from local temp table to local variables
-----------------------------------------------------------------
SELECT
@TEMP_ID_PK = TEMP_ID_PK,
@BR_JOBS_ID_PK_TEMP = BR_JOBS_ID_PK_TEMP,
@BR_Traffic_Volume_VPD = BR_Traffic_Volume_VPD,
@BR_Detour_length_required_km = BR_Detour_length_required_km,
@BR_Condition_Score = BR_Condition_Score,
@BR_Closure_Score = BR_Closure_Score,
@BR_Flood_Safety_Score = BR_Flood_Safety_Score,
@BR_Load_Limit_Score = BR_Load_Limit_Score,
@BR_Pot_maintenance_Score = BR_Pot_maintenance_Score,
@BR_Width_Score = BR_Width_Score,
@BR_Existing_Length_m = BR_Existing_Length_m,
@BR_Existing_Width_m = BR_Existing_Width_m,
@BR_FACTORS_evaluation_period = BR_FACTORS_evaluation_period,
@BR_Condition_maintenance_m2 = BR_Condition_maintenance_m2,
@BR_Pot_maintenance_Factor = BR_Pot_maintenance_Factor,
@BR_Com_Veh_percentage = BR_Com_Veh_percentage,
@BR_Load_Limit_CV_in_load_range = BR_Load_Limit_CV_in_load_range,
@BR_Time_to_take_Detour_mins = BR_Time_to_take_Detour_mins,
@BR_Detour_Accident_Cost = BR_Detour_Accident_Cost,
@BR_Flood_Events_FLoodways_5yrs = BR_Flood_Events_FLoodways_5yrs,
@BR_Flood_Safety_multiplier = BR_Flood_Safety_multiplier,
@BR_Floodway_Accident_Cost = BR_Floodway_Accident_Cost,
@BR_FACTORS_pot_Obstruction_factor = BR_FACTORS_pot_Obstruction_factor,
@BR_Obstruction_Dist_multiplier = BR_Obstruction_Dist_multiplier,
@BR_Upgrade_Area_Multiplier = BR_Upgrade_Area_Multiplier,
@BR_FACTORS_Construction_Cost_m2 = BR_FACTORS_Construction_Cost_m2,
@BR_FACTORS_Design_etc_Cost = BR_FACTORS_Design_etc_Cost,
@BR_Override_Cost = BR_Override_Cost,
@BR_FACTORS_Renual_Cost_m2 = BR_FACTORS_Renual_Cost_m2
FROM
@Temp_Source_Data
WHERE
TEMP_ID_PK = @i
-----------------------------------------------------------------
-- Calculate local value of BR_Traffic_Vol_Score
-----------------------------------------------------------------
If @BR_Traffic_Volume_VPD < 50 Begin Set @BR_Traffic_Vol_Score = 0 End
Else If @BR_Traffic_Volume_VPD < 200 Begin Set @BR_Traffic_Vol_Score = 5 End
Else If @BR_Traffic_Volume_VPD < 500 Begin Set @BR_Traffic_Vol_Score = 10 End
Else If @BR_Traffic_Volume_VPD < 2000 Begin Set @BR_Traffic_Vol_Score = 15 End
Else Begin Set @BR_Traffic_Vol_Score = 20 End
-----------------------------------------------------------------
-- Calculate local value of BR_Detour_Length_Score
-----------------------------------------------------------------
If @BR_Detour_length_required_km < 1 Begin Set @BR_Detour_Length_Score = 0 End
Else If @BR_Detour_length_required_km < 5 Begin Set @BR_Detour_Length_Score = 5 End
Else If @BR_Detour_length_required_km < 10 Begin Set @BR_Detour_Length_Score = 10 End
Else Begin Set @BR_Detour_Length_Score = 20 End
-----------------------------------------------------------------
-- Calculate local value of Priority_Score
-----------------------------------------------------------------
SET @Priority_Score =
@BR_Condition_Score +
@BR_Closure_Score +
@BR_Flood_Safety_Score +
@BR_Load_Limit_Score +
@BR_Pot_maintenance_Score +
@BR_Width_Score +
@BR_Traffic_Vol_Score +
@BR_Detour_Length_Score
-----------------------------------------------------------------
-- Calculate local value of Benifit_Maintenance
-----------------------------------------------------------------
SET @Benifit_Maintenance =
@BR_Existing_Length_m *
@BR_Existing_Width_m *
@BR_FACTORS_evaluation_period *
@BR_Condition_maintenance_m2 *
@BR_Pot_maintenance_Factor
-----------------------------------------------------------------
-- Calculate local value of VPD_to_Detour
-----------------------------------------------------------------
SET @VPD_to_Detour =
@BR_Com_Veh_percentage *
@BR_Load_Limit_CV_in_load_range
-----------------------------------------------------------------
-- Calculate local value of Time_Factor_sub
-----------------------------------------------------------------
SET @Time_Factor_sub =
@BR_Traffic_Volume_VPD *
@VPD_to_Detour *
23.0000000000 *
(power((1+0.0300000000),8))
-----------------------------------------------------------------
-- Calculate local value of Time_Factor
-----------------------------------------------------------------
SET @Time_Factor =
@Time_factor_sub *
@BR_Time_to_take_Detour_mins /
60.0000000000
-----------------------------------------------------------------
-- Calculate local value of Operating_Factor
-----------------------------------------------------------------
SET @Operating_Factor =
@VPD_to_Detour *
0.8000000000 *
POWER(1 + 0.0300000000, 8) *
@BR_Detour_length_required_km
-----------------------------------------------------------------
-- Calculate local value of Safety_Factor
-----------------------------------------------------------------
SET @Safety_Factor =
@BR_Detour_Accident_Cost * 0.850000 *
spatial.BR_VPD_to_Detour(@BR_Com_Veh_percentage, @BR_Load_Limit_CV_in_load_range) *
@BR_Traffic_Volume_VPD / 1000000.000000 *
@BR_Detour_length_required_km
-----------------------------------------------------------------
-- Calculate local value of Detour_Cost_per_Day
-----------------------------------------------------------------
SET @Detour_Cost_per_Day =
@Time_Factor +
@Operating_Factor +
@Safety_Factor
-----------------------------------------------------------------
-- Calculate local value of Benefit_Commercial_Vehical_Detour_Cost
-----------------------------------------------------------------
SET @Benifit_Commercial_Vehical_Detour_Cost =
@BR_FACTORS_evaluation_period *
365.0000000000 *
@Detour_Cost_per_Day
-----------------------------------------------------------------
-- Calculate local value of Benifit_Floodway_Safety_Cost
-----------------------------------------------------------------
SET @Benifit_Floodway_Safety_Cost =
@BR_Flood_Events_FLoodways_5yrs *
(@BR_FACTORS_evaluation_period / 5.000000) *
@BR_Flood_Safety_multiplier *
@BR_Traffic_Volume_VPD *
@BR_Floodway_Accident_Cost
-----------------------------------------------------------------
-- Calculate local value of Benefit_Obstruction_Safety_Cost
-----------------------------------------------------------------
SET @Benifit_Obstruction_Safety_Cost =
@BR_FACTORS_pot_Obstruction_factor *
@BR_Traffic_Volume_VPD *
@BR_FACTORS_evaluation_period *
@BR_Floodway_Accident_Cost *
@BR_Existing_Length_m *
@BR_Obstruction_Dist_multiplier
-----------------------------------------------------------------
-- Calculate local value of Benefit
-----------------------------------------------------------------
SET @Benifit =
@Benifit_Obstruction_Safety_Cost +
@Benifit_Floodway_Safety_Cost +
@Benifit_Commercial_Vehical_Detour_Cost +
@Benifit_Maintenance
-----------------------------------------------------------------
-- Calculate local value of Upgraded_Bridge_Area
-----------------------------------------------------------------
SET @Upgraded_Bridge_Area =
@BR_Existing_Length_m *
@BR_Existing_Width_m *
@BR_Upgrade_Area_Multiplier
-----------------------------------------------------------------
-- Calculate local value of Construction_Cost
-----------------------------------------------------------------
SET @Construction_Cost =
@Upgraded_Bridge_Area *
@BR_FACTORS_Construction_Cost_m2
-----------------------------------------------------------------
-- Calculate local value of Design_Etc_Cost
-----------------------------------------------------------------
SET @Design_Etc_Cost =
@Construction_Cost *
@BR_FACTORS_Design_etc_Cost
-----------------------------------------------------------------
-- Calculate local value of Total_Cost
-----------------------------------------------------------------
If @BR_Override_Cost > 0 Begin SET @Total_Cost = @BR_Override_Cost End
Else Begin SET @Total_Cost = @Construction_Cost + @Design_Etc_Cost End
-----------------------------------------------------------------
-- Calculate local value of Cost
-----------------------------------------------------------------
SET @Cost = @Total_Cost
-----------------------------------------------------------------
-- Calculate local value of BCR
-----------------------------------------------------------------
SET @BCR =
@Benifit /
@Total_Cost
-----------------------------------------------------------------
-- Calculate local value of Total_Score
-----------------------------------------------------------------
SET @Total_Score =
@BCR *
@Priority_Score
-----------------------------------------------------------------
-- Calculate local value of Renual_Amount
-----------------------------------------------------------------
SET @Renual_Amount =
@BR_Existing_Length_m *
@BR_Existing_Width_m *
@BR_FACTORS_Renual_Cost_m2
-----------------------------------------------------------------
-- Insert calculated local variables into return table
-----------------------------------------------------------------
INSERT
@BR_Calcs_Tbl
(
CALC_ID_PK,
BR_JOBS_ID_PK_CALC,
BR_Traffic_Vol_Score,
BR_Detour_Length_Score,
Priority_Score,
Benifit_Maintenance,
VPD_to_Detour,
Time_factor_sub,
Time_Factor,
Operating_Factor,
Safety_Factor,
Detour_Cost_per_Day ,
Benifit_Commercial_Vehical_Detour_Cost,
Benifit_Floodway_Safety_Cost,
Benifit_Obstruction_Safety_Cost,
BENIFIT,
Upgraded_Bridge_Area,
Construction_Cost,
Design_Etc_Cost,
Total_Cost,
COST,
BCR,
Total_Score,
Renual_Amount
)
SELECT
@TEMP_ID_PK AS CALC_ID_PK,
@BR_JOBS_ID_PK_TEMP AS BR_JOBS_ID_PK_CALC,
@BR_Traffic_Vol_Score AS BR_Traffic_Vol_Score,
@BR_Detour_Length_Score AS BR_Detour_Length_Score,
@Priority_Score AS Priority_Score,
@Benifit_Maintenance AS Benifit_Maintenance,
@VPD_to_Detour AS VPD_to_Detour,
@Time_factor_sub AS Time_factor_sub,
@Time_Factor AS Time_Factor,
@Operating_Factor AS Operating_Factor,
@Safety_Factor AS Safety_Factor,
@Detour_Cost_per_Day AS Detour_Cost_per_Day ,
@Benifit_Commercial_Vehical_Detour_Cost AS Benifit_Commercial_Vehical_Detour_Cost,
@Benifit_Floodway_Safety_Cost AS Benifit_Floodway_Safety_Cost,
@Benifit_Obstruction_Safety_Cost AS Benifit_Obstruction_Safety_Cost,
@BENIFIT AS BENIFIT,
@Upgraded_Bridge_Area AS Upgraded_Bridge_Area,
@Construction_Cost AS Construction_Cost,
@Design_Etc_Cost AS Design_Etc_Cost,
@Total_Cost AS Total_Cost,
@COST AS COST,
@BCR AS BCR,
@Total_Score AS Total_Score,
@Renual_Amount AS Renual_Amount
FROM
@Temp_Source_Data
WHERE
TEMP_ID_PK = @i
-----------------------------------------------------------------
-- Increment loop
-----------------------------------------------------------------
SET @i = @i +1
END
RETURN
END
GO
November 24, 2008 at 7:35 pm
RBAR! You should be able to do this in a set based query. First thing to ask, WHAT are trying to accomplish, NOT HOW do you do it. Answer me that question one, and then we'll see what step two shall be. Plus, the code is long and I really dont' want to try and figure out what you are doing based on the code.
November 24, 2008 at 8:19 pm
To add to what Lynn has stated - I will say that you need to look in Books Online for the subjects: CTE (Common Table Expression), Derived Tables and CASE. A couple of examples:
-- CTE
;WITH myCTE (col1, col2, col3)
AS (SELECT col1, col1 + 10 AS col2, col1 + 20 AS col3 FROM myTable WHERE id = my_id)
SELECT col1, col2 + col3 AS col4 FROM myCTE;
-- Derived Table
SELECT col1, col2 + col3 AS col4
FROM (SELECT col1, col1 + 10 AS col2, col1 + 20 AS col3 FROM myTable WHERE id = my_id);
-- CASE
SELECT CASE WHEN col1 < 50 THEN 0
WHEN col1 < 40 THEN 0
ELSE 10 END AS myCOL
FROM myTable
WHERE id = my_id;
Using combinations of the above, I am absolutely sure you can eliminate all of the RBAR, functions and other inefficient processing in the function you have created.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 24, 2008 at 8:24 pm
Some sample data and expected results based on the sample data would also be quite welcome.
November 24, 2008 at 8:29 pm
Jeffrey Williams (11/24/2008)
-- CASE
SELECT CASE WHEN col1 < 50 THEN 0
WHEN col1 < 40 THEN 0
ELSE 10 END AS myCOL
FROM myTable
WHERE id = my_id;
Slight problem with this case statement.
SELECT CASE WHEN col1 < 50 THEN 0
WHEN col1 < 40 THEN 0 -- Will never be evaluated
ELSE 10 END AS myCOL
FROM
myTable
WHERE
id = my_id;
Should be:
SELECT CASE WHEN col1 < 40 THEN 0
WHEN col1 < 50 THEN 0
ELSE 10 END AS myCOL
FROM
myTable
WHERE
id = my_id;
Not that it really matters, both will result in the same value.
November 24, 2008 at 8:41 pm
Lynn,
Thanks for the reply, the "what i'm trying to accomplish" is to move an access database to an SQL backend and keep the access forms in the front end. The access database has several queries the calculate columns of data and then use the results of those columns to calculate further columns of data, basically you might have "a + b" as "c" and "d + e" as "f" then the final calculation would be "c + f" = "g". obviously you could write "a + b + d + e" as "g", but the actual formulas use 5 or 6 "levels" of referred derived columns, re-use one derived colums several time and I need to display the interim values for each step as well. For example, I calculate the "Traffic_Volume_Score" which is then used to calculate the "Benefit_Floodway_Safety_Cost" which is then used to calculate the "Benefit" which is then used to calculate the "Benefit_Cost_Ratio" which is then used to calculate the "Total Score". The initial "Traffic_Volume_Score" is also used to calculate lots of other columns, so I didn't really want to have it duplicated lots of times in case we want to change it. Using the function, the "Traffic_Volume_Score" is only specified once, and then all other columns just refer to the one piece of data. As per my original message I did manage to create the acess query using a SQL query, but it was very cumbersome, difficult to decipher and hard to update - this may have been a result of my newbie SQL skills though.
November 24, 2008 at 9:01 pm
Paul:
What we are looking for is a Functional definition, not an Operational description. "I want to move my access query to SQL Server" is an operational description. Please provide a description of what you are trying to do in functional specifications.
If you are concerned about the complexity of this, and just want to understand what methods you can use, then provide a simplified example that we can show you how to implement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 24, 2008 at 9:02 pm
The thing is, what I am seeing is a very procedural process. When moving to SQL, you have to change your mind set to a set based approach. This may work quite well for several to a few hundred rows, but as you approach 1000's and more, you will see a significant change in performance.
I am currently working on a different approach to this particular function, and I hope when I finish, it will show you a new and different way to look at problems.
To be sure my changes work correctly, I will need sample data and the expected results based on the sample data. I would think that 5 to 10 rows of data would be good. To make it easier for me, please provide a CREATE TABLE statement for the table to hold the source data. The sample data should be insert statements that I can cut, paste, and execute in SSMS to load the table. What would also be great is to have the expected results in a similar fashion; a table to hold the expected results, and the expected results in the form of insert statements. This way I can use SQL to compare the results directly.
For more info on this, read the article in my signature block about the best way to ask for help (the first link below).
November 24, 2008 at 9:15 pm
To make things easier, provide me with sample data as it would come as the result of your major query, you know the one with all those left and right outer joins? This way you don't have to create me all the source tables and sample data for those. We'll start with the assumpation that that query is okay. You should know, however, I have rewritten it slightly, I reformatted it and aliased the tables so I could see it all without having scroll sideways as well.
November 24, 2008 at 9:55 pm
Lynn,
Thanks very much for your offer of assistance, below is a sample (10 rows) CSV file from the major query providing the source data. Further down is the contents of a CSV file of the results (10 rows). Let me know if you would like me to format it any differently.
Thank you
BR_JOBS_ID_PK,BR_Traffic_Volume_VPD,BR_Detour_length_required_km,BR_Condition_Score,BR_Closure_Score,BR_Flood_Safety_Score,BR_Load_Limit_Score,BR_Pot_maintenance_Score,BR_Width_Score,BR_Existing_Length_m,BR_Existing_Width_m,BR_FACTORS_evaluation_period,BR_Condition_maintenance_m2-yr,BR_Pot_maintenance_Factor,BR_Com_Veh_percentage,BR_Load_Limit_%CV_in_load_range,BR_Time_to_take_Detour_mins,BR_FACTORS_Detour_Accident_Cost,BR_Flood_Events_FLoodways_5yrs,BR_Flood_Safety_multiplier,BR_Factors_Floodway_Accident_Cost,BR_FACTORS_pot_Obstruction_factor,BR_Obstruction_Dist_multiplier,BR_FACTORS_Upgrade_Area_Multiplier,BR_FACTORS_Construction_Cost_m2,BR_FACTORS_Design_etc-Cost,BR_Override_Cost,BR_FACTORS_Renual_Cost_m2
1,200,20,14,0,10,10,10,0,9.5,8,20,$170.00,0.5,0.05,0.6,15,"$90,000.00",0,0.00000100,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$380,000.00","$2,150.00"
2,30,20,16,0,0,10,20,5,18.2,3.7,20,$190.00,1,0.05,0.6,15,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$247,811.00","$2,150.00"
3,0,20,16,0,0,5,20,0,14,4.5,20,$190.00,1,0,0.3,15,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0.3,1.6,"$2,300.00",0.1,"$75,000.00","$2,150.00"
4,670,3,16,0,0,20,20,5,20.2,5.2,20,$190.00,1,0.05,1,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0.6,1.6,"$2,300.00",0.1,"$600,000.00","$2,150.00"
5,1200,3,14,0,0,0,10,0,21,18,20,$170.00,0.5,0.05,0,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0,1.6,"$2,300.00",0.1,"$300,000.00","$2,150.00"
6,10000,3,10,0,0,0,10,0,21,13.7,20,$100.00,0.5,0.05,0,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0,1.6,"$2,300.00",0.1,"$300,000.00","$2,150.00"
7,5000,1,12,0,0,10,10,5,20,7.1,20,$150.00,0.5,0.05,0.6,1,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0.3,1.6,"$2,300.00",0.1,"$522,560.00","$2,150.00"
8,30,20,12,0,10,10,10,5,27.9,5.5,20,$150.00,0.5,0.05,0.6,15,"$90,000.00",0,0.00000100,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$564,696.00","$2,150.00"
9,30,7.5,16,0,10,10,20,0,10,5,20,$190.00,1,0.05,0.6,6,"$90,000.00",0,0.00000100,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$184,000.00","$2,150.00"
10,5,3,14,0,0,20,20,0,12.2,4,20,$170.00,1,0.05,1,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$179,584.00","$2,150.00"
BR_Traffic_Vol_Score,BR_Detour_Length_Score,Priority_Score,Benifit_Maintenance,VPD_to_Detour,Time_factor_sub,Time_Factor,Operating_Factor,Safety_Factor,Detour_Cost_per_Day,Benifit_Commercial_Vehical_Detour_Cost,Benifit_Floodway_Safety_Cost,Benifit_Obstruction_Safety_Cost,BENIFIT,Upgraded_Bridge_Area,Construction_Cost,Design_Etc_Cost,Total_Cost,COST,BCR,Total_Score,Renual_Amount
10,20,74,129200,3.00%,174.8143,43.7036,0.6080,9.180,$53.49,390488.74,$0.00,$904.40,520593.14,121.600,"$279,680","$27,968","$380,000","$380,000",1.3700,101.379,"$163,400"
0,20,71,255892,3.00%,26.2221,6.5555,0.6080,1.377,$8.54,62346.20,$0.00,$260.26,318498.46,107.744,"$247,811","$24,781","$247,811","$247,811",1.2852,91.253,"$144,781"
0,20,61,239400,0.00%,0.0000,0.0000,0.0000,0.000,$0.00,0.00,$0.00,$0.00,239400.00,100.800,"$231,840","$23,184","$75,000","$75,000",3.1920,194.712,"$135,450"
15,5,81,399152,5.00%,976.0463,32.5346,0.1520,7.688,$40.37,294736.16,$0.00,"$3,867.49",697755.65,168.064,"$386,547","$38,655","$600,000","$600,000",1.1629,94.197,"$225,836"
15,5,44,642600,0.00%,0.0000,0.0000,0.0000,0.000,$0.00,0.00,$0.00,$0.00,642600.00,604.800,"$1,391,040","$139,104","$300,000","$300,000",2.1420,94.248,"$812,700"
20,5,45,287700,0.00%,0.0000,0.0000,0.0000,0.000,$0.00,0.00,$0.00,$0.00,287700.00,460.320,"$1,058,736","$105,874","$300,000","$300,000",0.9590,43.155,"$618,555"
20,5,62,213000,3.00%,4370.3568,72.8364,0.0304,11.475,$84.34,615694.91,$0.00,"$14,286.00",842980.91,227.200,"$522,560","$52,256","$522,560","$522,560",1.6132,100.017,"$305,300"
0,20,67,230175,3.00%,26.2221,6.5555,0.6080,1.377,$8.54,62346.20,$0.00,$398.97,292920.17,245.520,"$564,696","$56,470","$564,696","$564,696",0.5187,34.754,"$329,918"
0,10,66,190000,3.00%,26.2221,2.6222,0.2280,0.516,$3.37,24576.21,$0.00,$143.00,214719.21,80.000,"$184,000","$18,400","$184,000","$184,000",1.1670,77.019,"$107,500"
0,5,59,165920,5.00%,7.2839,0.2428,0.1520,0.057,$0.45,3300.94,$0.00,$29.28,169250.22,78.080,"$179,584","$17,958","$179,584","$179,584",0.9425,55.605,"$104,920"
November 24, 2008 at 10:08 pm
Lynn,
I just realised that the server running the application is one that hasn't been upgraded to 2005 and is still running 2000 - I also noticed that there is a SQL 2000 - SQL Server Newbies forum as well. I'm very sorry for the incorrect posting, hopefully your code is not SQL 2005 specific.
I tried creating a CTE which sounds like what I originally wanted to do (before going into the TV UDF), but it doesn't work on SQL 2000.
November 24, 2008 at 10:22 pm
Paul Nicholson (11/24/2008)
Lynn,Thanks very much for your offer of assistance, below is a sample (10 rows) CSV file from the major query providing the source data. Further down is the contents of a CSV file of the results (10 rows). Let me know if you would like me to format it any differently.
Thank you
BR_JOBS_ID_PK,BR_Traffic_Volume_VPD,BR_Detour_length_required_km,BR_Condition_Score,BR_Closure_Score,BR_Flood_Safety_Score,BR_Load_Limit_Score,BR_Pot_maintenance_Score,BR_Width_Score,BR_Existing_Length_m,BR_Existing_Width_m,BR_FACTORS_evaluation_period,BR_Condition_maintenance_m2-yr,BR_Pot_maintenance_Factor,BR_Com_Veh_percentage,BR_Load_Limit_%CV_in_load_range,BR_Time_to_take_Detour_mins,BR_FACTORS_Detour_Accident_Cost,BR_Flood_Events_FLoodways_5yrs,BR_Flood_Safety_multiplier,BR_Factors_Floodway_Accident_Cost,BR_FACTORS_pot_Obstruction_factor,BR_Obstruction_Dist_multiplier,BR_FACTORS_Upgrade_Area_Multiplier,BR_FACTORS_Construction_Cost_m2,BR_FACTORS_Design_etc-Cost,BR_Override_Cost,BR_FACTORS_Renual_Cost_m2
1,200,20,14,0,10,10,10,0,9.5,8,20,$170.00,0.5,0.05,0.6,15,"$90,000.00",0,0.00000100,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$380,000.00","$2,150.00"
2,30,20,16,0,0,10,20,5,18.2,3.7,20,$190.00,1,0.05,0.6,15,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$247,811.00","$2,150.00"
3,0,20,16,0,0,5,20,0,14,4.5,20,$190.00,1,0,0.3,15,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0.3,1.6,"$2,300.00",0.1,"$75,000.00","$2,150.00"
4,670,3,16,0,0,20,20,5,20.2,5.2,20,$190.00,1,0.05,1,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0.6,1.6,"$2,300.00",0.1,"$600,000.00","$2,150.00"
5,1200,3,14,0,0,0,10,0,21,18,20,$170.00,0.5,0.05,0,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0,1.6,"$2,300.00",0.1,"$300,000.00","$2,150.00"
6,10000,3,10,0,0,0,10,0,21,13.7,20,$100.00,0.5,0.05,0,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0,1.6,"$2,300.00",0.1,"$300,000.00","$2,150.00"
7,5000,1,12,0,0,10,10,5,20,7.1,20,$150.00,0.5,0.05,0.6,1,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,0.3,1.6,"$2,300.00",0.1,"$522,560.00","$2,150.00"
8,30,20,12,0,10,10,10,5,27.9,5.5,20,$150.00,0.5,0.05,0.6,15,"$90,000.00",0,0.00000100,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$564,696.00","$2,150.00"
9,30,7.5,16,0,10,10,20,0,10,5,20,$190.00,1,0.05,0.6,6,"$90,000.00",0,0.00000100,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$184,000.00","$2,150.00"
10,5,3,14,0,0,20,20,0,12.2,4,20,$170.00,1,0.05,1,2,"$90,000.00",0,0.00000010,"$100,000.00",0.0000002381,1,1.6,"$2,300.00",0.1,"$179,584.00","$2,150.00"
BR_Traffic_Vol_Score,BR_Detour_Length_Score,Priority_Score,Benifit_Maintenance,VPD_to_Detour,Time_factor_sub,Time_Factor,Operating_Factor,Safety_Factor,Detour_Cost_per_Day,Benifit_Commercial_Vehical_Detour_Cost,Benifit_Floodway_Safety_Cost,Benifit_Obstruction_Safety_Cost,BENIFIT,Upgraded_Bridge_Area,Construction_Cost,Design_Etc_Cost,Total_Cost,COST,BCR,Total_Score,Renual_Amount
10,20,74,129200,3.00%,174.8143,43.7036,0.6080,9.180,$53.49,390488.74,$0.00,$904.40,520593.14,121.600,"$279,680","$27,968","$380,000","$380,000",1.3700,101.379,"$163,400"
0,20,71,255892,3.00%,26.2221,6.5555,0.6080,1.377,$8.54,62346.20,$0.00,$260.26,318498.46,107.744,"$247,811","$24,781","$247,811","$247,811",1.2852,91.253,"$144,781"
0,20,61,239400,0.00%,0.0000,0.0000,0.0000,0.000,$0.00,0.00,$0.00,$0.00,239400.00,100.800,"$231,840","$23,184","$75,000","$75,000",3.1920,194.712,"$135,450"
15,5,81,399152,5.00%,976.0463,32.5346,0.1520,7.688,$40.37,294736.16,$0.00,"$3,867.49",697755.65,168.064,"$386,547","$38,655","$600,000","$600,000",1.1629,94.197,"$225,836"
15,5,44,642600,0.00%,0.0000,0.0000,0.0000,0.000,$0.00,0.00,$0.00,$0.00,642600.00,604.800,"$1,391,040","$139,104","$300,000","$300,000",2.1420,94.248,"$812,700"
20,5,45,287700,0.00%,0.0000,0.0000,0.0000,0.000,$0.00,0.00,$0.00,$0.00,287700.00,460.320,"$1,058,736","$105,874","$300,000","$300,000",0.9590,43.155,"$618,555"
20,5,62,213000,3.00%,4370.3568,72.8364,0.0304,11.475,$84.34,615694.91,$0.00,"$14,286.00",842980.91,227.200,"$522,560","$52,256","$522,560","$522,560",1.6132,100.017,"$305,300"
0,20,67,230175,3.00%,26.2221,6.5555,0.6080,1.377,$8.54,62346.20,$0.00,$398.97,292920.17,245.520,"$564,696","$56,470","$564,696","$564,696",0.5187,34.754,"$329,918"
0,10,66,190000,3.00%,26.2221,2.6222,0.2280,0.516,$3.37,24576.21,$0.00,$143.00,214719.21,80.000,"$184,000","$18,400","$184,000","$184,000",1.1670,77.019,"$107,500"
0,5,59,165920,5.00%,7.2839,0.2428,0.1520,0.057,$0.45,3300.94,$0.00,$29.28,169250.22,78.080,"$179,584","$17,958","$179,584","$179,584",0.9425,55.605,"$104,920"
Okay, I don't normally do this, but I just spent about 4 hours of my own time working on a new solution for your problem, and based on the information you provided me above, you couldn't take the time to give me what I asked for in the format I asked for it, AND had even given you a reference to an article that would have helped you put it altogether for me.
You know what I am going to do? I am going to give you all the code I just wrote and let you do the testing and debugging. I will give you another reference that will help you, should you decide to that the time to read it, and that is the second link in my signature block. I used the running total calculation in that article as a basis for this code.
Have fun, as I think I'm done.
create table #Temp_Source_Data (
TEMP_ID_PK int IDENTITY(1,1) PRIMARY KEY NOT NULL,
BR_JOBS_ID_PK_TEMP int,
BR_Traffic_Volume_VPD decimal(24,10),
BR_Detour_length_required_km decimal(24,10),
BR_Condition_Score decimal(24,10),
BR_Closure_Score decimal(24,10),
BR_Flood_Safety_Score decimal(24,10),
BR_Load_Limit_Score decimal(24,10),
BR_Pot_maintenance_Score decimal(24,10),
BR_Width_Score decimal(24,10),
BR_Existing_Length_m decimal(24,10),
BR_Existing_Width_m decimal(24,10),
BR_FACTORS_evaluation_period decimal(24,10),
BR_Condition_maintenance_m2 decimal(24,10),
BR_Pot_maintenance_Factor decimal(24,10),
BR_Com_Veh_percentage decimal(24,10),
BR_Load_Limit_CV_in_load_range decimal(24,10),
BR_Time_to_take_Detour_mins decimal(24,10),
BR_Detour_Accident_Cost decimal(24,10),
BR_Flood_Events_FLoodways_5yrs decimal(24,10),
BR_Flood_Safety_multiplier decimal(24,10),
BR_Floodway_Accident_Cost decimal(24,10),
BR_FACTORS_pot_Obstruction_factor decimal(24,10),
BR_Obstruction_Dist_multiplier decimal(24,10),
BR_Upgrade_Area_Multiplier decimal(24,10),
BR_FACTORS_Construction_Cost_m2 decimal(24,10),
BR_FACTORS_Design_etc_Cost decimal(24,10),
BR_Override_Cost decimal(24,10),
BR_FACTORS_Renual_Cost_m2 decimal(24,10)
);
create table #BR_Calcs_Tbl (
CALC_ID_PK int PRIMARY KEY NOT NULL,
BR_JOBS_ID_PK_CALC int,
BR_Traffic_Vol_Score decimal(24,10) null,
BR_Detour_Length_Score decimal(24,10) null,
Priority_Score decimal(24,10) null,
Benifit_Maintenance decimal(24,10) null,
VPD_to_Detour decimal(24,10) null,
Time_factor_sub decimal(24,10) null,
Time_Factor decimal(24,10) null,
Operating_Factor decimal(24,10) null,
Safety_Factor decimal(24,10) null,
Detour_Cost_per_Day decimal(24,10) null,
Benifit_Commercial_Vehical_Detour_Cost decimal(24,10) null,
Benifit_Floodway_Safety_Cost decimal(24,10) null,
Benifit_Obstruction_Safety_Cost decimal(24,10) null,
BENIFIT decimal(24,10) null,
Upgraded_Bridge_Area decimal(24,10) null,
Construction_Cost decimal(24,10) null,
Design_Etc_Cost decimal(24,10) null,
Total_Cost decimal(24,10) null,
COST decimal(24,10) null,
BCR decimal(24,10) null,
Total_Score decimal(24,10) null,
Renual_Amount decimal(24,10) null
);
INSERT INTO #Temp_Source_Data(
BR_JOBS_ID_PK_TEMP,
BR_Traffic_Volume_VPD,
BR_Detour_length_required_km,
BR_Condition_Score,
BR_Closure_Score,
BR_Flood_Safety_Score,
BR_Load_Limit_Score,
BR_Pot_maintenance_Score,
BR_Width_Score,
BR_Existing_Length_m ,
BR_Existing_Width_m,
BR_FACTORS_evaluation_period,
BR_Condition_maintenance_m2,
BR_Pot_maintenance_Factor,
BR_Com_Veh_percentage,
BR_Load_Limit_CV_in_load_range,
BR_Time_to_take_Detour_mins,
BR_Detour_Accident_Cost,
BR_Flood_Events_FLoodways_5yrs,
BR_Flood_Safety_multiplier,
BR_Floodway_Accident_Cost,
BR_FACTORS_pot_Obstruction_factor,
BR_Obstruction_Dist_multiplier,
BR_Upgrade_Area_Multiplier,
BR_FACTORS_Construction_Cost_m2,
BR_FACTORS_Design_etc_Cost,
BR_Override_Cost,
BR_FACTORS_Renual_Cost_m2
)
SELECT
BR_JOBS_ID_PK,
BR_Traffic_Volume_VPD,
BR_Detour_length_required_km,
BR_Condition_Score,
BR_Closure_Score,
BR_Flood_Safety_Score,
BR_Load_Limit_Score,
BR_Pot_maintenance_Score,
BR_Width_Score,
BR_Existing_Length_m,
BR_Existing_Width_m,
BR_FACTORS_evaluation_period,
[BR_Condition_maintenance_m2-yr],
BR_Pot_maintenance_Factor,
BR_Com_Veh_percentage,
[BR_Load_Limit_%CV_in_load_range],
BR_Time_to_take_Detour_mins,
BR_FACTORS_Detour_Accident_Cost,
BR_Flood_Events_FLoodways_5yrs,
BR_Flood_Safety_multiplier,
BR_Factors_Floodway_Accident_Cost,
BR_FACTORS_pot_Obstruction_factor,
BR_Obstruction_Dist_multiplier,
BR_FACTORS_Upgrade_Area_Multiplier,
BR_FACTORS_Construction_Cost_m2,
[BR_FACTORS_Design_etc-Cost],
BR_Override_Cost,
BR_FACTORS_Renual_Cost_m2
FROM
(spatial.CAPITAL_WORKS_JOBS cwj
LEFT JOIN spatial.[Capital Works type] cwt
ON (cwj.Job_Type_FK = cwt.Capital_Works_Type_ID))
RIGHT JOIN (((((((((
spatial.BR_JOBS brj
LEFT JOIN spatial.BR_FACTORS brf
ON brj.BR_factors_link_FK = brf.BR_FACTORS_ID_PK)
LEFT JOIN spatial.BR_Obstruction_Distance brod
ON brj.BR_Obstruction_Dist_FK = brod.BR_Obstruction_Dist_ID_PK)
LEFT JOIN spatial.BR_Condition_Existing brce
ON brj.BR_Existing_Manual_Condition_FK = brce.BR_Condition_ID_PK)
LEFT JOIN spatial.BR_Potential_Maintenance brpm
ON brj.BR_Potential_Maintenance_Cost_FK = brpm.BR_Pot_Maintenance_ID_PK)
LEFT JOIN spatial.BR_Width_of_Existing
ON brj.BR_Width_Existing_FK = spatial.BR_Width_of_Existing.BR_Width_ID_PK)
LEFT JOIN spatial.BR_Load_Limit
ON brj.BR_Load_Limit_FK = spatial.BR_Load_Limit.BR_Load_Limit_ID_PK)
LEFT JOIN spatial.BR_Closure
ON brj.BR_Closure_Duration_FK = BR_Closure.BR_Closure_ID_PK)
LEFT JOIN spatial.BR_Flood_Safety_Risk
ON brj.BR_Flood_Safety_Risk_FK = spatial.BR_Flood_Safety_Risk.BR_Flood_Safety_ID_PK)
LEFT JOIN spatial.BR_Commercial_Vehicals
ON brj.BR_Percentage_Commercial_Vehicals_FK = spatial.BR_Commercial_Vehicals.BR_Com_Veh_ID_PK)
ON cwj.CAPITAL_WORKS_JOBS_ID_PK = brj.BR_JOBS_ID_PK
ORDER BY
BR_JOBS_ID_PK;
declare
-----------------------------------------------------------------
-- Declaration of local variables to store calculated data --
-----------------------------------------------------------------
@BR_Traffic_Vol_Score decimal(24,10),
@BR_Detour_Length_Score decimal(24,10),
@Priority_Score decimal(24,10),
@Benifit_Maintenance decimal(24,10),
@VPD_to_Detour decimal(24,10),
@Time_factor_sub decimal(24,10),
@Time_Factor decimal(24,10),
@Operating_Factor decimal(24,10),
@Safety_Factor decimal(24,10),
@Detour_Cost_per_Day decimal(24,10),
@Benifit_Commercial_Vehical_Detour_Cost decimal(24,10),
@Benifit_Floodway_Safety_Cost decimal(24,10),
@Benifit_Obstruction_Safety_Cost decimal(24,10),
@BENIFIT decimal(24,10),
@Upgraded_Bridge_Area decimal(24,10),
@Construction_Cost decimal(24,10),
@Design_Etc_Cost decimal(24,10),
@Total_Cost decimal(24,10),
@COST decimal(24,10),
@BCR decimal(24,10),
@Total_Score decimal(24,10),
@Renual_Amount decimal(24,10),
@i int
insert into #BR_Calcs_Tbl (
CALC_ID_PK,
BR_JOBS_ID_PK_CALC
)
select
TEMP_ID_PK,
BR_JOBS_ID_PK_TEMP
from
#Temp_Source_Data;
update #BR_Calcs_Tbl set
@BR_Traffic_Vol_Score = BR_Traffic_Vol_Score = case
when BR_Traffic_Volume_VPD < 50 then 0
when BR_Traffic_Volume_VPD < 200 then 5
when BR_Traffic_Volume_VPD < 500 then 10
when BR_Traffic_Volume_VPD < 2000 then 15
else 20
end,
@BR_Detour_Length_Score = BR_Detour_Length_Score = case
when BR_Detour_length_required_km < 1 then 0
when BR_Detour_length_required_km < 5 then 5
when BR_Detour_length_required_km < 10 then 10
else 20
end,
@Priority_Score = Priority_Score = BR_Condition_Score +
BR_Closure_Score +
BR_Flood_Safety_Score +
BR_Load_Limit_Score +
BR_Pot_maintenance_Score +
BR_Width_Score +
BR_Traffic_Vol_Score +
BR_Detour_Length_Score,
@Benifit_Maintenance = Benifit_Maintenance = BR_Existing_Length_m *
BR_Existing_Width_m *
BR_FACTORS_evaluation_period *
BR_Condition_maintenance_m2 *
BR_Pot_maintenance_Factor,
@VPD_to_Detour = VPD_to_Detour = BR_Com_Veh_percentage *
BR_Load_Limit_CV_in_load_range,
@Time_factor_sub = Time_factor_sub = @BR_Traffic_Volume_VPD *
@VPD_to_Detour *
23.0000000000 *
(power((1+0.0300000000),8)),
@Time_Factor = Time_Factor = @Time_factor_sub *
BR_Time_to_take_Detour_mins /
60.0000000000,
@Operating_Factor = Operating_Factor = @VPD_to_Detour *
0.8000000000 *
POWER(1 + 0.0300000000, 8) *
BR_Detour_length_required_km,
@Safety_Factor = Safety_Factor = BR_Detour_Accident_Cost * 0.850000 *
spatial.BR_VPD_to_Detour(BR_Com_Veh_percentage, BR_Load_Limit_CV_in_load_range) *
BR_Traffic_Volume_VPD / 1000000.000000 *
BR_Detour_length_required_km,
@Detour_Cost_per_Day = Detour_Cost_per_Day = @Time_Factor +
@Operating_Factor +
@Safety_Factor,
@Benifit_Commercial_Vehical_Detour_Cost = Benifit_Commercial_Vehical_Detour_Cost = BR_FACTORS_evaluation_period *
365.0000000000 *
@Detour_Cost_per_Day,
@Benifit_Floodway_Safety_Cost = Benifit_Floodway_Safety_Cost = BR_Flood_Events_FLoodways_5yrs *
(BR_FACTORS_evaluation_period / 5.000000) *
BR_Flood_Safety_multiplier *
BR_Traffic_Volume_VPD *
BR_Floodway_Accident_Cost,
@Benifit_Obstruction_Safety_Cost = Benifit_Obstruction_Safety_Cost = BR_FACTORS_pot_Obstruction_factor *
BR_Traffic_Volume_VPD *
BR_FACTORS_evaluation_period *
BR_Floodway_Accident_Cost *
BR_Existing_Length_m *
BR_Obstruction_Dist_multiplier,
@BENIFIT = BENIFIT = @Benifit_Obstruction_Safety_Cost +
@Benifit_Floodway_Safety_Cost +
@Benifit_Commercial_Vehical_Detour_Cost +
@Benifit_Maintenance,
@Upgraded_Bridge_Area = Upgraded_Bridge_Area = BR_Existing_Length_m *
BR_Existing_Width_m *
BR_Upgrade_Area_Multiplier,
@Construction_Cost = Construction_Cost = @Upgraded_Bridge_Area *
BR_FACTORS_Construction_Cost_m2,
@Design_Etc_Cost = Design_Etc_Cost = Construction_Cost *
@BR_FACTORS_Design_etc_Cost,
@Total_Cost = Total_Cost = case
when BR_Override_Cost > 0 then BR_Override_Cost
else @Construction_Cost + @Design_Etc_Cost
end,
@COST = COST = @Total_Cost,
@BCR = BCR = @BENIFIT / @Total_Cost,
@Total_Score = Total_Score = @BCR * @Priority_Score,
@Renual_Amount = Renual_Amount = BR_Existing_Length_m *
BR_Existing_Width_m *
BR_FACTORS_Renual_Cost_m2
from
#BR_Calcs_Tbl brct
inner join #Temp_Source_Data tsd
on (brct.CALC_ID_PK = tsd.TEMP_ID_PK);
select * from #BR_Calcs_Tbl;
November 24, 2008 at 10:27 pm
Paul Nicholson (11/24/2008)
Lynn,I just realised that the server running the application is one that hasn't been upgraded to 2005 and is still running 2000 - I also noticed that there is a SQL 2000 - SQL Server Newbies forum as well. I'm very sorry for the incorrect posting, hopefully your code is not SQL 2005 specific.
I tried creating a CTE which sounds like what I originally wanted to do (before going into the TV UDF), but it doesn't work on SQL 2000.
I didn't write a single CTE. The code should work with SQL Server 2000. If not, hopefully someone else out there can help you, cause as I said, I'm done.
:alien:
November 24, 2008 at 10:30 pm
On a personal note, this forum thread allowed me to break 3000 forum posts!
Edit: And that does NOT count this post!
November 24, 2008 at 10:47 pm
Lynn,
Thank you very much for you time and effort in helping me with this. I did read the link in your email about generating the code to create the table and insert the data, I just got a bit thrown when you asked me to just provide the output of the the main query rather than the underlying tables. I will eneavour to provide the data in the appropriate format if I need assistance again. I will definitely read through the other links in your email as my aim is to improve my SQL skills. You went beyond what I could have hoped for by providing actual code and I truly appreciate it, I'm very sorry if I made the task more difficult or onerous.
Thank you
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply