help on Date difference between two different rows

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

Transact-SQL (2005)

help on Date difference between two different rows

Author

Topic

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-07 : 13:38:37

hi,

create FUNCTION [dbo].[FN_REPORT_RELEASE_CYCLE] (@BU varchar(100),@AuditType varchar(100),@AuditManager varchar(max),@FromDate datetime,@ToDate datetime, @Type int) returns int
AS
BEGIN
DECLARE @CNT INT
IF @AuditManager=''
BEGIN

If @TYPE=1

begin
select @CNT= isnull(max(Datedif(day,TaskStartDate,TaskFinishDate)),0)
from Msp_EpmTask_UserView where Projectuid in (select projectuid from msp_epmProject_Userview
Where ProjectStartDate >= @FromDate and ProjectFinishDate <= @ToDate
and BU=@BU and [Audit Type]=@AuditType
and [Status of Audit]='Completed')
and TaskName in ('Audit Field Work','Final Report Circulation date')
group by TaskStartDate,TaskFinishDate
order by TaskStartDate
end

Currently iam getting difference of 'Audit Field Work'startdate - 'Audit Field Work'finishdate but i need
'Audit Field Work'startdate - 'FinalReportCirculation finish date'

Plz help me

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-10-07 : 13:50:19

You need to do two things:
1) Join the table to itself in such a way that the columns involved appear in the same record of the resulting record set. You haven't specified how these two records relate to each other so I can't be more specific than that.

2) Use the DATEDIFF function to perform the actual difference. See BOL for details.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-07 : 13:56:09

These two records are in same table

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-07 : 14:49:10

To compare any row to a "previous" row you need to know what defines the row sequence. It could be an identity column, or a date, or anything that can be used to know the "order" of the rows. So here is an example of what BustazKool suggested: Assume your Row1 and Row2 was an INT column called [Row] and was an unbroken sequece of INTs from one to however many rows are in the table. Then you could use DATEDIFF with a self join like this:


select a.col1
       ,b.col2
       ,datediff(day, a.col1, b.col2) as [date_difference]
from   <yourTable> a
inner join <yourTable> b
      on b.row = a.row+1

So first you must specify what defines your row sequence. If it isn't something simple like a a contiguous sequence of INT values then there are other ways to achieve association of one row to it's "previous" row. But what you've provided so far won't do it.

Be One with the Optimizer
TG

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-08 : 02:48:29

plz help it is urgent

Sachin.Nand

2937 Posts

Posted - 2009-10-08 : 02:56:48

Didnt you understand what BustazKool & TG suggested.

PBUH

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-08 : 03:02:07

quote:
Originally posted by Idera

Didnt you understand what BustazKool & TG suggested.

PBUH

NOOO

hi,

create FUNCTION [dbo].[FN_REPORT_RELEASE_CYCLE] (@BU varchar(100),@AuditType varchar(100),@AuditManager varchar(max),@FromDate datetime,@ToDate datetime, @Type int) returns int
AS
BEGIN
DECLARE @CNT INT
IF @AuditManager=''
BEGIN

If @TYPE=1

begin
select @CNT= isnull(max(Datedif(day,TaskStartDate,TaskFinishDate)),0)
from Msp_EpmTask_UserView where Projectuid in (select projectuid from msp_epmProject_Userview
Where ProjectStartDate >= @FromDate and ProjectFinishDate <= @ToDate
and BU=@BU and [Audit Type]=@AuditType
and [Status of Audit]='Completed')
and TaskName in ('Audit Field Work','Final Report Circulation date')
group by TaskStartDate,TaskFinishDate
order by TaskStartDate
end

Currently iam getting difference of 'Audit Field Work'startdate - 'Audit Field Work'finishdate but i need
'Audit Field Work'startdate - 'FinalReportCirculation finish date'

Plz help me

Sachin.Nand

2937 Posts

Posted - 2009-10-08 : 03:31:24

quote:
Originally posted by krish001

These two records are in same table

declare @tbl as table(id int identity,date datetime)
insert into @tbl
select '13-apr-2009' union all
select '14-apr-2009' union all
select '16-apr-2009' union all
select '18-apr-2009' union all
select '20-apr-2009' union all
select '22-apr-2009' union all
select '23-apr-2009' union all
select '25-apr-2009'

select * from @tbl

select a.date
,b.date
,datediff(day, a.date, b.date) as [date_difference]
from @tbl a
inner join @tbl b
on b.id = a.id+1

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources