Query date difference 2

[Shal2]

Hi all,

I have a table which has rows like below:
-----------------------------------
Documentnum TYPE TRANDATE
------------------------------------
907837 TFI 2004-02-25
907837 TFO 2004-02-23

907688 TFI 2004-02-24
907688 TFO 2004-02-20
-------------------------------------
Now, I want to select 907688 because the difference between the dates is more then 3 days in the last two rows. Is it possible to do, if you notice the only difference is the TYPE which is TFI in one and TFO in the last row, both have same documentnum.

How can I write the select statement to obtain the result.

Thank you,
Shal

 

[cheyney]

something like this?

select a.DocumentNum, b.TranDate as TFODate, a.TranDate as TFIDate, datediff(dd, b.TranDate, a.TranDate) as DaysDifference
from Table1 a, Table1 b
where a.DocumentNum = b.DocumentNum
and a.Type = 'TFI'
and b.Type = 'TFO'
and datediff(dd, b.TranDate, a.TranDate) > 3

Cheyney

 

[Shal2]

Thank you Cheyney,

This works perfectly well for me.
I am not good in joins and specially self joins!

Thanks again,
Shal

 

[Shal2]

I am back again,

What if the row for TFI does not exist and I want to retrieve the documentnum which has TFO but not TFI?

In the above table:
If third row was not there, then I would want to retreive last row.

Is it possible?
Thanks,

Shal

 

[cheyney]

select b.DocumentNum, b.TranDate as TFODate, a.TranDate as TFIDate, datediff(dd, b.TranDate, a.TranDate) as DaysDifference
from Table1 a, Table1 b
where a.DocumentNum = b.DocumentNum
and a.Type = 'TFI'
and b.Type = 'TFO'
and datediff(dd, b.TranDate, a.TranDate) > 3
UNION

select b.DocumentNum, b.TranDate as TFODate, Null as TFIDate, Null as DaysDifference
from Table1 b
where 
b.Type = 'TFO' and
not exists (select * from Table1 a where a.Type = 'TFI' and a.DocumentNum = b.DocumentNum)

Cheyney

 

[Shal2]

This is working for me,
thank you again.
Shal

 

[Shal2]

Hi All,
Had to modify the query a bit to get what I really wanted. If anybody is interested here is the query:

Purpose was to select the documentnum which was TFO(transfered out) but not yet TFI(transfered in) even after 3 days from the TFO date.

Used the code posted by Cheyney but surrounded it with another select so as to select only those which were outstanding.

Select * from (Cheney's code above)NewTable
where NewTable.DaysDifference IS NULL

Thanks,
Shal

 

[cheyney]

In that case you only need to use the portion of my code which appears after the UNION...no need to to get the extraneous rows, only to filter them back out

Cheyney

 

[Shal2]

You are right again,
just copying and not thinking!!
silly me,
Shal

 

[Shal2]

On second thought, if I just use the query after Union, it will give me documentnum which have datediff less than three.

 

[Shal2]

Finally used query:

select
b.DocumentNum,
b.TranDate as TFODate,
Null as TFIDate,
Null as DaysDifference
from
Table1 b
where
b.TRANDATE > '03/01/2004' and
b.Type = 'TFO' and
not exists (select * from Table1 a where a.Type = 'TFI' and a.DocumentNum = b.DocumentNum)
and b.TranDate < dateadd(dd,3,'03/01/2004')