calculated column from previous row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

calculated column from previous row?

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2011-05-11 : 17:50:12

how can I accomplish getting the previous row value + 1 in a column.

if I cannot do it in a calculated field.

can I do it while selecting into another table? Or Update?

Anyway without a cursor? :D

thanks



create table #dates 
(
	Paycheck_Dt datetime not null,
	AdjustedPaycheck_Dt datetime not null,
        PeriodEnd_Dt datetime not null,
        PeriodStart_Dt AS ***previos row PeriodEnd_Dt + 1***)	

INSERT INTO #dates
SELECT '2011-01-06 00:00:00.000','2011-01-06 00:00:00.000','2010-12-30 00:00:00.000' UNION ALL
SELECT '2011-01-13 00:00:00.000','2011-01-13 00:00:00.000','2011-01-06 00:00:00.000' UNION ALL
SELECT '2011-01-20 00:00:00.000','2011-01-20 00:00:00.000','2011-01-13 00:00:00.000' UNION ALL
SELECT '2011-01-27 00:00:00.000','2011-01-27 00:00:00.000','2011-01-20 00:00:00.000' UNION ALL
SELECT '2011-02-03 00:00:00.000','2011-02-03 00:00:00.000','2011-01-27 00:00:00.000' UNION ALL
SELECT '2011-02-10 00:00:00.000','2011-02-10 00:00:00.000','2011-02-03 00:00:00.000' UNION ALL
SELECT '2011-02-17 00:00:00.000','2011-02-17 00:00:00.000','2011-02-10 00:00:00.000' UNION ALL
SELECT '2011-02-24 00:00:00.000','2011-02-24 00:00:00.000','2011-02-17 00:00:00.000' UNION ALL
SELECT '2011-03-03 00:00:00.000','2011-03-03 00:00:00.000','2011-02-24 00:00:00.000'

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2011-05-11 : 18:10:54

I went with row over partition.

--weekly
select
a.Day_Dt AS Paycheck_Dt,
a.Adjusted_Dt AS AdjustedPaycheck_Dt,
DATEADD( dd, -1 * @nPeriod1EndDay_Nbr, a.Day_Dt ) AS PeriodEnd_Dt,
DATEADD(dd, 1, b.Day_Dt) AS PeriodStart_Dt
from
(
select row_number() over
( partition by Day_Nm
order by Day_Nm ) as rownum,
Day_Nm, Day_Dt, Adjusted_Dt
from #dates
) a
LEFT OUTER JOIN
(
select row_number() over
( partition by Day_Nm
order by Day_Nm ) as rownum,
Day_Nm, Day_Dt
from #dates
) b
ON a.rownum = b.rownum + 1
AND b.Day_Nm IN ('Thursday')
where a.Day_Nm IN ('Thursday')

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-05-12 : 19:41:56

For large tables you will have much better performance with a clustered index update or a CTE.

If you still need assistance on how to improve speed let me know and I'll post some examples. I'm lazy right now though so I'd rather not if you are not going to use them:)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources