Calculation current row + Previous row value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculation current row + Previous row value

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 06:54:07

H, I need help please

I need to calculate the current row field value with the previous row field value. Field Split row2 + Field Accum row1
and Split row3 + Accum row2 eg:

Split Accum
row1 0 0
row2 0.0476 0.0476
row3 0.0476 0.0952

Regards

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-30 : 07:13:11

select a.row, a.split, sum(b.split) as accum
from yourtable a cross join yourtable b
where b.row <= a.row
group by a.row, a.split

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-30 : 07:23:29

[code]select a.*,added=a.Accum+b.Accum
from yourtable a left join yourtable b on convert(int,replace(a.Split ,'row',''))-1=convert(int,replace(b.Split ,'row',''))[/code]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-30 : 08:15:48

Hi Try This

select a.split,a.accum,b.total from urtable a
cross apply (select sum(accum) as total from urtable where rowno = a.rowno or rowno = a.rowno - 1) b

Here rowno is an identity column in urtable

Jai Krishna

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 08:58:22

Thank You all for the help.
I tried all 3 methods but not winning.

I don't have a simple table it is build via multiple selects

SELECT Split_Date,Split_Day ,Split_Day_Accum
FROM
(select Split_Date
,case when Date_Day = 'Work Day' then Split_Day else 0 end as Split_Day
,0 as Split_Day_Accum
from
(select CONVERT(VARCHAR(10), Temp2.dates, 103) + ' 00:00:00' AS Split_Date
,Period
,case when Temp2.dates = V_Hol2.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,NumDaysInMonth
,Work_Days
,round(sum(1.0E / Work_Days),4) AS Split_Day

from
(Select Period
,NumDaysInMonth
,count(Date_Day) as Work_Days
from
(Select substring(CONVERT(VARCHAR(10), dates, 103),7,4) + '.' + substring(CONVERT(VARCHAR(10), dates, 103),4,2) as Period
,case when dates = V_Hol.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,dates
,dbo.udf_GetNumDaysInMonth(dates) as NumDaysInMonth
from @TempTable

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol
on V_Hol.holiday = dates

) v1
where Date_Day = 'Work Day'
group by Period,NumDaysInMonth

) v2

left join @TempTable as Temp2
on substring(Period,6,7) = substring(CONVERT(VARCHAR(10), Temp2.dates, 103),4,2)

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol2
on V_Hol2.holiday = Temp2.dates

group by Temp2.dates
,Period
,V_Hol2.holiday
,NumDaysInMonth
,Work_Days

) v3
) v4

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 09:22:55

I don't have a field row, i was only trying to explain and show the rows results

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-30 : 10:12:25

From what I understand, your query currently returns 3 columns.
First column is a date
Second column is the split value.
Third column should return a running total, but currently returns 0.

If that is the case, modify your current query as follows:

with SplitData as
(
PUT YOUR CURRENT QUERY AS IT EXISTS NOW HERE
)
select a.Split_Date, a.Split_Day, sum(b.Split_Day)
from SplitData a cross join SplitData b
where b.Date <= a.Date
group by a.SplitDate, a.Split_Day

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 10:37:30

Thanks for the help.
Really struggling - learning new & interesting methods though.

I does not return the correct results, it only returns the 12 days of a month as well as the accum total does not add up to 1.

eg:
Date Split Accum
01/01/2009 4 4
02/01/2009 4 8

Result I want is where the second row Accum value = (row2 Split + row1 Accum)
& it should continue for all the the rows within the result.

Please Assist

select a.Split_Date, sum(a.Split_Day+b.Split_Day)
from SplitData a cross join SplitData b
where b.Split_Date <= a.Split_Date
group by a.Split_Date

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-30 : 10:57:07

The query you included in your last reply does not seem right - it will not give you the right values. Unless I misunderstood something about your data, the query I listed in my previous reply should give you the right results. If it does not, experiment with the script given below. It creates a test table, puts some data into it, and then queries for the accumulated total.

-- create a test table.
create table TestTable (date datetime, split float);
go

