Indexed views: How to insert value into other table with index views?

Question

My table:

CREATE TABLE [dbo].[Balance] (
    [Id]             INT             IDENTITY (1, 1) NOT NULL,    
    [Balance]        DECIMAL (18, 2) NOT NULL,
    [Today_Date]     AS              (CONVERT([char](10),getdate(),(126))),
    [Date_end]       DATE            NOT NULL,
    [Remaining_Days] AS              (datediff(day,CONVERT([char](10),getdate(),(126)),[Date_end])),
    [In_Months]      AS              (datediff(day,CONVERT([char](10),getdate(),(126)),[Date_end]))/(30),
    [Amount_Monthly] AS              CAST((case when ((datediff(day,CONVERT([char](10),getdate(),(126)),[Date_end]))/30) = 0 then NULL else [Balance]/((datediff(day,CONVERT([char](10),getdate(),(126)),[Date_end]))/30) end) as DECIMAL(18,2)),
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

How it looks like:

I want it to automatically insert the Amount_Monthly into a new table so it would look like this:

E.g. if it says In_Months = 2 it should fill out January and February's Balance_monthly to 7058,82. I want it to calculate it automatically Just like I made it automatically calculate remaining_days depending on the input.

Thank you!

Answer 1

@Mahesh So, @Usedbyalready's answer seemed pretty overkill, I tried making it myself with case inside an update and it works perfectly.

UPDATE Months
SET Months.Balance_monthly = 
    CASE 
        WHEN Balance.In_Months > 1 THEN Amount_Monthly          
    END
FROM Balance
JOIN Months 
ON Months.Id <= Balance.In_Months;

I also made a trigger that automatically inserts the values into my Months table:

CREATE TRIGGER [Balance_monthly]
ON [dbo].[Balance]
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON

    UPDATE Months
    SET Months.Balance_monthly = 
    ((Balance.In_Months + 12 - Months.Id) / 12) * Amount_Monthly          
    FROM Balance
    CROSS JOIN Months;


END

Answer 2

You need 12 rows each represents a Month number 1 to 12. I have used a simple union all query in a CTE for this but you may already have a table of numbers to use instead. Then join this where the month number is less than or equal to the [in_Month] column. That join will automatically now multiply the rows of your table by the wanted number of months.

;with m12 as (
  select 1 as mn
  union all      select 2      union all      select 3      union all      select 4
  union all      select 5      union all      select 6      union all      select 7
  union all      select 8      union all      select 9      union all      select 10
  union all      select 11     union all      select 12
 )
select
        row_number() over(order by b.id, m12.mn) as [ID]
      , datename(month,dateadd(month,m12.mn - 1,0)) as [Month]
      , b.Amount_Monthly as Balance_Monthly
from Balance b
inner join m12 on m12.mn <= b.in_months

see: http://sqlfiddle.com/#!6/4fc6f/3

Note you will probably want to include db.balance.id as [balanceid] or similar in the new table so you can trace back to the source row id.

If CTEs are a problem just use a "derived table" instead, e.g.

select
        row_number() over(order by b.id, m12.mn) as [ID]
      , datename(month,dateadd(month,m12.mn - 1,0)) as [Month]
      , b.Amount_Monthly as Balance_Monthly
from Balance b
inner join (
              select 1 as mn
              union all      select 2      union all      select 3      union all      select 4
              union all      select 5      union all      select 6      union all      select 7
              union all      select 8      union all      select 9      union all      select 10
              union all      select 11     union all      select 12
           ) as m12 on m12.mn <= b.in_months

Answer 3

Today's Date New End date Remaining days Daily 2015-11 2015-12 2016-01 2016-02 2016-03 2016-04 2016-05 2016-06 2016-07 2016-08 2016-09 2016-10 2016-11 2016-12 11/30/2015 12/31/2015 1/31/2016 2/28/2016 3/31/2016 4/30/2016 5/31/2016 6/30/2016 7/31/2016 8/31/2016 9/30/2016 10/31/2016 11/30/2016 12/31/2016 10/29/2015 1/4/2016 67 $210.71 $6,321.33 $6,532.04 $842.84 $- $- $- $- $- $- $- $- $- $- $-
10/29/2015 1/8/2016 71 $283.24 $8,497.16 $8,780.40 $2,265.91 $- $- $- $- $- $- $- $- $- $- $-

it should be like this

Answer 4

Here amount is not splitting correctly, for example if remaining days in Balance if today date is 2015-12-16 and enddate is 2016-01-31 then remaining days could be 46 days, Here Amount need to split into December Month i.e current Month and January Month, can anyone let me know how to achieve it