locked
Group by based on Effective and Expiration Date RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I have this issue where i need to group each insurance based on effective date and expiration date. Example

    

    I want to group these to show as following 

    1. COVERED CA- HEALTH NET HMO       2016-04-14       2017-01-26

    2. COVERED CA- HEALTH NET HMO       2017-01-26       NULL

    3. Medical Managed Care- HealthNet     2016-02-08       2016-04-14

    4. Medical Managed Care- HealthNet     2016-04-14       2017-01-28

    5........

    I could do this using rank and then replace, but my code is getting too complicated, please help

    Shaik, Rasool




    Monday, April 17, 2017 11:41 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote 
    -- pls try this 
DECLARE @tbl as table ( name Varchar(255), Eff_Date DateTime, Exp_Date DateTime);

insert into @tbl
SELECT 'HMO','2016-4-14 18:39:26' ,'19000101' UNIon ALL 
SELECT'HMO','19000101' ,'2017-1-26 17:32:56'  UNIon ALL 
SELECT'HMO','2017-1-26 17:42:35' ,'19000101' ;

--code from here 
SELECT 
Eff_Dates .name , Eff_Dates.Eff_Date AS Eff_Dt , MIN(Exp_Dates.Exp_Date) AS Exp_Dt
FROM @tbl  Eff_Dates
LEFT JOIN @tbl Exp_Dates ON Eff_Dates.name = Exp_Dates.name AND Eff_Dates.Eff_Date <= Exp_Dates.Exp_Date
where Eff_Dates.Eff_Date > '19000101'
group by Eff_Dates .name , Eff_Dates.Eff_Date

    • Marked as answer by SHAIK RASOOL Tuesday, April 18, 2017 12:28 AM
    Tuesday, April 18, 2017 12:12 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote 
    -- pls try this 
DECLARE @tbl as table ( name Varchar(255), Eff_Date DateTime, Exp_Date DateTime);

insert into @tbl
SELECT 'HMO','2016-4-14 18:39:26' ,'19000101' UNIon ALL 
SELECT'HMO','19000101' ,'2017-1-26 17:32:56'  UNIon ALL 
SELECT'HMO','2017-1-26 17:42:35' ,'19000101' ;

--code from here 
SELECT 
Eff_Dates .name , Eff_Dates.Eff_Date AS Eff_Dt , MIN(Exp_Dates.Exp_Date) AS Exp_Dt
FROM @tbl  Eff_Dates
LEFT JOIN @tbl Exp_Dates ON Eff_Dates.name = Exp_Dates.name AND Eff_Dates.Eff_Date <= Exp_Dates.Exp_Date
where Eff_Dates.Eff_Date > '19000101'
group by Eff_Dates .name , Eff_Dates.Eff_Date

    • Marked as answer by SHAIK RASOOL Tuesday, April 18, 2017 12:28 AM
    Tuesday, April 18, 2017 12:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for this, above gave me enough logic to get the result i wanted ( with little tweak:) ) 

    SELECT 
    Eff_DATES .name , Eff_Dates.Eff_DT AS Eff_Dt , MIN(Exp_Dates.EXP_DT) AS Exp_Dt
    FROM #TEMP EFF_DATES
    LEFT JOIN #TEMP  EXP_DATES ON Eff_Dates.name = Exp_Dates.name AND Eff_Dates.Eff_dt <= Exp_Dates.EXP_DT

    group by Eff_Dates .name , Eff_Dates.EFF_DT
    HAVING 
      NOT(Eff_Dates.Eff_DT ='19000101' AND MIN(Exp_Dates.EXP_DT) ='19000101')

    Thanks for helping

    Tuesday, April 18, 2017 12:30 AM