Calculated/Derived Column on existing table...please help

  • Hey guys, I have been going nuts over this issue for some time and I am seeking help.

    I have SQL Server table with values, as follows:

    Account - Date - Amount - Summary

    10000 - 2010-1-1 - 50.00 - 0.00

    10000 - 2010-2-1 - 50.00 - 0.00

    10000 - 2010-3-1 - 50.00 - 0.00

    10000 - 2010-4-1 - 50.00 - 0.00

    10000 - 2010-5-1 - 50.00 - 0.00

    10000 - 2010-6-1 - 50.00 - 0.00

    10000 - 2010-7-1 - 50.00 - 0.00

    10000 - 2010-8-1 - 50.00 - 0.00

    10000 - 2010-9-1 - 50.00 - 0.00

    10000 - 2010-10-1 - 50.00 - 0.00

    10000 - 2010-11-1 - 50.00 - 0.00

    10000 - 2010-12-1 - 50.00 - 600.00

    10000 - 2011-1-1 - 25.00 - 0.00

    10000 - 2011-2-1 - 25.00 - 0.00

    10000 - 2011-3-1 - 50.00 - 0.00

    10000 - 2011-4-1 - 50.00 - 0.00

    10000 - 2011-5-1 - 50.00 - 0.00

    10000 - 2011-12-1 - 25.00 - 825.00

    10000 - 2012-1-1 - 100.00 - 0.00

    10000 - 2012-2-1 - 200.00 - 0.00

    10000 - 2012-3-1 - 100.00 - 0.00

    10000 - 2012-5-1 - 100.00 - 0.00

    10000 - 2012-6-1 - 100.00 - 0.00

    10000 - 2012-8-1 - 100.00 - 0.00

    10000 - 2012-12-1 - 100.00 - 1625.00

    10001 - 2010-1-1 - 50.00 - 0.00

    10001 - 2010-2-1 - 60.00 - 0.00

    10001 - 2010-12-1 - 60.00 - 170.00

    10001 - 2011-1-1 - 50.00 - 0.00

    10001 - 2011-2-1 - 50.00 - 0.00

    10001 - 2011-3-1 - 50.00 - 0.00

    10001 - 2011-4-1 - 50.00 - 0.00

    10001 - 2011-6-1 - 50.00 - 0.00

    10001 - 2011-8-1 - 50.00 - 0.00

    10001 - 2011-10-1 - 50.00 - 0.00

    10001 - 2011-12-1 - 50.00 - 570.00

    This is a basic snapshot of the table. The "Summary" column gives the total for the "Amounts" at the end of the year (based on "date" column), but only when the MONTH(Date) = '12'. It goes on this way for hundreds of accounts, with about 4 more years as well. I would like to add a column to this existing table, called "SummaryPreviousYear". The SummaryPreviousYear column should have the sum of the amounts from MONTH(Date) = '12' and the previous year. I'd like to join this column on the account number, so that it sits next to the Summary column and gives a value just like the Summary value does, but the SummaryPreviousYear value would need to be present the whole way down the column, not just where the month is 12. For example, the following row:

    Before:

    Account - Date - Amount - Summary

    10001 - 2011-10-1 - 50.00 - 0.00

    10001 - 2011-12-1 - 50.00 - 570.00

    After:

    Account - Date - Amount - Summary - SummaryPreviousYear

    10001 - 2011-10-1 - 50.00 - 0.00 - 170.00

    10001 - 2011-12-1 - 50.00 - 570.00 - 170.00

    Can anyone help me with this? I am pulling my hair out here for 2 days and need to get this dataset created so I can proceed with my report development. Unfortunately, the DBA's off site. Literally at my wit's end. Any help would be greatly appreciated.

  • dj1202 (8/10/2012)


    Hey guys, I have been going nuts over this issue for some time and I am seeking help.

    I have SQL Server table with values, as follows:

    Account - Date - Amount - Summary

    10000 - 2010-1-1 - 50.00 - 0.00

    10000 - 2010-2-1 - 50.00 - 0.00

    10000 - 2010-3-1 - 50.00 - 0.00

    10000 - 2010-4-1 - 50.00 - 0.00

    10000 - 2010-5-1 - 50.00 - 0.00

    10000 - 2010-6-1 - 50.00 - 0.00

    10000 - 2010-7-1 - 50.00 - 0.00

    10000 - 2010-8-1 - 50.00 - 0.00

    10000 - 2010-9-1 - 50.00 - 0.00

    10000 - 2010-10-1 - 50.00 - 0.00

    10000 - 2010-11-1 - 50.00 - 0.00

    10000 - 2010-12-1 - 50.00 - 600.00

    10000 - 2011-1-1 - 25.00 - 0.00

    10000 - 2011-2-1 - 25.00 - 0.00

    10000 - 2011-3-1 - 50.00 - 0.00

    10000 - 2011-4-1 - 50.00 - 0.00

    10000 - 2011-5-1 - 50.00 - 0.00

    10000 - 2011-12-1 - 25.00 - 825.00

    10000 - 2012-1-1 - 100.00 - 0.00

    10000 - 2012-2-1 - 200.00 - 0.00

    10000 - 2012-3-1 - 100.00 - 0.00

    10000 - 2012-5-1 - 100.00 - 0.00

    10000 - 2012-6-1 - 100.00 - 0.00

    10000 - 2012-8-1 - 100.00 - 0.00

    10000 - 2012-12-1 - 100.00 - 1625.00

    10001 - 2010-1-1 - 50.00 - 0.00

    10001 - 2010-2-1 - 60.00 - 0.00

    10001 - 2010-12-1 - 60.00 - 170.00

    10001 - 2011-1-1 - 50.00 - 0.00

    10001 - 2011-2-1 - 50.00 - 0.00

    10001 - 2011-3-1 - 50.00 - 0.00

    10001 - 2011-4-1 - 50.00 - 0.00

    10001 - 2011-6-1 - 50.00 - 0.00

    10001 - 2011-8-1 - 50.00 - 0.00

    10001 - 2011-10-1 - 50.00 - 0.00

    10001 - 2011-12-1 - 50.00 - 570.00

    This is a basic snapshot of the table. The "Summary" column gives the total for the "Amounts" at the end of the year (based on "date" column), but only when the MONTH(Date) = '12'. It goes on this way for hundreds of accounts, with about 4 more years as well. I would like to add a column to this existing table, called "SummaryPreviousYear". The SummaryPreviousYear column should have the sum of the amounts from MONTH(Date) = '12' and the previous year. I'd like to join this column on the account number, so that it sits next to the Summary column and gives a value just like the Summary value does, but the SummaryPreviousYear value would need to be present the whole way down the column, not just where the month is 12. For example, the following row:

    Before:

    Account - Date - Amount - Summary

    10001 - 2011-10-1 - 50.00 - 0.00

    10001 - 2011-12-1 - 50.00 - 570.00

    After:

    Account - Date - Amount - Summary - SummaryPreviousYear

    10001 - 2011-10-1 - 50.00 - 0.00 - 170.00

    10001 - 2011-12-1 - 50.00 - 570.00 - 170.00

    Can anyone help me with this? I am pulling my hair out here for 2 days and need to get this dataset created so I can proceed with my report development. Unfortunately, the DBA's off site. Literally at my wit's end. Any help would be greatly appreciated.

    Probably everyone around here will suggest that you should NOT store calculated values. Calc's should be done in the presentation, or just prior to presentation in your query. That being said... here is your test data:

    create table #Accounts (

    Account varchar(10) not null,

    [Date] date not null,

    Amount decimal(28,2) not null,

    Summary decimal(28,2) null

    )

    insert into #Accounts (Account, [Date] , Amount , Summary )

    values ('10000','2010-02-01',50,0),

    ('10000','2010-03-01',50,0),

    ('10000','2010-04-01',50,0),

    ('10000','2010-05-01',50,0),

    ('10000','2010-06-01',50,0),

    ('10000','2010-07-01',50,0),

    ('10000','2010-08-01',50,0),

    ('10000','2010-09-01',50,0),

    ('10000','2010-10-01',50,0),

    ('10000','2010-11-01',50,0),

    ('10000','2010-12-01',50,600),

    ('10000','2011-01-01',25,0),

    ('10000','2011-02-01',25,0),

    ('10000','2011-03-01',50,0),

    ('10000','2011-04-01',50,0),

    ('10000','2011-05-01',50,0),

    ('10000','2011-12-01',25,825),

    ('10000','2012-01-01',100,0),

    ('10000','2012-02-01',200,0),

    ('10000','2012-03-01',100,0),

    ('10000','2012-05-01',100,0),

    ('10000','2012-06-01',100,0),

    ('10000','2012-08-01',100,0),

    ('10000','2012-12-01',100,1625),

    ('10001','2010-01-01',50,0),

    ('10001','2010-02-01',60,0),

    ('10001','2010-12-01',60,170),

    ('10001','2011-01-01',50,0),

    ('10001','2011-02-01',50,0),

    ('10001','2011-03-01',50,0),

    ('10001','2011-04-01',50,0),

    ('10001','2011-06-01',50,0),

    ('10001','2011-08-01',50,0),

    ('10001','2011-10-01',50,0),

    ('10001','2011-12-01',50,570)

    There are many ways to extract what you are looking for. Here is one, albeit a fairly brutal one, but easy to follow:

    select SummaryYear=year(a.[date])

    ,a.Account

    ,a.[Date]

    ,a.Amount

    ,AnnualAmount=case when month(a.[date]) = 12 then at.AnnualTotal else 0 end

    ,RunningAnnualAmount=case when month(a.[date]) = 12 then rt.RunningTotal else 0 end

    from #Accounts a

    left outer join ( select sum(amount) as AnnualTotal, year([date]) as ATYear, Account as ATAccount

    from #Accounts

    group by year([date]), Account

    ) as at on at.ATYear = year(a.[Date])

    and at.ATAccount = a.Account

    left outer join ( select sum(amount) as RunningTotal, Account as RTAccount

    from #Accounts

    group by Account

    ) as rt on rt.RTAccount = a.Account

    drop table #Accounts

    Best of luck..

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • I'm not sure from your question if you want the summary value from the month 12 record of the previous year, or if you are trying to sum up multiple records from the previous year.

    Here is sample code to add the month 12 summary value from the previous year onto each record of the following year:

    CREATE TABLE TEMP1

    (

    Account Int,

    Month_Year DateTime,

    Amount Decimal(8,2),

    Summary Decimal(10,2)

    )

    INSERT INTO TEMP1 VALUES (10000, '2012-01-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-02-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-03-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-04-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-05-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-06-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-07-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-08-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-12-01', 50, 450)

    INSERT INTO TEMP1 VALUES (10000, '2011-01-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2011-02-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2011-03-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2011-04-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2011-05-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2011-06-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2011-12-01', 50, 350)

    INSERT INTO TEMP1 VALUES (10000, '2010-01-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-02-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-03-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-04-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-05-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-06-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-07-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-08-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-09-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2010-10-01', 50, 0)

    INSERT INTO TEMP1 VALUES (10000, '2012-12-01', 50, 550)

    SELECT

    Record.*,

    Previous_Year.Summary AS SummaryPreviousYear

    FROM

    TEMP1 Record

    Join

    TEMP1 Previous_Year

    ON

    Previous_Year.Account = Record.Account

    AND Previous_Year.Month_Year = LTRIM(STR(Year(Record.Month_Year))) + '-12-01'

  • Here's another approach:

    ;WITH Summary AS (

    SELECT Account, Year=DATEPART(year, [Date]), Summary=SUM(Summary)

    FROM #Accounts

    GROUP BY Account, DATEPART(year, [Date]))

    SELECT a.Account, [Date], Amount, a.Summary

    ,PreviousYearSummary=b.Summary

    FROM #Accounts a

    LEFT JOIN Summary b ON a.Account = b.Account AND b.Year = DATEPART(year, a.[Date]) - 1

    If you don't like NULL appearing in the PreviousYearSummary column where there is no previous year available, change the computed value to wrap b.Summary with ISNULL:

    PreviousYearSummary=ISNULL(b.Summary, 0)

    I'd be mighty curious which runs fastest on your 500K record set.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply