Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Tricky sql-question

    I have a problem in a database we have. It contains prices and agreementdates for different stuff.

    We get data from a source as a textfile, my db is supposed to spit out the faulty rows and return them.

    The problem is in the agreementdates, two agreements can't overlap eachother. It can look like this when its ok:

    Id StartDate EndDate Price
    --------------------------------------------
    123 20040101 20041231 12.50
    123 20050101 20050228 13.00

    But somtimes the agreementdates overlap:

    Id StartDate EndDate Price
    --------------------------------------------
    123 20040101 20041231 12.50
    123 20040701 20041231 13.00

    These two last rows are not supposed to be in my database, they are to be spit out. But how do I select them?

    SQL-query or stored procedure would do the job but how?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think this is the logic you want:

    select Table1.*
    from YourTable Table1
    inner join YourTable Table2
    on (Table1.StartDate <= Table2.EndDate and Table1.EndDate >= Table2.StartDate)

    blindman

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    I modified blindman's query - if you ever have a scenario where you can have 2 identical records and this is an error in your environment then you should modify this query to do subquery with a count(*) testing for a value greater than 1 (query 2 example) :

    select Table1.*
    from yourtable Table1
    inner join yourtable Table2
    on (table1.id = table2.id and ((Table2.StartDate between Table1.StartDate and table1.enddate) or
    (Table2.EndDate between Table1.StartDate and table1.enddate)) and
    ((table1.startdate <> table2.startdate) or (table1.enddate <> table2.enddate)))

    select Table1.*
    from yourtable Table1
    where
    1 < (select count(*) from yourtable table2
    where table1.id = table2.id and
    ((Table2.StartDate between Table1.StartDate and table1.enddate) or
    (Table2.EndDate between Table1.StartDate and table1.enddate)))

  4. #4
    Join Date
    Jul 2003
    Posts
    123
    I have a problem... we found that in some cases only one of the overlapping agreements get selected.

    Why
    ((Table2.StartDate between Table1.StartDate and table1.enddate) or
    (Table2.EndDate between Table1.StartDate and table1.enddate))

    and not
    ((Table1.StartDate between Table2.StartDate and table2.enddate) or
    (Table1.EndDate between Table2.StartDate and table2.enddate))

    The last version makes more rows generated than the first, we get all the agreements that are overlapping.. (I hope)

    So will I have a problem with this different code than the first?
    (wondering)

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    doesn't work like it's supposed to...

    The scenario is this:

    Agreement1.start = 2003-09-15
    Agreement1.end = 2008-12-31

    Agreement2.start = 2004-01-01
    Agreement2.end = 2004-12-31

    Of those two only the first agreement get kicked ot with this version:
    ((Table2.StartDate between Table1.StartDate and table1.enddate) or
    (Table2.EndDate between Table1.StartDate and table1.enddate))

  6. #6
    Join Date
    Jul 2003
    Posts
    123
    Turns out I have to select the rows a little differently thats all...
    This works (tested)...

    select * from yourtable where id in
    (select Table1.id
    from yourtable Table1
    inner join yourtable Table2
    on (table1.id = table2.id and ((Table2.StartDate between Table1.StartDate and table1.enddate) or
    (Table2.EndDate between Table1.StartDate and table1.enddate)) and
    ((table1.startdate <> table2.startdate) or (table1.enddate <> table2.enddate)))
    )

    It now spits out all agreements with overlapping agreementdates...

Posting Permissions

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