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

Question

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

dj1202

SSCommitted

Points: 1829
More actions
August 10, 2012 at 12:43 pm

#260677

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.

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

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

Possinator Hall of Fame Points: 3422 More actions · Answer 1

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]

sestell1 SSChampion Points: 10230 More actions · Answer 2

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'

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

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