| Next record [message #373718] |
Wed, 02 May 2001 13:54  |
Anita
Messages: 21
Registered: March 2000
|
Junior Member |
|
|
Hello
I have a table in which
there are multiple rows for each employee and I want to find the next record for each employee.
Can any one help
|
|
|
|
|
|
| Re: Next record [message #373728 is a reply to message #373727] |
Thu, 03 May 2001 08:55  |
Anita
Messages: 21
Registered: March 2000
|
Junior Member |
|
|
O.K FOR ex I have atransfer TABLE
emplid Territory START_DT END_DT
1000 X 1/MAR/2001 still working
1111 X 1/JAN/2000 1/JAN/2001
2222 X 2/MAY/1999 2/OCT/1999
3000 Y 1/JUL/2000 still working
4000 Y 1/JAN/2000 1/JUN/2000
5000 Y 2/JUN/1999 2/OCT/1999
1000 Z 1/may/2001 Still working
1111 Z 1/JAN/2000 1/FEB/2001
5000 Z 2/JUN/1999 2/OCT/1999
the USER want TO know how many days since 1/1/2001 the territories were vacant. You'd need TO look at fo gaps
BETWEEN the END DATE OF one RECORD AND the START OF the NEXT
AND accumulate the days.
Basically what users are looking at is how many days they are losing money by not having a rep in a territory.
For ex fro terriotry X since 1/jan/2001 to 1/mar/2001 no one was active/working in it that is 2 months of gap.
though for terriotry "Y" from 2/oct/1999 to 1/jan/2000 here it is three months of gap but it is 2000 and we are not looking at it .
and similarly for "Z" territory from 1/feb/2001 to 1/may /2001 the terriotry was vacant for 3 months .I want to consider this .
Hope I am clear
Thanx
|
|
|
|
| Re: Next record [message #373737 is a reply to message #373728] |
Thu, 03 May 2001 14:41 |
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
|
Junior Member |
|
|
you want to try
select territory, max( END_DT )- min( START_DT) +1 - sum( end_dt - START_DT+1)
from your_table where end_dt is not null group by territory
where no end_dt=start_dt for a territory
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
