Create Table And Get Data From Another Table

I have a table Cost category: CREATE TABLE [dbo].[CostCategory]( [ID_CostCategory] [int] NOT NULL, [Name] [varchar](150) NOT NULL, [Plan] [money] NOT NULL, [Realization] [money] NU

Solution 1:

Try this

--create table without realization column
CREATE TABLE [dbo].[CostCategory](
[ID_CostCategory] [int] NOT NULL,
[Name] [varchar](150) NOT NULL,
[Plan] [money] NOT NULL
) go

CREATE TABLE [dbo].[Cost](
[ID_Cost] [int] NOT NULL,
[Name] [varchar](50) NULL,
[ID_CostCategory] [int] NULL,
[ID_Department] [int] NULL,
[ID_Project] [int] NULL,
[Value] [money] NULL,

) go 

Create a UDF to calculate sum of the cost column:

CREATE FUNCTION [dbo].[CalculateRealization](@Id INT) 
RETURNS money
AS 
BEGIN
  DECLARE @cost money

  SELECT @cost = SUM(Value)
  FROM [dbo].[Cost]
  WHERE [ID_CostCategory] = @ID

  return @cost
END

Now Alter your CostCategory table to add computed column:

ALTER TABLE [dbo].[CostCategory]
   ADD [Realization] AS dbo.CalculateRealization(ID_CostCategory);

Now you can select Realization from Costcategory

SELECT ID_CostCategory, Realization
FROM [dbo].[CostCategory]

Answer to your comment below:

Create Another UDF

CREATE FUNCTION [dbo].[CheckValue](@Id INT, @value Money) 
RETURNS INT
AS 
BEGIN
  DECLARE @flg INT
  SELECT @flg = CASE WHEN [Plan] >= @value THEN 1 ELSE 0 END
  FROM [dbo].[CostCategory]
  WHERE [ID_CostCategory] = @ID

  return @flg;
END

Now add Constraint on Cost Table:

ALTER TABLE ALTER TABLE [dbo].[Cost]
  ADD CONSTRAINT CHK_VAL_PLAN_COSTCATG
    CHECK(dbo.CheckValue(ID_CostCategory, Value) = 1)

Solution 2:

You do not need to have a Realization column as part of the CostCategory table. Rather, you will want to use a join.

Select A.ID_CostCategory, A.Name, SUM(B.Value) As Realization from CostCategory A
JOIN Cost B ON A.ID_CostCategory = B.ID_CostCategory
Group By A.ID_CostCategory, A.Name