Sometime the values from the source database turn into NULL values. There are two (or more) ways how to deal with this:
A. You can convert the value to zero before it is inserted in the Anchor Model
B. You can insert NULL values in the Anchor Model
C. Other?
Option 'B' is not possible.
Option 'A' looks correct, but sometimes NULL values serve a specific meaning in the database.
The AVG function is an example of that (see the example below).
What is the best way in order to deal with numeric values (or other values) that are deleted or turn into NULL. Without converting the complete Anchor Model database to BI-temporal and use the ErasedAt attribute for evey historic attribute.
Thanks!
----------------------------------------------------------------------------
-- Example:
----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Budget]') AND type in (N'U'))
DROP TABLE [dbo].[Budget]
CREATE TABLE [dbo].[Budget](
[ID] [int] NOT NULL,
[Budget] [decimal](18, 2) NULL,
[ChangedAt] [datetime] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Budget]([ID],[Budget],[ChangedAt])
VALUES (1,100.0,'2010-01-01')
,(2,120.0,'2011-01-01')
,(3,0.0,'2012-01-01')
SELECT AVG([Budget]) FROM [dbo].[Budget]
-- AVG = 73.333333!
DELETE FROM [dbo].[Budget]
INSERT INTO [dbo].[Budget]([ID],[Budget],[ChangedAt])
VALUES (1,100.0,'2010-01-01')
,(2,120.0,'2011-01-01')
,(3,NULL,'2012-01-01')
SELECT AVG([Budget]) FROM [dbo].[Budget]
-- AVG = Value = 110!
-- Conclusion: NULL or zero give different values!
