You must Sign In to post a response.
  • Category: SQL Server

    How to compare database using sql ?


    Are you looking for a way to compare database columns using sql ? then read this thread to know how to compare it



    Hi,

    I am dealing with a large database which looks like following-

    Empid policyid startdate enddate
    120 A120 07/01/2010 06/30/2011
    120 A121 07/01/2011 06/30/2012
    121 B119 07/01/2014 06/30/2015
    122 A125 07/01/2012 06/30/2013
    122 A126 07/01/2013 06/30/2014
    122 A127 06/01/2014 06/30/2015

    My requirement is, i need to check for each employee that is there any policy for which start date of any policy is prior to the end date of earlier policy. Like dual coverage at the same time.

    Can anyone help here as how to write query for this condition.
  • #748946
    Hello Ashutosh ,

    I can not getting you can you elaborate more?

    Regards,
    Nirav Prabtani (Senior Web Developer)
    Email : niravjprabtani@gmail.com
    blog : niravprabtani.blogspot.in

  • #748982
    Hi ,Use below Script

    CREATE TABLE #temptable
    (
    empid INT,
    policyid CHAR(5),
    startdate DATE,
    enddate DATE
    )

    INSERT INTO #temptable
    VALUES (120,
    'A120',
    '07/01/2010',
    '06/30/2011')

    INSERT INTO #temptable
    VALUES (120,
    'A121',
    '07/01/2011',
    '06/30/2012')

    INSERT INTO #temptable
    VALUES (121,
    'B119',
    '07/01/2014',
    '06/30/2015')

    INSERT INTO #temptable
    VALUES (122,
    'A125',
    '07/01/2012',
    '06/30/2013')

    INSERT INTO #temptable
    VALUES (122,
    'A126',
    '07/01/2013',
    '06/30/2014')

    INSERT INTO #temptable
    VALUES (122,
    'A127',
    '06/01/2014',
    '06/30/2015')

    SELECT *,
    ROW_NUMBER()
    OVER (
    partition BY [empid]
    ORDER BY policyid) AS rownum
    INTO #temp
    FROM #temptable

    SELECT *,
    CASE
    WHEN startdate < col THEN 'invaild'
    END AS [Status]
    FROM (SELECT *,
    (SELECT enddate
    FROM #temp
    WHERE rownum = a.rownum - 1
    AND empid = a.empid) AS [Col]
    FROM #temp a) AS tes

    DROP TABLE #temptable

    Hope it will helpful to you!If means mark this post..

    Regards
    Siva


  • Sign In to post your comments