To compare data in different columns for different rows of records:

Database

To compare data in different columns for different rows of records:

I’ve a table named MBR_DATA which is of below structure:

MBR_ID      ADMT_DT  DSCHRG_DT
01 01/01/2012 01/15/2012
01 01/24/2012 01/31/2012 <--
02 01/01/2012 01/09/2012
02 01/01/2012 01/09/2012
02 01/01/2012 01/09/2012
02 01/13/2012 12/31/8888 <--
03 01/28/2012 12/31/8888
03 01/29/2012 12/31/8888
04 01/08/2012 12/31/8888

I’ve to fetch the count of MBR’s whose have got re-admitted - which means their new ADMT_DT > DSCHRG_DT of previous admission. In the above case I should get count as 2 since 2 MBR’s have got readmitted- highlighted readmission in bold.

NOTE: We have to identify readmission within 2-30 days from previous DSCHRG_DT.

Quick ideas are really appreciated!

Thanks,

Vaish

4 REPLIES
Teradata Employee

Re: To compare data in different columns for different rows of records:

You can achieve that using self-join with a condition you mentioned above. To get the count you will be required  to have a nested SELECT or derived table, and count the MBR_IDs in upper SELECT.

Senior Apprentice

Re: To compare data in different columns for different rows of records:

Hi Vaish,

SELECT COUNT(*)
FROM
(
SELECT *
FROM tab
QUALIFY
ADMT_DT
- MIN(DSCHRG_DT)
OVER (PARTITION BY MBR_ID
ORDER BY ADMT_DT, DSCHRG_DT
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) BETWEEN 2 AND 30
) AS dt

Re: To compare data in different columns for different rows of records:

Thanks a lot Deiter.. This works out :)

Re: To compare data in different columns for different rows of records:

Hi Deiter,

Could you please help me with similar question? Taking above example (modified data), I have something like:

 

MBR_ID      ADMT_DT  DSCHRG_DT

    01   01/01/2012 01/21/2012

    01   01/20/2012 01/24/2012

    01   01/18/2012 01/25/2012

    01   01/15/2012 01/26/2012

    01   01/24/2012 01/31/2012

    02   01/01/2012 01/09/2012

    02   01/01/2012 01/15/2012

    02   01/01/2012 01/14/2012

    02   01/13/2012 12/31/8888

 

If the DSCHRG_DT for an MBR_ID falls in the range of another ADMT_DT and DSCHRG_DT for same MBR_ID, I would need count of those.

Example output below: COUNT values with reason how value is computed is displayed

MBR_ID      ADMT_DT  DSCHRG_DT COUNT

    01   01/01/2012 01/21/2012 0 (reason: no DSCHRG_DT falls in the range of this ADMT_DT and DSCHRG_DT for this MBR ID)

    01   01/20/2012 01/24/2012 1 (reason: above 1 DSCHRG_DT falls in the range of this ADMT_DT and DSCHRG_DT)

    01   01/18/2012 01/25/2012 2 (reason: above 2 DSCHRG_DT falls in the range of this ADMT_DT and DSCHRG_DT)

    01   01/15/2012 01/26/2012 3 (reason: above 3 DSCHRG_DT falls in the range of this ADMT_DT and DSCHRG_DT)

    01   01/24/2012 01/31/2012 3 (reason: above 3 DSCHRG_DT falls in the range of this ADMT_DT and DSCHRG_DT)

    02   01/01/2012 01/09/2012 0 (reason: no DSCHRG_DT falls in the range of this ADMT_DT and DSCHRG_DT for this MBR ID)

    02   01/01/2012 01/15/2012 1 (reason: above 1 DSCHRG_DT falls in the range of this ADMT_DT and DSCHRG_DT)

    02   01/01/2012 01/14/2012 1 (reason: 1 DSCHRG_DT (01/09/2012) falls in the range of this ADMT_DT and DSCHRG_DT for this MBR ID)

    02   01/13/2012 12/31/8888 2

 

Thanks a much in advance Deiter!

-Ravi