Getting correct start date for member eligibility

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hi All,
    I have a report to develop for which I am writing a query.

    I need help with getting correct startdate for member eligibilty..
    I have 3 members with different ID's  1003,2003,4003
    we have eligibility defined for every year in separate row.. with a startdate and end date.
    but for some there is a gap example member :1003 has no eligibility in 2015.

    in the report I have to go back only 2 years from today's date i.e. till 3/30/2015. and capture the start date from that span and present it.
    but for 1003 as i don't have any data for 2015, it is capturing 2014 dates. I want it to capture the 2016 date instead as we cannot send any data before 2015.
    any help with this.
    thanks in advance

    Sql code is here:

    create table #temp
    (
    id varchar(20), stdate varchar(20),enddate varchar(20)
    )

    insert into #temp values( '1003','20140501','20141231')
    insert into #temp values ( '1003','20160101','20161231')
     insert into #temp values ( '1003','20170101','20171231')

      insert into #temp values ( '2003','20140401','20141231')
    insert into #temp values( '2003','20150101','20151231')
    insert into #temp values ( '2003','20160101','20161231')
     insert into #temp values ( '2003','20170101','20171231')

      insert into #temp values ( '4003','20130101','20151231')

    insert into #temp values ( '4003','20160101','20161231')
     insert into #temp values ( '4003','20170101','20171231')

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

        DECLARE @BeginningOfYear VARCHAR(20)

        SELECT
            
            @BeginningOfYear =  CAST(YEAR(GETDATE()) - 2 AS varchar(10)) + '-01-01'

    SELECT *
               FROM (SELECT *, Ltrim(Rtrim(Str(b.stdate)))   AS min_STDT,
                    
                    Row_number ()
                    OVER (
                     partition BY b.id
                     ORDER BY b.id, b.stdate desc ) AS RN
                 FROM (
                                 SELECT DISTINCT
                         id,
                         stdate ,
                         enddate
            
                                                            -- else         a.estdt
                                                        
                    FROM #temp a
                   
    where
                                         -- and Ltrim(Rtrim(Str(a.estdt))) >=REPLACE(@BeginningOfYear, '-', '')

                                           REPLACE(@BeginningOfYear, '-', '') BETWEEN Ltrim(Rtrim(Str(a.stdate))) AND CONVERT(VARCHAR(8), Getdate(), 112)

                                         -- and Ltrim(Rtrim(Str(a.estdt))) >= REPLACE(@BeginningOfYear, '-', '')
                                         )     b
                                         ) c
               WHERE rn = 1

     

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • J Livingston SQL

    SSC Guru

    Points: 51272

    a few q's......you have posted in SQL 2005 forum...pls confirm this is the version you are working on.
    can you post expected results from your sample set up...so we know what you actually wish to achieve to xref our solutions
    are you actually storing dates as varchars?...or is this just how you set up your rest data?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Lynn Pettis

    SSC Guru

    Points: 442308

    I made a few changes, such as using int for id instead of varchar(20), datetime for instead of varchar(20) for the dates.
    does the following provide what you are trying to accomplish?

    create table #temp(
      id int -- varchar(20),
      , stdate datetime -- varchar(20),
      , enddate datetime --varchar(20)
    )


    insert into #temp values(1003,'20140501','20141231');
    insert into #temp values(1003,'20160101','20161231');
    insert into #temp values(1003,'20170101','20171231');

    insert into #temp values(2003,'20140401','20141231');
    insert into #temp values(2003,'20150101','20151231');
    insert into #temp values(2003,'20160101','20161231');
    insert into #temp values(2003,'20170101','20171231');

    insert into #temp values(4003,'20130101','20151231');
    insert into #temp values(4003,'20160101','20161231');
    insert into #temp values(4003,'20170101','20171231');

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


    DECLARE @BeginningOfYear datetime -- VARCHAR(20)

    SELECT @BeginningOfYear =  DATEADD(year,datediff(year,0,getdate()),0) --CAST(YEAR(GETDATE()) - 2 AS varchar(10)) + '-01-01'

    with basedata as (
    select
      id
      , stdate
      , enddate
      , rn = row_number() over (partition by id order by stdate)
    from
      #temp
    where
      DATEADD(year,-2,getdate()) < enddate-- and GETDATE()
    )
    select
      id
      , stdate
      , enddate
      , convert(varchar(10), stdate, 120) MinStartDate
    from
      basedata
    where
      rn = 1;

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Thank you Lynn Pettis


    I can't believe this came with such simple condition..i was breaking and fixing for last 2 days to get this right..

    I still made one more change to my code as i was having  following in Row_number() ORDER BY ID, stdt  DESC

    i had to remove DESC form here..

    You guys are genius!

    Thanks for your help again..

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply