sql server computed columns

Hi Peter,
Can you please few rows from your table and the expected output?

In SQL Server when creating a table (or adding a column to a table) you can define it as computed column:

CREATE TABLE TableName  
(  
    ColumnID INT,
    Column1 INT, 
    Column2 INT, 
    ComputedCol AS ABS(ISNULL(Column1,0)) - ABS(ISNULL(Column2,0)
)

Select allOpen in new window

This will make ComputedCol a computed column and you won't need to provide a value for it. The value will be calculated based in Column1 and Column2 values. Example:

INSERT INTO TableName (ColumnID, Column1, Column2)
VALUES (1, 20, -30)

Select allOpen in new window

In my results table I created a column already a numeric one to save the calculation to avoid delay when viewing. The query that finds where values change produces output like this :
descriptive ie camshaft changed here
GLASSid_GLASScat      BATCH      PREV      CHANGE      Resultdiff      GLASS_cat
223695C      201611      SOHC      DOHC      False      C
223699C      201611      SOHC      DOHC      False      C
226265C      201611      SOHC      DOHC      False      C
230826C      201611      SOHC      DOHC      False      C
230827C      201611      SOHC      DOHC      False      C
234385B      201612      SOHC      DOHC      False      B


Numeric here the horse power changed
GLASSid_GLASScat      BATCH      PREV      CHANGE      Resultdiff      GLASS_cat
219584B      201607      3      2      False      B
219591B      201607      6      8      False      B
160451C      201611      268      242      False      C
219571B      201607      10      9      False      B
236230B      201611      21      14      False      B
209050B      201607      0      9      False      B

The query you and pawan assisted with finds these events and it does so beautifully

I need to store these events into tblcompareevents.

because there is not enough manpower to look at every change event I need to find the variance and store it but of course it will fall over if i try to work out variance on SOHC VS DOHC but would work fine on 268       vs 242 a difference of 26.

Vehicle data has many events so looking at calculated fields can be slow hence the desire to work out the variance and store it in a variance field.

I was hoping sql server had a way I could determine the field type then do the appropriate insert one with variance calculated in a calculated field and one not.

I bow to your wisdom as you will know best how I should tackle this now you saw the real data

So, you would only need something like?

ALTER TABLE tblcompareevents
   ADD COLUMN variance AS (ABS(ISNULL(BATCH,0)) - ABS(ISNULL(PREV,0))

Select allOpen in new window

is isnumeric robust enough, i am sure I read somewhere that it has instances where its not reliable?

Please try this - 100% robust. Isnumeric does not work in few cases.

--
ALTER TABLE camshaft 
	ADD NewColumn AS 
        ABS(CASE WHEN TRY_PARSE(CHANGE AS INT) IS NOT NULL AND TRY_PARSE(PREV AS INT) IS NOT NULL 
			  THEN CAST ( PREV AS INT)  - CAST (CHANGE AS INT) 
 			  ELSE 0 END)
--

Select allOpen in new window

Peter, why would you have a column to hold different data types? Wouldn't be more easy to have separate columns for that? Or use only the ID and the Description you can get from a generic table?

The clients send their datasets in with datatypes all over the place. I can get to all the data changes changes now between each month in the format that you saw with PREV and CHANGE taking the values and storing as string. This allows me to grab every change from every field from all clients in one query.

Hi Peter,
Have you tried my last comment.?

thank you