Professional Web Applications Themes

Query to find the starting of new contact - Microsoft SQL / MS SQL Server

Hi I've a table called Contract id------start_date--------end_date ======================== 1------1/1/2000---------12/31/2000 1------2/1/2001---------7/1/20001 1------7/2/2001---------12/31/2002 1------1/1/2003---------4/5/2003 1------4/6/2003---------NULL Here start_date & end_date are the starting and ending of conctracts. Is the contractor valid, then end_date is NULL (ie, no end_Date) A person may cancel his contact OR change contract type. During change, a new entry will insert into the table by ending the first contact, ie, the end_date of the first contact and start_date of the second contact differs by one day. Here I want to check from which date, the contacter is wtih me continuously, Here the valid date is the second row, start_date, ...

  1. #1

    Default Query to find the starting of new contact

    Hi I've a table called Contract

    id------start_date--------end_date
    ========================
    1------1/1/2000---------12/31/2000
    1------2/1/2001---------7/1/20001
    1------7/2/2001---------12/31/2002
    1------1/1/2003---------4/5/2003
    1------4/6/2003---------NULL

    Here start_date & end_date are the starting and ending of conctracts.
    Is the contractor valid, then end_date is NULL (ie, no end_Date)

    A person may cancel his contact OR change contract type. During change, a
    new entry will insert into the table by ending the first contact, ie, the
    end_date of the first contact and start_date of the second contact differs
    by one day.

    Here I want to check from which date, the contacter is wtih me continuously,
    Here the valid date is the second row, start_date, ie 2/1/2000. After that
    date, even though he changed his contract type, he is continuously in
    contract without any break, to till date.

    Please help me identifying the start date of a contactror
    Thanks
    Shafeek Khalidh



    Shafeek Guest

  2. #2

    Default Re: Query to find the starting of new contact

    Hi Tom, here the query returns the starting date of the contacter wtih my
    company, its not the starting date for the current continuous contract.

    After the first starting, he may go out of the contact for some time and may
    come back later. When he comes back , that date is the date, I'm looking
    for.

    Thanks for your help
    Shafeek


    Shafeek Guest

  3. #3

    Default Re: Query to find the starting of new contact

    The ISO model of time of time is based on half-open intervals, so a date
    like '2001-01-01' includes all the "points in time" from '2001-01-01
    00:00.000...' thru '2001-01-01 59:59.9999..., so '2001-01-01' and
    '2001-01-02' are contigous, but do not overlap in the sense of having a
    common duration.

    For this solution, you need a Calendar table, which is a common SQL
    programming trick -- it si a table of calendar dates and all the other
    temporal information for the enterprise.

    SELECT X.contract_num, MIN(X.begin_dt), X.end_dt
    FROM (SELECT C1.contract_num, C1.begin_dt, MAX(C2.end_dt)
    FROM Contracts AS C1, Contracts AS C2
    WHERE C1.contract_num = C2.contract_num
    AND C1.begin_dt < C2.end_dt
    AND DATEDIFF(DD, C1.begin_dt, C2.end_dt) +1
    = (SELECT COUNT(DISTINCT K1.cal_date)
    FROM Calendar AS K1
    WHERE EXISTS
    (SELECT *
    FROM Contracts AS C3
    WHERE C1.contract_num = C3.contract_num
    AND K1.cal_date BETWEEN C3.begin_dt
    AND C3.end_dt
    AND C3.begin_dt BETWEEN C1.begin_dt
    AND C2.end_dt
    AND C3.end_dt BETWEEN C1.begin_dt
    AND C2.end_dt))
    GROUP BY C1.contract_num, C1.begin_dt) AS X(contract_num,
    begin_dt, end_dt)
    GROUP BY contract_num, end_dt;

    How it work is simple: you use three copies of Contracts: C1 is the
    start, C2 is the end, and C3 is all the durations in the middle. Find
    the length of [C1, C2] in days. Find the number of distinct calendar
    days in all the durations inside the range of [C1, C2]. If there is one
    or more days not covered by a duration, reject it.

    The use of a NULL for the "still open" contract is fine, but remember to
    use "COALESCE(end_dt, CURRENT_TIMESTAMP)" in your code to get the right
    answers

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: Query to find the starting of new contact

    Hmmm, I see. Sorry, I missed the point of your initial
    example.

    First, I'll re-state that the logic to accomplish this
    would be much simpler if you had a column for the
    contractor's status. Then, all you would have to do is
    look for the oldest record that has an 'active'
    or 'continuous' status for each contractor. This would
    require that the status column for each of the 'active'
    records be updated when the contractor cancels, but that
    would be easier than working with the complex logic you
    are faced with now.

    Anyway, if you'll forgive me for preaching, I'll take a
    stab at a solution given you current structure;

    If I understand correctly, the first contract of a series
    will have the most recent start_date that is not one day
    after another record's end_date (with the same id). Also,
    since only id's with a null for the end_date of at least
    one record are of interest, how about something like:

    SELECT id,
    (SELECT TOP 1 start_date
    FROM Contract C1
    WHERE NOT EXISTS
    (SELECT *
    FROM Contract C2
    WHERE C2.id = Contract.id AND
    C2.end_date=C1.start_date - 1)
    ORDER BY start_date DESC
    )AS StartDate
    FROM dbo.Contract
    WHERE ISDATE(end_date)=0

    This should do it, but there may be more direct approach.
    By the way, in my earlier post I used the ISNULL()
    function, but I don't think my usage is correct since the
    end_date column is a datetime type. I think the ISDATE()
    function should work though since the null values should
    not be considered "dates"

    Good luck,

    Tom
     
    contacter wtih my 
    continuous contract. 
    for some time and may 
    date, I'm looking 
    Tom Guest

Similar Threads

  1. Where to find: Advanced Query Wizard
    By zientar in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: February 6th, 03:55 PM
  2. query to find primary key
    By roohbir in forum MySQL
    Replies: 4
    Last Post: March 20th, 10:00 PM
  3. UltraSuite Customers: Please contact me for a full refund (or I'll contact you within the next day or so)
    By J.S. \(UltraSuite\) in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: April 19th, 11:46 PM
  4. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  5. Query to find table name?
    By JDP@Work in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 11th, 10:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139