Need help on query to select before and after values

Analytics
Enthusiast

Need help on query to select before and after values

Hello, could you help me please to write select query - Columnt - BLOCK_CODE has different block codes I need a query which gives me what was the value before and after TERM block code. KEY_ACC BLOCK_CODE EFF_START_DATE 123 COLL 9/2/2016 123 COLL 9/3/2016 123 TERM 9/5/2016 123 TERM 9/6/2016 123 TERM 9/7/2016 123 TERM 9/8/2016 123 TERM 11/8/2016 123 DC 11/9/2016 123 DC 11/10/2016 So i need a query which provide me info BLOCK_CODE before and after TERM.

Accepted Solutions
Junior Contributor

Re: Need help on query to select before and after values

This returns the 2nd row of a group of rows with a NOT NULL BCXBS_CNTRY_TERM_DT:

SELECT *
FROM BCXBS_1
QUALIFY Min(BCXBS_CNTRY_TERM_DT) -- NULL date two rows before current row
        Over (PARTITION BY BCXBS_KEY_ACCT
              ORDER BY EFFECTIVE_START_DATE
              ROWS BETWEEN 2 Preceding AND 2 Preceding) IS NULL
    AND Min(BCXBS_CNTRY_TERM_DT) -- no NULL date in previous row
        Over (PARTITION BY BCXBS_KEY_ACCT
              ORDER BY EFFECTIVE_START_DATE
              ROWS BETWEEN 1 Preceding AND 1 Preceding) IS NOT NULL

 

1 ACCEPTED SOLUTION
9 REPLIES
Enthusiast

Re: Need help on query to select before and after values

SEL
BCXBS_KEY_ACCT,
BCXBS_CNTRY_TERM_DT,
(BCXBS_GLBL_BLK_RSN_CD_2 ) AS BLOCK_CODE_AFTER_TERM,
EFFECTIVE_START_DATE

FROM BCXBS_1
where BCXBS_CNTRY_TERM_DT is not null
and BCXBS_KEY_ACCT='123'

qualify row_number() over (partition by BCXBS_KEY_ACCT,BCXBS_CNTRY_TERM_DT order by EFFECTIVE_START_DATE asc) = 1

-------------------------

Output which i get is  - 

BCXBS_KEY_ACCT BCXBS_CNTRY_TERM_DT BLOCK_CODE_AFTER_TERM EFFECTIVE_START_DATE
1 123                         11/9/2010                               P2L                                             8/12/2015
2 123                         5/13/2013                               TERM                                         5/13/2013

 

I need to have the output P2L.. Because this is next BLOCK_CODE_AFTER_TERM.

below informatrion from main table as by effective date next code is P2L...

 

BCXBS_KEY_ACCT BCXBS_CNTRY_TERM_DT BLOCK_CODE_AFTER_TERM EFFECTIVE_START_DATE

123                            5/13/2013                               TERM                                         11/14/2013
123                            5/13/2013                               P2L                                             11/15/2013

Junior Contributor

Re: Need help on query to select before and after values

Your example data is hard to read, the result of your query doesn't match the data and you didn't specify exactly what you want.

 

Do you need one row with the blockcodes of the row before/after a group of rows with 'TERM' or both rows before/after seperate?

 

Enthusiast

Re: Need help on query to select before and after values

Hi Dnoeth,

 

Apologoies for late reply and  example data which is hard to read..

 

Maybe you could help me with this scenario please –

 

i.e. we have two BCXBS_CNTRY_TERM_DT different dates, mostly it should be one, but in some cases it is allowed to have more than one..

 

 

BCXBS_KEY_ACCT

BCXBS_CNTRY_TERM_DT

BCXBS_GLBL_BLK_RSN_CD_2

EFFECTIVE_START_DATE

692

583

?

COLL

8/28/2015

693

583

?

COLL

8/29/2015

694

583

9/1/2015

TERM

8/31/2015

695

583

9/1/2015

TERM

9/1/2015

696

583

9/1/2015

TERM

9/2/2015

1044

583

9/1/2015

