Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: Trouble phrasing a query

    i have 2 tables

    first contains the current information
    and the second contains the changes to it

    so structure looks something like this
    Data
    ID, Valid, Value1, value2, DateSet


    DataAudit
    LogID, ID, Valid, Value1, value2, DateSet, DateLogged


    so it might contain
    Data
    1,1,10,12,#01/10/2011#
    2,0,10,12,#28/09/2011#
    3,0,10,12,#20/10/2010#
    4,1,5,50,#01/10/2011#

    DataAudit
    1,1,1,10,12,#01/10/2009#,#01/09/2010#
    2,2,0,10,12,#28/09/2011#,#20/10/2010#
    3,2,0,10,12,#20/10/2010#, #28/09/2011#
    4,1,1,5,50,#01/09/2010#,#01/01/2011#
    5,1,1,15,12,#01/01/2011#,#01/03/2011#
    6,1,0,10,16,#01/03/2011#,#01/06/2011#
    7,1,0,10,19,#01/06/2011#,#01/10/2011#
    8,4,1,5,50,#01/09/2011#,#01/10/2011#


    so the question is how do you write the query that will select what each value was on over a given date range
    so for example on the #02/09/2011#

    for data id 1 it should return DataAudit LogID 7
    for data id 2 it should return DataAudit LogID 3
    for data id 3 it should return Data ID 3
    for data id 4 it should return DataAudit LogID 8

    i can't do a straight
    where dateSet > @StartDate and dateLogged < @endDate
    as this will complete ignore any record that doesn't change inside the date range
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Shouldn't it be something more like
    Code:
    select fields
    from dataaudit
    where dateset < '02/09/2011'
      and datelogged > '02/09/2011'
    union
    select fields
    from data
    where dateset < '02/09/2011'
    [/code]

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by MCrowley View Post
    Shouldn't it be something more like
    Code:
    select fields
    from dataaudit
    where dateset < '02/09/2011'
      and datelogged > '02/09/2011'
    union
    select fields
    from data
    where dateset < '02/09/2011'
    [/code]
    problem is if it changed at say 02/09/2011 11:00
    then that value is still with in the date range of 02/09/2011 - 03/09/2011 ie 2nd september 2011

    so i would need both the pre 11:00 and the Post 11:00 as both applied during the time frame where as with yours that data wouldn't appear at all as it happened after the start date but before the end date
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    To be fair, you have not included time portions in any of the test data. Assuming that the time portion is stored separately from the date...
    Code:
    select fields
    from dataaudit
    where dateset <= '02/09/2011'
      and datelogged => '02/09/2011'
    union
    select fields
    from data
    where dateset <= '02/09/2011'

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'm not complaining at least your trying to help

    actually the time doesn't matter but the start date and end date for the date range could be 1 day apart, a week apart or anything really and that still means that a change within the time range is ignore where as my first suggest is the exact opposite

    in fact if we unioned your query with mine think we would have a complete dataset
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    this is my latest attempt

    still checking the result set
    i'm using a memory table to reduce work when i modify the criteria

    Code:
    DECLARE @StartDate DATE
    DECLARE @EndDate DATE
    
    SELECT  @StartDate = '01 sep 2011' ,
            @EndDate = '01 oct 2011'
    
    
    DECLARE @tmps TABLE
        (
          LogID INT ,
          ID INT ,
          Active BIT ,
          Name VARCHAR(60),
          TypeID INT,
          ParentID INT ,
          DateSet DATE ,
          DateLogged DATE
        )
    
    INSERT  INTO @tmp
            ( LogID ,
              ID ,
              Active ,
              Name,
              ParentID ,
              DateSet ,
              DateLogged  ,
              TypeID
            )
            SELECT  logID ,
                    ID ,
                    Active ,
                    Name,
                    ParentID  ,
                    DateCreated ,
                    DateSet ,
                    DateLogged ,
                    TypeID
            FROM    Audit.Data
           UNION ALL
            SELECT -1 ,
                    ID,
                    Active ,
                    Name,
                    ParentID ,
                    DateSet ,
                    GETDATE() + 1 ,
                    TypeID
            FROM    dbo.Data
    
    
    SELECT * FROM @tmp
    WHERE  Active = 1 AND (
    @StartDate BETWEEN DateSet AND DateLogged 
    or
    @EndDate BETWEEN DateSet AND DateLogged 
    )
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Wait. Are you looking for the state of the data on a particular day, or all changes in a certain time range?

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'm looking for all values that applied during a timeframe

    so what value applied when the timeframe started and any changes within it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I may understand what you're looking for, and if I do the problem is part of timespan queries. the logic for selecting them is counter-intuitive at first, but once I thought it through it became blindingly clear. See if this helps:
    Code:
    --  ptp  20111025  Deal with date span queries
    
    CREATE TABLE #foo(
       fooId			INT			IDENTITY
    ,  dateBegin		DATETIME
    ,  dateEnd			DATETIME
       )
       
    INSERT INTO #foo (
       dateBegin, dateEnd
       )  SELECT        '1999-12-24', '2000-01-06'
       UNION ALL SELECT '2000-01-01', '2000-01-31'
       UNION ALL SELECT '2001-02-02', '2001-02-14'
       UNION ALL SELECT '2003-03-17', '2003-04-15'
       UNION ALL SELECT '2004-02-29', '2005-05-01'
       UNION ALL SELECT '2004-07-04', '2004-09-06'
       UNION ALL SELECT '2005-11-24', '2005-12-25'
    
    DECLARE @dBegin		DATETIME
    ,  @dEnd			DATETIME
    
    SELECT @dBegin = '1950-01-01', @dEnd = GETDATE()
    
    SELECT *
       FROM #foo AS a
       WHERE  @dBegin   <= a.dateEnd
          AND a.dateBegin <= @dEnd
    
    SELECT @dBegin = '1999-12-25'
    
    SELECT *
       FROM #foo AS a
       WHERE  @dBegin   <= a.dateEnd
          AND a.dateBegin <= @dEnd
    
    SELECT @dBegin = '2005-12-01', @dEnd = GETDATE()
    
    SELECT *
       FROM #foo AS a
       WHERE  @dBegin   <= a.dateEnd
          AND a.dateBegin <= @dEnd
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i can't quite see what your upto there but i'll have another look tomorrow seee it if make more sense when its not 5 minutes to the end of the day
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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