Script not working correctly

Toad Data Point & Toad Intelligence Central
1

Hi, I have been trying to work with this “following” procedure script but for some reason it does not always produce the correct results (examples below).

CREATE TABLE ar_asrh_dm_etl_beacon.Flg_OV_Algo1a1a AS
(SELECT Claim_id, Mem_id, Dte_SvcFrom_id AS “DOS or Admit”, Dte_Disch_id AS discharge,

MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following) AS “Readmit”,

 MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
 ROWS between 1 following and 1 following) - Dte_Disch_id AS "Day Diff",

 CASE WHEN 
 MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
 ROWS between 1 following and 1 following) - Dte_SvcFrom_id  BETWEEN 0 and 15   

 THEN 1 ELSE 0
 END AS "14DyReadmitFLG", Stay_id,  SvcPlace_id, ClaimCategory_id, Dx_Cd, Dx_Dl,
        ProvSpc_ds, ProvSpc_cd, ProvSpc_id, PatStat_dl, PatStat_id,  ProvRend_LastName,
        ProvRend_Id, Dte_Admit_id, Dte_Disch_id

FROM ar_asrh_dm_etl_beacon.Flg_OV_Algo1a1

GROUP BY Mem_id, Dte_SvcFrom_id, Claim_id, ProvSpc_ds, ProvSpc_cd, ProvSpc_id, PatStat_dl, PatStat_id,
“DOS or Admit”, discharge, ProvRend_id, ProvRend_LastName, Stay_id, SvcPlace_id, Dte_Admit_id, Dte_Disch_id,
ClaimCategory_id, Dx_Cd, Dx_Dl, Proc_CPT_id , Proc_CPT2_id, ProcSubCat_CPT_id)

WITH DATA;

EXAMPLES:

Claim_id Mem_id DOS or Admit discharge Readmit Day Diff 14DyReadmitFLG
5497721 1432 11/13/2009 11/30/2009 12/1/2009 1 0
6092480 3057 8/18/2010 8/24/2010 9/4/2010 11 0
7161403 3057 9/4/2010 9/22/2010 9/24/2010 2 0
7162225 3057 9/24/2010 9/30/2010 10/13/2010 13 0

Also, in some instances the Day Diff is 0 and the flag is 1 and other times 0 day Diff is flagged as a 0?

Also, in some instances it populates the incorrect “following date” should be 10/15/2010 not 10/13/2010 here.

Claim_id Mem_id DOS or Admit discharge Readmit Day Diff 14DyReadmitFLG
7161403 3057 9/4/2010 9/22/2010 9/24/2010 2 0
7162225 3057 9/24/2010 9/30/2010 10/13/2010 13 0
7503170 3057 10/15/2010 10/16/2010 0

I have several of these simple algorithms with different date cut points for different purposes but basically the same idea and they all have this same trouble.

Help! Please :wink:

WHP