MAN2

10/12/2016

1045

583

9/1/2015

MAN2

10/13/2016

1046

583

?

COLL

10/14/2016

1047

583

10/17/2016

TERM

10/15/2016

1048

583

10/17/2016

TERM

10/17/2016

1049

583

10/17/2016

TERM

10/18/2016

 

The requirement is to select BCXBS_GLBL_BLK_RSN_CD_2 which is next EFFECTIVE_START_DATE after BCXBS_CNTRY_TERM_DT happened, I have highlighted.

 

This should sound like BCXBS_GLBL_BLK_RSN_CD_2 (BLOCK_CODE) in next day after termination date..

Junior Contributor

Re: Need help on query to select before and after values

This returns the 2nd row of a group of rows with a NOT NULL BCXBS_CNTRY_TERM_DT:

SELECT *
FROM BCXBS_1
QUALIFY Min(BCXBS_CNTRY_TERM_DT) -- NULL date two rows before current row
        Over (PARTITION BY BCXBS_KEY_ACCT
              ORDER BY EFFECTIVE_START_DATE
              ROWS BETWEEN 2 Preceding AND 2 Preceding) IS NULL
    AND Min(BCXBS_CNTRY_TERM_DT) -- no NULL date in previous row
        Over (PARTITION BY BCXBS_KEY_ACCT
              ORDER BY EFFECTIVE_START_DATE
              ROWS BETWEEN 1 Preceding AND 1 Preceding) IS NOT NULL

 

Enthusiast

Re: Need help on query to select before and after values

Thank you very much!!! it has solved my doubts!!!

Enthusiast

Re: Need help on query to select before and after values

Hi dnoeth,

 

Maybe you could help me please with one more.. which is quite too complex for me...

 

 

BCXBS_KEY_ACCT

BCXBS_CNTRY_TERM_DT

BLOCK_CODE

EFFECTIVE_START_DATE

789

792

?

COLL

3/9/2016

790

792

?

COLL

3/10/2016

791

792

?

COLL

3/11/2016

792

792

?

COLL

3/12/2016

793

792

3/15/2016

TERM

3/14/2016

794

792

3/15/2016

TERM

3/15/2016

839

792

3/15/2016

TERM

5/6/2016

840

792

3/15/2016

TERM

5/7/2016

841

792

3/15/2016

FS

5/9/2016

842

792

3/15/2016

FS

5/10/2016

843

792

3/15/2016

FS

5/11/2016

844

792

3/15/2016

FS

5/12/2016

845

792

3/15/2016

FS

5/13/2016

846

792

?

DECL

5/14/2016

847

792

?

DECL

5/16/2016

848

792

?

DECL

5/17/2016

849

792

5/19/2016

TERM

5/18/2016

850

792

5/19/2016

TERM

5/19/2016

 

I need to select rows before BCXBS_CNTRY_TERM_DT but in this case i also need to add the dates in column BCXBS_CNTRY_TERM_DT which are after rows   -

example OP should be like this -  

 

BCXBS_KEY_ACCT

BCXBS_CNTRY_TERM_DT

BLOCK_CODE

EFFECTIVE_START_DATE

792

792

3/15/2016

COLL

3/12/2016

848

792

5/19/2016

DECL

5/17/2016

 

Thank you,

Algirdas

Junior Contributor

Re: Need help on query to select before and after values

Should be quite similar to the previous result:

SELECT ..., 
   Min(BCXBS_CNTRY_TERM_DT) -- next row's date
           Over (PARTITION BY BCXBS_KEY_ACCT
                 ORDER BY EFFECTIVE_START_DATE
                 ROWS BETWEEN 1 Following AND 1 Following) AS NextTermDt
FROM BCXBS_1
QUALIFY BCXBS_CNTRY_TERM_DT IS NULL -- no date for current row
    AND NextTermDt IS NOT NULL      -- but for next row

 

Re: Need help on query to select before and after values

Super @dnoeth. Awesome!

Enthusiast

Re: Need help on query to select before and after values

dnoeth, thank you very much, you got skills!