Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
3
Guests Online:
43

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

Consecutive Data Range

Microsoft SQL Server
Please wait...

Consecutive Data Range

Postby lacare on Wed Mar 18, 2015 1:14 am

Ok I got this from another post by another person. My needs are very similar but i lack the SQL knowledge.

MemberCode ----- ClaimID ----- StartDate ----- EndDate
00001 ----- 012345 ----- 2010-01-15 ----- 2010-01-20
00001 ----- 012350 ----- 2010-01-19 ----- 2010-01-22
00001 ----- 012352 ----- 2010-01-20 ----- 2010-01-25
00001 ----- 012355 ----- 2010-01-26 ----- 2010-01-30
00002 ----- 012357 ----- 2010-01-20 ----- 2010-01-25
00002 ----- 012359 ----- 2010-01-30 ----- 2010-02-05
00002 ----- 012360 ----- 2010-02-04 ----- 2010-02-15
00003 ----- 012365 ----- 2010-02-15 ----- 2010-02-30

  1. DECLARE @t TABLE (MemberCode  VARCHAR(100), ClaimID  
  2.     INT,StartDate   DATETIME,EndDate DATETIME)
  3. INSERT @t
  4. VALUES
  5. ('00001'   ,       012345   ,        '2010-01-15'   ,       '2010-01-20')
  6. ,('00001'   ,       012350   ,       '2010-01-19'   ,       '2010-01-22')
  7. ,('00001'   ,       012352   ,       '2010-01-20'   ,       '2010-01-25')
  8. ,('00001'   ,       012355   ,       '2010-01-26'   ,       '2010-01-30')
  9. ,('00002'   ,       012357   ,       '2010-01-20'   ,       '2010-01-25')
  10. ,('00002'   ,       012359   ,       '2010-01-30'   ,       '2010-02-05')
  11. ,('00002'   ,       012360   ,       '2010-02-04'   ,       '2010-02-15')
  12. ,('00003'   ,       012365   ,       '2010-02-15'   ,       '2010-02-28')
  13. ,('00004'   ,       012366   ,       '2010-03-18'   ,       '2010-03-23')
  14. ,('00005'   ,       012367   ,       '2010-03-19'   ,       '2010-03-25')
  15. ,('00006'   ,       012368   ,       '2010-03-20'   ,       '2010-03-21')
  16. ,('00006'   ,       012369   ,       '2010-04-20'   ,       '2010-04-22')
  17. ,('00006'   ,       012370   ,       '2010-05-20'   ,       '2010-05-21')
  18. ,('00006'   ,       012371   ,       '2010-05-22'   ,       '2010-03-23')
  19.  



DESIRED OUTPUT:

MEMBERID, CLAIMID, MIN_START DATE, MAX_START DATE, DAYS

So member might have several ranges of consecutive claims coming in and it should capture total days for those consecutive set. Sets should probably be over 2 days just to cut down on professional claims since we just want to capture institutional claims.

also for example :

,('00006' , 012369 , '2010-04-20' , '2010-04-22')
,('00006' , 012370 , '2010-05-20' , '2010-05-21')
,('00006' , 012371 , '2010-05-22' , '2010-03-23')

this should be
MEMBERID, CLAIMID, MIN_START DATE, MAX_START DATE, DAYS
00006 , 012369 , '2010-04-20' , '2010-04-22' , 2
00006 , 012370 , '2010-05-20' , '2010-03-23' , 3

using sql 2008 needless to say millions of records involved ...
http://www.sqlfiddle.com/#!3/c3365/20
lacare
Newbie
Newbie
 
Posts: 1
Joined: Wed Mar 18, 2015 1:04 am
Unrated