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

user76
Starting Member

5 Posts

Posted - 2004-04-15 : 14:30:31

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"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-15 : 14:31:12

http://www.sqlteam.com/item.asp?ItemID=12654

Tara

user76
Starting Member

5 Posts

Posted - 2004-04-15 : 14:35:46

thx i did go thru that article but i cud not understand the solution, i dont need to calculate the enddate i already have it. my requirement is to find the continuity

surefooted
Posting Yak Master

188 Posts

Posted - 2004-04-15 : 14:52:28

To just get the id would this work?

select id
from table
where valid = 'y'
group by id
having datediff(dd, min(startdate), max(enddate)) >= 120

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-15 : 15:00:14

[code]

INSERT INTO myTable99([id], startdate, enddate, seq, valid)
SELECT 100, '2002/01/01', '2002/02/01', 1, 'y' UNION ALL
SELECT 100, '2002/03/01', '2002/04/01', 2, 'y' UNION ALL
SELECT 100, '2002/04/01', '2002/05/01', 3, 'y' UNION ALL
SELECT 200, '2003/01/01', null, 1, 'y' UNION ALL
SELECT 300, '2002/01/01', '2003/01/01', 1, 'y'
GO

SELECT DATEDIFF(d,MIN_startdate,MAX_enddate) FROM (
SELECT [id], MIN(startdate) AS MIN_StartDate,MAX(enddate) AS MAX_EndDate
FROM myTable99
GROUP BY [id]) AS XX
WHERE DATEDIFF(d,MIN_startdate,MAX_enddate) >= 120
GO

DROP TABLE myTable99
GO

[/code]

However I would argue that [id] 100 doesn't get in..they had a month off..

Hmmmm....seems vaguely familiar...what college?

Brett

8-)

user76
Starting Member

5 Posts

Posted - 2004-04-15 : 15:21:49

thx a ton to all of you,
its not college work Bret :-) you are right 100 is not to be included but then result returned 120 days ..

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-15 : 16:26:21

Yeah you're going to have to read (closely) Jeff's runs and streaks article...

I'll play with it...tomorrow...

It's Margarita time....

Brett

8-)

user76
Starting Member

5 Posts

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

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