Cumulative Values

Question

Hi

I need your help

actully i have a clumn

id     date

1      20 aug

2      12 oct

3      23 jan

now i need

id     date

1       null

2     20 aug

3      12 oct

Kindly help

Answer 1

select 1 as id, cast (null as <place the type of [date] column here >) as [date], 
union
select id+1, [date] from myTable
where id < (select max(t.id) from myTable t)

Just a guess.

---

Serg

Answer 2

Below are a couple of methods depending on your SQL Server version:

DECLARE @MyTable TABLE (
	id int NOT NULL PRIMARY KEY
	,[date] date NOT NULL
	);

INSERT INTO @MyTable VALUES
	 (1, '20120820')
	,(2, '20121012')
	,(3, '20120123');

--SQL Server 2005, SQL Server2008, SQL Server 2008 R2
WITH curr AS (
	SELECT
		[id]
		,[date]
		,ROW_NUMBER() OVER(ORDER BY [date]) AS rownum
	FROM @MyTable
	)
SELECT
	curr.[id]
	,prev.[date]
FROM curr
LEFT JOIN curr AS prev ON
	prev.rownum = curr.rownum - 1;

--SQL Servr 2012
SELECT
	[id]
	,LAST_VALUE([date]) OVER(ORDER BY [date] ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS [date]
FROM @MyTable
ORDER BY [date];

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com