Problem with Subtraction Of Dates

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

SQL Server Development (2000)

Problem with Subtraction Of Dates

Author

Topic

naveenwinsome
Starting Member

2 Posts

Posted - 2007-04-06 : 10:05:00

I have to get the difference of dates of single column, the table is temporary table. The records will be inserted into the table through a cursor ( it will retriev values from another table and insert into this temporary table ) and also i have a column called "StopsTheClock". If the value changes from 0 to 1 or remains same i have to subtract dates and store the value.

For eg:

_ID_______Date________________StopsTheClock
_1_______03/03/2007______________0_________
_2_______03/04/2007______________0_________
_3_______03/06/2007______________1_________
_4_______03/09/2007______________0_________
_5_______03/20/2007______________1_________
_6_______03/27/2007______________0_________

My calculation should be

(date1-date2)+(date2-date3)+(date4-date5)+(date6-getdate())

plz mail me if u got solution naveenwinsome@gmail.com

Naveen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 10:16:11

[code]
declare @table table
(
ID int,
Date datetime,
StopsTheClock int
)

insert into @table
select 1, '03/03/2007', 0 union all
select 2, '03/04/2007', 0 union all
select 3, '03/06/2007', 1 union all
select 4, '03/09/2007', 0 union all
select 5, '03/20/2007', 1 union all
select 6, '03/27/2007', 0

select sum(
case when (a.StopsTheClock = 0 and b.StopsTheClock = 0)
or (a.StopsTheClock = 0 and b.StopsTheClock = 1)
or (b.StopsTheClock is null)
then datediff(day, a.Date, coalesce(b.Date, getdate()))
end
)
from @table a left join @table b
on a.ID = b.ID - 1
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-06 : 15:52:59

And this, just in case the ID's are not sequential

SELECT	SUM(DATEDIFF(DAY, r.FromDate, r.ToDate))
FROM	(
		SELECT	t1.Date AS FromDate,
			(SELECT COALESCE(MIN(t2.Date), CURRENT_TIMESTAMP) FROM @Table AS t2 WHERE t2.Date > t1.Date) AS ToDate
		FROM	@Table AS t1
		WHERE	t1.StopsTheClock = 0
	) AS r

Peter Larsson
Helsingborg, Sweden

naveenwinsome
Starting Member

2 Posts

Posted - 2007-04-08 : 01:08:28

Thank You.

quote:

Originally posted by khtan


declare	@table table
(
	ID		int,
	Date		datetime,
	StopsTheClock	int
)

insert into @table
select 1, '03/03/2007', 0 union all
select 2, '03/04/2007', 0 union all
select 3, '03/06/2007', 1 union all
select 4, '03/09/2007', 0 union all
select 5, '03/20/2007', 1 union all
select 6, '03/27/2007', 0

select	sum(
	case 	when 	(a.StopsTheClock = 0 and b.StopsTheClock = 0)
		or	(a.StopsTheClock = 0 and b.StopsTheClock = 1)
		or	(b.StopsTheClock is null)
		then 	datediff(day, a.Date, coalesce(b.Date, getdate()))
		end
	)
from	@table a left join @table b
	on	a.ID	= b.ID - 1

Naveen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources