Update sql temp table column with computational expression

user_7418767 0
Viewed 94

I perform the SQL code below. Although computational expression is not Zero, all rows update with zero, as an additional information denominator is bigger in every cases.

UPDATE #t
SET ProportionOfvehicle = (CountOfVehicle / @TotalNumberOfPrivateVehicles)*100
WHERE LegalPersonID IS NOT NULL;
sql-server sql
1 Answers

I could finally solved the problem by my self. I created a temp table which the data type for its computational fields are float like below

**CREATE TABLE #t
(
    CountOfVehicle BIGINT,
    LegalPersonID BIGINT,
    RegionID BIGINT,
    ProportionOfvehicle FLOAT,
    ProportionOfAccident FLOAT,
    ProportionOfPersonel FLOAT
)**

then insert into it for other fields except computational fields with code below

**INSERT  INTO #t
(
    CountOfVehicle,
    LegalPersonID,
    RegionID,
    ProportionOfvehicle,
    ProportionOfAccident,
    ProportionOfPersonel
)
SELECT COUNT(*) AS CountOfVehicle,
       LegalPersonID,
       RegionID,
       NULL AS ProportionOfvehicle,
       NULL AS ProportionOfAccident,
       NULL AS ProportionOfPersonel
FROM Bus.VehicleOwnerships
WHERE OwnershipTypeValue IN (1,2)
GROUP BY LegalPersonID,
         RegionID**

and finally update the computational fields with the statement below

**UPDATE #t
SET ProportionOfvehicle = CountOfVehicle / @TotalNumberOfOrganizationVehicles
WHERE RegionID IS NOT NUL**
user_74187670
Related