sql query

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

sql query

Hi ,

 

I have data as below.

 

Member      eligibility date            qualifying code

========================================

A1                1/1/2016                       X1

A1                2/1/2016                       X1

A1                4/1/2016                       X1

A1                5/1/2016                       X2

I need to pick up first record if there is a break in dates of each month and next by code.

My answer should be like below.

A1            1/1/2016                   X1

A1             4/1/2016                  X1

A1             5/1/2016                  X2

Any help is appreciated.

 

Thanks 


Accepted Solutions
Enthusiast

Re: sql query

To slove this, you can use windowed analytical functions

 

something like this should work

 

SyntaxEditor Code Snippet

select 
dateintable as actualrowdate, max(dateintable) OVER (partition by membeeeeer order by dateintable rows between 0 precceding and 1 following) nextdate, case when nextdate-actualrowdate > 1 then 'BREAK' else 'NOT BREAK' end breakindicator

from xy

 

1 ACCEPTED SOLUTION
4 REPLIES
Enthusiast

Re: sql query

To slove this, you can use windowed analytical functions

 

something like this should work

 

SyntaxEditor Code Snippet

select 
dateintable as actualrowdate, max(dateintable) OVER (partition by membeeeeer order by dateintable rows between 0 precceding and 1 following) nextdate, case when nextdate-actualrowdate > 1 then 'BREAK' else 'NOT BREAK' end breakindicator

from xy

 

Enthusiast
Enthusiast

Re: sql query

I tried the below solution. It worked partially. I need to include qualifying code also for spanning.

Please help.

Enthusiast

Re: sql query

I need first record with min elig date if code doesn't change.

If there is a gap in date or code change then i need first record.

 

Any help is appreciated.