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!

I suggest you read this (regarding "indexed views" in SQL server) http://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server but I think you need Enterprise Edition of SQL Server to use that feature. Otherwise you need trigger(s) &/or stored procedure(s) to add rows into another table when you add/alter data in that existing table. It cannot be undertaken as a "computed column" — Paul Maxwell, Nov 20 '15 at 13:37

Paul Maxwell · Answer 1 · 2015-11-20T13:25:26.650

1

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

edited Nov 20 '15 at 13:25

answered Nov 20 '15 at 11:07

Paul Maxwell

33,002
3
32
51

I want it inserted into my Months table. – Bungicasse Nov 20 '15 at 11:16
Did you visit the sqlfiddle? if it isn't correct please alter the question so I can offer something else. But as the question stands what I have offered does exactly what you described as the need. – Paul Maxwell Nov 20 '15 at 11:18
just add "into my_months_table" or similar. You still need "the query" to produce the wanted rows. – Paul Maxwell Nov 20 '15 at 11:20
I don't really want to paste this every time I get a new value, look how I calculate remaining_days for instance, I want it automatic if possible. – Bungicasse Nov 20 '15 at 11:54
To achieve the automation you are seeking you need a trigger to get a new row (or changed row) in the existing table to produce between 1 and 12 rows in a different table. But the logic outlined in that query remains valid. I'm not offering to write your triggers for you. – Paul Maxwell Nov 20 '15 at 13:14
just so it's clear, you cannot use a "computed column" to manage rows in a different table you needs triggers or perhaps a (job running stored proc). Maybe a "materialized view" would do it for you if you have enterprise edition. nb: The query does not depend on using any CTEs (a derived table would work just as well). – Paul Maxwell Nov 20 '15 at 13:20
no, I won't attempt to re-write your question. sorry. – Paul Maxwell Nov 20 '15 at 13:26

score 0 · Answer 2 · answered Nov 22 '15 at 11:54

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

Bungicasse · Accepted Answer · 2015-11-27T08:23:49.983

@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

score 0 · Answer 4 · answered Dec 16 '15 at 05:32

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

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

4 Answers4