-- insert some data into the table.
insert into TestTable (date, split) values ('2008-01-01',10);
insert into TestTable (date, split) values ('2008-01-02',3);
insert into TestTable (date, split) values ('2008-01-03',7);
insert into TestTable (date, split) values ('2008-01-04',-5);
insert into TestTable (date, split) values ('2008-01-05',22);
insert into TestTable (date, split) values ('2008-01-06',5);
go

-- run the query to get accummulated data
select a.date, a.split, sum(b.split) as accumm
from TestTable a cross join TestTable b
where b.date <= a.date
group by a.date, a.split
order by a.date

This should give you the following result:
date split accumm
2008-01-01 00:00:00.000 10 10
2008-01-02 00:00:00.000 3 13
2008-01-03 00:00:00.000 7 20
2008-01-04 00:00:00.000 -5 15
2008-01-05 00:00:00.000 22 37
2008-01-06 00:00:00.000 5 42

Is that the type of result you are trying to get? If not, I misunderstood the question.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 11:20:49

Hi, Not really the result value but i think the method is close.

It is an ongoing accum value for all days:

days = all days of year
Split = the same for days within month
Accum = current row split + prev row Accum value

The split value remians the same for a specif month with the first day of month always = 0
eg result:

2008-01-01 0 0
2008-01-02 4 4
2008-01-03 4 8
2008-01-04 4 12
2008-01-05 4 16
2008-01-06 4 20

Thanks for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 11:30:53

[code]SELECT t.day,t.Split,t.Split + COALESCE(t1.Accum,0) AS Accum
FROM Table t
OUTER APPLY (SELECT TOP 1 Accum
FROM Table
WHERE day<t.day
ORDER BY t.day DESC)t1
[/code]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 12:09:24

Still no luck:

The SELECT TOP 1 Accum return value = 0

then the complete result is where the Split & Accum has the sam values

2009-01-01 0 0
2009-01-03 0.0476 0.0476
2009-01-04 0.0476 0.0476
2009-01-05 0.0476 0.0476

query:
Select t.SpDate,t.Split,t.Split + COALESCE(t1.Accum,0) AS Accum
FROM
(select SpDate
,case when Date_Day = 'Work Day' then Split_Day else 0 end as Split
,0 as Accum
from
(select CONVERT(VARCHAR(10), Temp2.dates, 103) + ' 00:00:00' AS SpDate
,Period
,case when Temp2.dates = V_Hol2.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,NumDaysInMonth
,Work_Days
,round(sum(1.0E / Work_Days),4) AS Split_Day

from
(Select Period
,NumDaysInMonth
,count(Date_Day) as Work_Days
from
(Select substring(CONVERT(VARCHAR(10), dates, 103),7,4) + '.' + substring(CONVERT(VARCHAR(10), dates, 103),4,2) as Period
,case when dates = V_Hol.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,dates
,dbo.udf_GetNumDaysInMonth(dates) as NumDaysInMonth
from @TempTable

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol
on V_Hol.holiday = dates

) v1
where Date_Day = 'Work Day'
group by Period,NumDaysInMonth

) v2

left join @TempTable as Temp2
on substring(Period,6,7) = substring(CONVERT(VARCHAR(10), Temp2.dates, 103),4,2)

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol2
on V_Hol2.holiday = Temp2.dates

group by Temp2.dates
,Period
,V_Hol2.holiday
,NumDaysInMonth
,Work_Days

) v3
) t

OUTER APPLY
(
SELECT TOP 1 Accum
FROM
(select SpDate
,case when Date_Day = 'Work Day' then Split_Day else 0 end as Split
,0 as Accum
from
(select CONVERT(VARCHAR(10), Temp2.dates, 103) + ' 00:00:00' AS SpDate
,Period
,case when Temp2.dates = V_Hol2.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,NumDaysInMonth
,Work_Days
,round(sum(1.0E / Work_Days),4) AS Split_Day

from
(Select Period
,NumDaysInMonth
,count(Date_Day) as Work_Days
from
(Select substring(CONVERT(VARCHAR(10), dates, 103),7,4) + '.' + substring(CONVERT(VARCHAR(10), dates, 103),4,2) as Period
,case when dates = V_Hol.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,dates
,dbo.udf_GetNumDaysInMonth(dates) as NumDaysInMonth
from @TempTable

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol
on V_Hol.holiday = dates

) v6
where Date_Day = 'Work Day'
group by Period,NumDaysInMonth

) v7

left join @TempTable as Temp2
on substring(Period,6,7) = substring(CONVERT(VARCHAR(10), Temp2.dates, 103),4,2)

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol2
on V_Hol2.holiday = Temp2.dates

group by Temp2.dates
,Period
,V_Hol2.holiday
,NumDaysInMonth
,Work_Days

) v8 )v9

WHERE SpDate < t.SpDate
ORDER BY t.SpDate DESC
)t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 12:17:59

