looping through records with and without cursor

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)

looping through records with and without cursor

Author

Topic

shantheguy
Starting Member

22 Posts

Posted - 2012-04-20 : 11:10:56

Hi all,

I have a requirement in sql server and it is as follows:

col1 col2 col3
10 120 2012-10-11
10 130 2012-12-11
10 140 2012-12-18

here my requirement is like i need to get the difference in dates record by record as below:

col1 col2 col3 col 4 days difference in col3 column
10 120 2012-10-11 0
10 130 2012-12-11 61
10 140 2012-12-18 7

How can i achieve this please help me out.

thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 11:43:27

You should be able to do a left join on to the table itself and calculate this. Since there is no sequential number, you would need to generate one - which is what the CTE in the query below does.

;WITH cte AS
(
	SELECT *,ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col3) AS RN
	FROM YourTable
)
SELECT
	a.col1, a.col3, a.col3,
	COALESCE(DATEDIFF(dd,b.col3,a.col3),0) AS differenceCol
FROM
	cte a
	LEFT JOIN cte b ON
		a.RN = b.RN+1
		AND a.col1 = b.col1

shantheguy
Starting Member

22 Posts

Posted - 2012-04-23 : 01:01:00

Thanks alot...sunitabeck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources