count days on date by row - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

count days on date by row

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-06-23 : 03:44:06

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-06-23 : 03:55:41

We are going to need more information:

What is your table structure?
Is there only ever a single action for each stage of the process?
Is your date really formatted that way or is it actually a proper DATETIME format?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
http://www.hollowtreewebdesign.co.uk - a web design company in its infancy

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-06-23 : 04:06:26

im thinking to create a new column stating 'new' status for 1 days, 'assigned' status for 2 days, and so on.

the date is just an example. is actually a proper DATETIME format.

thanks

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-06-23 : 04:09:40

i would like to get the result of

3/6 minus 1/6 = 2 days,

the datetime minus by rows. how can i do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-23 : 04:16:06

[code]
declare @tbl table
(
status varchar(10),
date datetime
)

insert into @tbl
select 'new', '2011-06-01' union all
select 'assigned', '2011-06-03' union all
select 'pending', '2011-06-10' union all
select 'closed', '2011-06-15'

; with cte as
(
select status, date, row_no = row_number() over (order by date)
from @tbl
)
select c1.status, c1.date, days = isnull(datediff(day, c2.date, c1.date), 1)
from cte c1
left join cte c2 on c1.row_no = c2.row_no + 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-06-23 : 04:25:55

thanks KH

but i have a list of ID. this is only example.
so how can i apply to all? union 1 by 1?? impossible. cuz the date is different according to different ID.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-23 : 04:26:53

you only need the query part that is starting from like ;with cte . . .

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-23 : 04:28:20

replace the "yourtable" with your actual table name


; with cte as
(
	select	ID, status, date, row_no = row_number() over (partition by ID order by date)
	from	yourtable
)
select	c1.ID, c1.status, c1.date, days = isnull(datediff(day, c2.date, c1.date), 1)
from	cte c1
	left join cte c2	on   c1.ID      = c2.ID	
                                and  c1.row_no	= c2.row_no + 1

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-06-23 : 04:44:50

ok noted.

thanks alot KH!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources