Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Extract names against service
Message
From
19/09/2008 09:38:51
 
 
To
19/09/2008 08:41:17
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Vista
Network:
Windows 2008 Server
Database:
MS SQL Server
Miscellaneous
Thread ID:
01348870
Message ID:
01348903
Views:
18
Crudely:

Select cName, (Depart - Arrival)/365 as ServeTime ;
from years ;
into cursor x ;
where BETWEEN( (Depart - Arrival)/365, 20, 35)

I haven't allowed for leap years but this is a quick first cut.

It may be a good idea to create a function that works out the service time, allowing for leap years, etc., and substitute that for the BETWEEN() function, eg:

Select cName, MyLeapYearFunction( Depart, arrival) as ServeTime ;
from years ;
into cursor X

Select cName, ServeTime ;
from X ;
into Cursor Y ;
where BETWEEN( ServeTime , 20, 35)

>Dear Experts
>
>CREATE cursor Years (cname c(15),arrival D(8), Depart d(8))
>
>INSERT INTO Years VALUES ('A',{^1945-07-01},{^1965-07-01}) 	&& 20 year
>INSERT INTO Years VALUES ('B',{^1975-07-01},{^1977-07-01})	&& 2 year
>INSERT INTO Years VALUES ('C',{^1975-07-01},{^1977-07-01})	&& 2 year
>INSERT INTO Years VALUES ('D',{^1970-07-01},{^1991-07-01})	&& 21 year
>INSERT INTO Years VALUES ('E',{^1981-07-01},{^1986-07-01})	&& 5 year
>INSERT INTO Years VALUES ('F',{^1981-07-01},{^1986-07-01})	&& 5 year
>INSERT INTO Years VALUES ('G',{^1987-07-01},{^1997-07-01})	&& 10 year
>INSERT INTO Years VALUES ('H',{^1998-07-01},{^2009-07-01})	&& 11 year
>INSERT INTO Years VALUES ('I',{^1940-07-01},{^1975-07-01})	&& 35 year
>INSERT INTO Years VALUES ('J',{^1940-07-01},{^1980-07-01})	&& 40 year
>INSERT INTO Years VALUES ('K',{^1940-07-01},{^1980-07-01})	&& 40 year
>INSERT INTO Years VALUES ('L',{},{^1980-07-01})	&& 0 year
>INSERT INTO Years VALUES ('M',{},{^1980-07-01})	&& 0 year
>
>I want to extract employees whose service duration is 20 to 35 years
>
>I need following result
>
>cname--Years
>'A'-----20
>'D'-----21
>'I'-----35
>
>Please help
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.
Previous
Reply
Map
View

Click here to load this message in the networking platform