Results 1 to 3 of 3

Thread: Difficult Query

  1. #1
    Join Date
    Dec 2011
    Posts
    8

    Unanswered: Difficult Query

    I have a query that is rather difficult. I have solved it, but I would like to solve it without creating an additional table or by using a cursor. In other words, I would like the query to be applied only to the two tables defined by the SQL code at the end of this discussion. Again, an additional table may not be created. I have a solution that involves creating an additional table, I do not want to do that. Also, queries involving cursors are not permitted.

    One table will be called S_FROM_TO (for Supplier From-To)

    Semantically, this table (S_FROM_TO) means the supplier with the number S# was under contract to supply parts from this day (and not on the day immediately before that day) to this day (and not on the day immediately after that day).

    The data is as follows

    S_FROM_TO

    S# from to

    1 1/4/2006 1/10/2006
    2 1/2/2006 1/4/2006
    2 1/7/2006 1/10/2006
    3 1/3/2006 1/10/2006
    4 1/4/2006 1/10/2006
    5 1/2/2006 1/10/2006

    Another table is called SP_FROM_TO (for Supplier of Parts FROM TO)

    Semantically, this table (SP_FROM_TO) means Supplier S# supplied part P# from this date (and not on the day immediately before that day) to this day (and not on the day immediately after that day) .

    SP_FROM_TO

    S# P# from to

    1 1 1/4/2006 1/10/2006
    1 2 1/5/2006 1/10/2006
    1 3 1/9/2006 1/10/2006
    1 4 1/5/2006 1/10/2006
    1 5 1/4/2006 1/10/2006
    1 6 1/6/2006 1/10/2006
    2 1 1/2/2006 1/4/2006
    2 1 1/8/2006 1/10/2006
    2 2 1/3/2006 1/3/2006
    2 2 1/9/2006 1/10/2006
    3 2 1/8/2006 1/10/2006
    4 2 1/6/2006 1/9/2006
    4 4 1/4/2006 1/8/2006
    4 5 1/5/2006 1/10/2006

    If SP_FROM_TO has a row showing supplier Sx as able to supply some specific part from day spf to day spt, then table S_FROM_TO shows the supplier as being under contract for that period. No row in SP_FROM_TO will show a time period for the supply of a part that is outside the corresponding suppliers contract period.

    The question that is to be put into a query (Again, SQL that does not involve creating an additional table or SQL that involves a cursor) that involve the two tables above are:

    Query: Get S#-FROM-TO rows for suppliers who have been unable to supply any parts at all during at least one interval of time, where FROM and TO together designate a maximal interval during which supplier S# was in fact unable to supply any part at all while under contract.

    In other words, for the time each supplier is under contract, for what time periods did that supplier not supply a part.

    This query involves both of the tables.

    The result of your query should be:

    S# from to
    _________________________
    2 1/7/2006 1/7/2006
    3 1/3/2006 1/7/2006
    5 1/2/2006 1/10/2006

    So, I am looking for the SQL that returns the result set above.

    For your convenience, I supply the code below to create and populate the tables. These are the only tables that can be used:

    Create table S_FROM_TO(S# int, fromDate datetime, toDate datetime)
    GO
    insert into S_FROM_TO values(1, '1/4/2006', '1/10/2006')
    insert into S_FROM_TO values(2, '1/2/2006', '1/4/2006')
    insert into S_FROM_TO values(2, '1/7/2006', '1/10/2006')
    insert into S_FROM_TO values(3, '1/3/2006', '1/10/2006')
    insert into S_FROM_TO values(4, '1/4/2006', '1/10/2006')
    insert into S_FROM_TO values(5, '1/2/2006', '1/10/2006')
    GO

    Create table SP_FROM_TO (S# int, P# int, fromdate datetime, todate datetime)
    GO

    insert into SP_FROM_TO VALUEs(1, 1, '1/4/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(1, 2, '1/5/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(1, 3, '1/9/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(1, 4, '1/5/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(1, 5, '1/4/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(1, 6, '1/6/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(2, 1, '1/2/2006','1/4/2006')
    insert into SP_FROM_TO VALUEs(2, 1, '1/8/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(2, 2, '1/3/2006','1/3/2006')
    insert into SP_FROM_TO VALUEs(2, 2, '1/9/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(3, 2, '1/8/2006','1/10/2006')
    insert into SP_FROM_TO VALUEs(4, 2, '1/6/2006','1/9/2006')
    insert into SP_FROM_TO VALUEs(4, 4, '1/4/2006','1/8/2006')
    insert into SP_FROM_TO VALUEs(4, 5, '1/5/2006','1/10/2006')
    GO
    Last edited by gurzynski; 12-22-11 at 16:42. Reason: Added some addition info on from and to days

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Difficult Query

    In SS2005 or above, the following query may be an option:

    Code:
    ;with sfto as
    ( select [S#] ,fromDate as ftDate ,toDate from S_FROM_TO
      union all
      select [S#] ,dateadd(dd,1,ftDate) ,toDate
      from sfto
      where ftDate < toDate
    )
     ,spfto as
    ( select [S#] ,fromDate as ftDate ,toDate from SP_FROM_TO
      union all
      select [S#] ,dateadd(dd,1,ftDate) ,toDate
      from spfto
      where ftDate < toDate
    )
    select s.[S#] ,min(s.ftDate) as [from] ,max(s.ftDate) as [to]
    from
    ( select [S#] ,ftDate from sfto
      except
      select [S#] ,ftDate from spfto
    ) s
    group by s.[S#]
    order by s.[S#]

  3. #3
    Join Date
    Dec 2011
    Posts
    8

    Looking at It

    homerow,

    Thanks for the query. I am looking at it now with a more extensive dataset, but it does give the correct answer for the data I supplied.

    Thanks again

Posting Permissions

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