March 25, 2011 at 10:55 am
(note: I accidentally posted into SQL2K so reposting here)
Hello all, I am trying to quickly store a Previous "days" value in the Current "days" record. So the goal is for the table to store Account, Day and Market value with a Running Total and what Yesterdays Market value was.
Here is the code to produce the results and though the running total works I can't figure out why the update of Yesterdays Market value doesn't work, it's just repeating todays. Hopefully something simple I'm doing wrong.
Thanks in advance for the help.
declare @DayID_in int=-1,
@AccountID_in int=-1,
@YesterdayMV float=0.0,
@RunningTotalMV float=0.0,
@NewGroup_bt bit=0
create table #AccountClassInputs(
AccountID_in int NOT NULL,
DayID_in int not null,
MarketValue_fl float NOT NULL,
YesterdayMarketValue_fl float not null,
RunningTotalMV float not null
)
CREATE UNIQUE CLUSTERED INDEX [PK_AccountClassInputs] ON #AccountClassInputs
(
[AccountID_in] ASC,
[DayID_in] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
insert into #AccountClassInputs
values (1,1,10000,0,0)
insert into #AccountClassInputs
values (1,2,11000,0,0)
insert into #AccountClassInputs
values (1,3,12000,0,0)
insert into #AccountClassInputs
values (2,1,11000,0,0)
insert into #AccountClassInputs
values (2,2,12000,0,0)
insert into #AccountClassInputs
values (2,3,13000,0,0)
insert into #AccountClassInputs
values (2,4,14000,0,0)
insert into #AccountClassInputs
values (2,5,15000,0,0)
update #AccountClassInputs
set
@DayID_in=DayID_in, -- anchor column needed to insure consistency
@NewGroup_bt=CASE WHEN @AccountID_in<>AccountID_in THEN 1 ELSE 0 END,
@RunningTotalMV=CASE WHEN @NewGroup_bt=1 THEN 0 ELSE @YesterdayMV + MarketValue_fl END,
YesterdayMarketValue_fl=@YesterdayMV,
@YesterDayMV=CASE WHEN @NewGroup_bt=1 THEN 0 ELSE MarketValue_fl END,
RunningTotalMV=@RunningTotalMV,
@AccountID_in=AccountID_in
FROM #AccountClassInputs OPTION (MAXDOP 1)
select * from #AccountClassInputs
drop table #AccountClassInputs
March 25, 2011 at 11:39 am
No need to repost here, now that we know it's 2008 in the other thread.
Please post responses here.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply