Urgent help on Runs and sequences

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

Transact-SQL (2000)

Urgent help on Runs and sequences

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-20 : 10:01:27

smp writes "i have a table with the below structure


id    startdate                  enddate          seq    valid
100    2002/01/01                2002/02/01       1      y
100    2002/03/01                2002/04/01       2      y
100    2002/04/01                2002/05/01       3      y
200    2003/01/01                null             1      y
300    2002/01/01                2003/01/01       1      y

I need to find out people who are enrolled (valid=y) continuously for 4 months or (120) days. example id 100 and 300 are enrolled for 4 months

can anybody please help...
thx in advance
S"

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-20 : 10:03:22

Select [id] from table where valid='y' and datediff(month,startdate,enddate)>=4

;-]

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-20 : 10:20:27

opps misrerad a little

Select [id], startdate, enddate, valid
from
(select
[id],min(startdate) startdate ,max(enddate) enddate, valid
from enrollments where valid='y' group by [id],valid
)grp_enrolls
where datediff(month,grp_enrolls.startdate,grp_enrolls.enddate)>=4

assuming table is called enrollments.

;-]

user76
Starting Member

5 Posts

Posted - 2004-05-04 : 14:35:30

This is in conjunction with the same above question, thx for the reply, the queries work for pulling out data for all people who have enrolled for 120 days, but what i need to get is only people who have enrolled for the first 120 days of their enrollment period any ideas or suggestions, pls help

Example

id startdate enddate seq valid
100 2002/01/01 2002/02/01 1 y
100 2002/03/01 2002/04/01 2 y
100 2002/04/01 2002/05/01 3 y
200 2003/01/01 2003/02/01 1 y
300 2002/01/01 2003/01/01 1 y
200 2004/01/01 null 2 y

In this above listing 200 is invalid as the persons startdate + 120 days he is not enrolled but enrolls later.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources