Post reply

Merge Membership Effective Date and Expiration Date

  • June 2, 2010 at 11:27 am

    #235882

    I have a table called membership which contains memberid, effdat and expdat. When a member extend the membership on time, the next effective date would start the same day as the expiration date, thus there is no break in membership. However, if a membership does not extend on time, the next effective date would not be the same day or the next day of expiration date, thus causing a break between previous expiration date and next effective date.

    Is there any way that I can merge all expiration date and effective date that are not break and leaving only the expiration date and effective dates that are break.

    Thanks,

    qjlee

  • June 3, 2010 at 6:58 am

    This was removed by the editor as SPAM

  • June 3, 2010 at 6:48 pm

    #1176262

    For example:

    MemberID Effdat EXPdat

    ABC123 01/01/2005 06/30/2005

    ABC123 06/30/2006 11/30/2005

    ABC 123 12/02/2005 01/31/2006

    ABC123 01/31/2006 06/30/2006

    After the merge, the membership should appears as

    MEMBERID EFFDAT EXPDAT

    ABC123 01/01/2005 11/30/2005

    ABC123 12/02/W005 06/30/2006

    Thanks,

    Qjlee

  • June 3, 2010 at 7:07 pm

    #1176266

    stewartc-708166 (6/3/2010)

    Please provide the DDL for the table and some sample data, so we can provide you with a workable solution.

    Qjlee, apparently you don't know how to do this in a way that makes things easy for us to help you. You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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