[code]SELECT t.day,t.Split,t.Split + COALESCE(t1.Accum,0) AS Accum
FROM Table t
OUTER APPLY (SELECT SUM(Accum) AS Accum
FROM Table
WHERE day<t.day)t1
[/code]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 12:31:16

Unfortunalty, the same result!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 12:41:56

[code]
DECLARE @Accum decimal(15,5),@Split decimal(15,5),@Date datetime
SELECT TOP 1 @Accum=Accum,@Split=Split,@Date=date
FROM YourTable

UPDATE YourTable
SET @Accum=Split=Split+CASE WHEN date <> @Date THEN @Accum ELSE 0 END,
@Date=date,
@Split=Split
[/code]
also you should have clustered index on date,split fields

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 13:07:04

I'm Lost - it does not return any records but a message stating 1 row affected - i gues thats due to the update

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 13:09:51

Thank You All for helping - clearly I will not get this working without your help!!!
I will be leaving now and will have to continue on Monday as I'm on leave.

Here is the qry - Please Assst!

DECLARE @Accum decimal(15,5),@Split decimal(15,5),@Date datetime

SELECT TOP 1 @Accum=Accum,@Split=Split,@Date=SpDate

FROM
(select SpDate
,case when Date_Day = 'Work Day' then Split_Day else 0 end as Split
,0 as Accum
from
(select CONVERT(VARCHAR(10), Temp2.dates, 103) + ' 00:00:00' AS SpDate
,Period
,case when Temp2.dates = V_Hol2.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,NumDaysInMonth
,Work_Days
,round(sum(1.0E / Work_Days),4) AS Split_Day

from
(Select Period
,NumDaysInMonth
,count(Date_Day) as Work_Days
from
(Select substring(CONVERT(VARCHAR(10), dates, 103),7,4) + '.' + substring(CONVERT(VARCHAR(10), dates, 103),4,2) as Period
,case when dates = V_Hol.holiday then 'Holiday'
when DATEPART(WEEKDAY,dates) in ('7','1') then 'Weekend' else 'Work Day' end Date_Day
,dates
,dbo.udf_GetNumDaysInMonth(dates) as NumDaysInMonth
from @TempTable

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol
on V_Hol.holiday = dates

) v1
where Date_Day = 'Work Day'
group by Period,NumDaysInMonth

) v2

left join @TempTable as Temp2
on substring(Period,6,7) = substring(CONVERT(VARCHAR(10), Temp2.dates, 103),4,2)

left join (SELECT CONVERT(VARCHAR(19), holiday, 120) as holiday from dbo.MIS_SplitFactor_Holidays) V_Hol2
on V_Hol2.holiday = Temp2.dates

group by Temp2.dates
,Period
,V_Hol2.holiday
,NumDaysInMonth
,Work_Days

) v3
) t

UPDATE t
SET @Accum=Split=Split+CASE WHEN date <> @Date THEN @Accum ELSE 0 END,
@Date=date,
@Split=Split

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 13:22:21

Also see this

http://www.sqlteam.com/article/calculating-running-totals

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-01-05 : 06:38:28

Thank You All
This got me going!

Solution 3: The "Guru's Guide" Solution
(NoIndex = 38 secs, Index = 17 secs)

SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-01-05 : 08:01:19

Hi,

Is there a way of reseting the running total to 0 based on a certain criteria and continue calculating.

When the date is ist of the month (Split_Date like '01%') then set Accum_Tot to 0.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 08:50:26

quote:
Originally posted by ismailc

Hi,

Is there a way of reseting the running total to 0 based on a certain criteria and continue calculating.

When the date is ist of the month (Split_Date like '01%') then set Accum_Tot to 0.

Regards

yup. its possible. what you need is a CASE..WHEN... construct

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources