Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    41

    Hello - Selecting the Lowest DateTime if it meets some conditions

    Hello

    My table:

    http://s9.postimage.org/65cvk8n0f/Capture.png

    ID (PK), userID (FK), from [Datetime], to[datetime], type [int] , value [nvarchar]
    ...


    Conditions
    I want to select the column "to" which is a datetime-column for all users within the table.

    One user can have multiple registered rows in "mytable", so I want to select the row that contains the newest "from" date.

    The newest "from"-column for a specific user is where I am struggling... Because if the "to"-column of any of the registered rows by the current user is equal to the newest "from -1 day" column for the user, I want to select the second newest from-date.

    Something along these lines:

    Code:
    //Handwritten
    DECLARE @from DATETIME   //Current Selected datetime [the newest of the current user doing the query]
    DECLARE @id INT               //userID
    DECLARE @tbl TABLE (from datetime, userid int)
    
    
    SET @from = dbo.GetNewestFrom(@id)
    
    
    INSERT INTO @tbl(from, userid) VALUES ( @from, @id) //Inserting it to then later select it... Could have just selected it, details for now, move on...:)
    SELECT * FROM @tbl                                              //Returns the result of the current user...
    Code:
    //Handwritten
    CREATE FUNCTION GetNewestFrom @id int
    RETURN DATETIME
    BEGIN
    DECLARE @from DATETIME
    
    SET @from = SELECT MAX(from) FROM table WHERE userID = @id AND type = 1   //The query for persons without type = 1, is just selecting the MAX(from) FROM table...
    //It is if Type = 1, then I shall check for other registered dates for the user, so from here on out, pretending Type = 1, without further checks...
    
    DECLARE @end INT
    
    SET @end = 0
    DECLARE @from2 DATETIME
    
    WHILE (@end = 0)
    BEGIN
         SET @from2 = 
         (SELECT from FROM table WHERE userID = @id AND @from IN (SELECT table.to + 1 FROM table WHERE userID = @id)
    
         IF @from IS NULL 
         BEGIN
              @end = 1 //we have selected the newest from, that did not have a "to-date" less than a day... Which mean the rows had a gap of at least 2 days between the registering...
         END
       
         IF @from IS NOT NULL //Else :P
         BEGIN
             @from = @from2
         END
    ///Continue on reading....
    
    END
    
    
    SELECT @from //Or is it return @datetime, anyways... 
    END
    So, for user ID 199, it shall return 2012.11.23, because he is Type != 1...

    But for user ID 201 [all users with Type = 1], I need to run through some more code, to figure out which date we want... Which date do I want? The "from"-date that is the newest, but if "to" is just 1 day before "from", I want to select the "from" at that row instead... Example with numbers [integers] instead of Date:
    Code:
     From - To 
     5      8
    10    15
    16    20
    I want to select "10" in the from column as the maximum "from"-integer, and not 16. Because in the "To" column we have a match in the "from" column on the condition "To + 1" [15+ 1 == 16], so then I want the "from" value where "To" equals 15, which is 10. The top row I do not want, because the difference between From and To [10 - 8 is larger than 1]...

    This means that the Image shown... I am first selecting the "1", then I do some checks, if the "to" for the user is 1 day earlier than the MAX(from) I grab a new "from" value...

    What I need help with, I think, or I hope I'll eventually solve it anyways, but struggling with thinking straight, grabbing the "from"-cell recursivly, based on "to"-cell for a user...


    Maybe looking at this the wrong way... Thinking of it as a programming language,I mean, C++/C#, which gives me a headache when trying to get it to work in SQL...Oh well, its weekend, saturday night...


    Maybe I can do something similar to this???
    Code:
    SELECT MIN (t1.from) FROM table as t1
    WHERE
    t1.from IN
    (
    	SELECT t2.to+ 1 FROM table as t2
    	WHERE t2.userID = @id
    )

    Last edited by ManyTimes; 12-08-12 at 19:32.

  2. #2
    Join Date
    Sep 2010
    Posts
    12
    Please provide table definition with sample data and logic to archive expect result. Your explanation is very unclear.

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    41
    Yes, my explanation was indeed unclear, hopefully I've cleared it up a bit... I've edited the first post.

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    It would still be more helpful if you post the table definition, some sample data, and the rules (in sentence form).

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    This probably isn't what you want, but it might send you in the right direction:
    Code:
    DECLARE @t TABLE (
       [id]     INT
    ,  [userid] INT
    ,  [from]   DATE
    ,  [to]     DATE
    ,  [type]   INT
    ,  [value]  NVARCHAR(9)
       );
       
    INSERT INTO @t
       VALUES
          (1, 201, '11/11/2012', '11/28/2012', 1, 'a string')
    ,     (2, 199, '11/23/2012',         null, 2, 'a string')
    ,     (3, 201, '11/29/2012', '12/01/2012', 1, 'a string')
    ,     (4, 201, '10/03/2012', '10/15/2012', 3, 'a string')
    ,     (5, 201, '12/02/2012',         null, 1, 'a string');
    
    WITH a AS (
       SELECT b.[from], b.[to], b.[userid]
          FROM @t AS b
          WHERE b.[to] IS NOT NULL
       UNION ALL SELECT c.[from], c.[to], c.[userid]
          FROM @t AS c
          JOIN a AS d
             ON DATEADD(day, 1, c.[to]) = d.[from]
          WHERE  c.[userid] = d.[userid]
             AND c.[type] = 1)
    SELECT MIN(a.[from]) AS 'from'
    ,  a.[to]
       FROM a
       GROUP BY a.[to];
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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