CODEWITHSUNDEEP
sqlsql-serverstored-procedurescalculated-columns
kkris77
kkris77

Reputation: 117

Computed column based on other computed column within insert procedure

I am creating a simple database with data that will allow me (using excel) to analyse my consumption of cold water, warm water, eletricity etc. and charges for those.

I am going to use useforms in excel to insert the data to database so I've created a SQL procedure. Below it's an example of inserting data for cold water.

I am passing to the procedure two important parameters: @Price and @Reading. In the table [Cold water] I've got also columns: Consumption and Cost.

What I want to do, is to calculate first the consumption for current month (as a actual @Reading - previous reading). Then, having computed current consumption I want to use this value and calculate the cost (current consumption * @Price).

I've managed to calculate Current consumption and the cost using subquery. But I am curious whether (and how if possible) I can directly use a computed value that has not been inserted yet (current consumption), to calculate another one (the cost) within one insert instead of using subquery.

The only idea I've thought about is writing a trigger executed after this insert. Also I don't know how to insert the very first row to the table with this procedure instead of inserting the first row manually.

I'd appreciate any tips.

@Date       AS DATE,
@Reading        AS DECIMAL (6,3),
@Price      AS DECIMAL (6,2),
@Unit   AS VARCHAR (5)

AS
    BEGIN
        SET NOCOUNT ON


-- Cold water
IF NOT EXISTS
        (
        SELECT Date
        FROM [Cold water]
        WHERE Date = @Date
        )
         BEGIN
            INSERT INTO dbo.[Cold water] 
            (
                Date,
                Year,
                Month,
                Day,
                Reading,
                Consumption,
                Unit,
                Price,
                Cost
            )
            VALUES
            (
                @Date,
                (SELECT YEAR(@Date)),
                (SELECT MONTH(@Date)),
                (SELECT DAY(@Date)),
                @Reading,
                (SELECT 
                    (@Reading - (SELECT Reading FROM [Cold water] WHERE ID = (SELECT MAX(ID) FROM [Cold water]))
                    )
                ),
                @Unit,
                @Price,
                (SELECT @Price* (SELECT (@Reading - (SELECT Reading FROM [Cold water] WHERE ID = (SELECT MAX(ID) FROM  [Cold water])
                                                   )
                                        )
                                )
                )
            )
        END

Upvotes: 1

Views: 284

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You can do what you want using insert . . . select:

INSERT INTO dbo.[Cold water](Date, Year, Month, Day, Reading,
                             Consumption, Unit, Price, Cost)
    SELECT @Date, YEAR(@Date), MONTH(@Date), DAY(@Date), @Reading,
           c.Consumption, @Unit, @Price, @Price * c.Consumption
    FROM (SELECT (@Reading - Reading)  as Consumption
          FROM [Cold water]
          WHERE ID = (SELECT MAX(ID) FROM [Cold water])
         ) c;

Some comments:

  • You overuse nested selects. These are not needed just to apply a function.
  • You do not need to store date components if you are storing the date. That is why the database contains functions like year() and month().
  • The calculation of cost is probably better done as a computed column.

EDIT:

You can add computed columns as:

alter table dbo.[Cold water] add year as (year(date));

or:

alter table dbo.[Cold water] add cost as (unit * price);

To Excel, these will appear as bona fide columns. I would recommend using this logic for all the columns that can be computed.

Upvotes: 1

Related Questions