Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: help with query using min datediff

    Hi,

    I'm having problems trying to figure this out and was hoping someone knows the solution

    basically I have a result set like this
    Code:
    id     startDate     EndDate       DiffInDate ReportID
    --------------------------------------------------
    1      2011/01/01   2011/01/01     0            100
    1      2011/01/02   2011/01/05     3            101
    1      2011/01/03   2011/01/07     4            102
    2      2011/01/01   2011/01/02     1            103
    2      2011/01/02   2011/01/04     2            104
    3      2011/01/01   2011/01/01     0            105
    3      2011/01/02   2011/01/04     2            106
    its just a basic select (id, startdate,enddate, abs(datediff(day,startdate,enddate)), reportID from a bunch of different tables.
    what I want is to only return the lowest diffInDate value. Not sure what I need to add to do that. So in the end the result comes back as
    Code:
    id     startDate     EndDate       DiffInDate ReportID
    --------------------------------------------------
    1      2011/01/01   2011/01/01     0            100
    2      2011/01/01   2011/01/02     1            103
    3      2011/01/01   2011/01/01     0            105
    any ideas, I assume I need something like a MIN(abs(datediff))) but not sure?

    Thanks in advance
    Last edited by Inf123; 09-01-11 at 12:57.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work:
    Code:
    WITH CTE AS
    (SELECT id, 
    	startdate,
    	enddate, 
    	reportID, 
    	ROW_NUMBER() OVER (PARTITION BY id ORDER BY abs(datediff(day, startdate, enddate)) DESC) as RowNumber
    from different_tables
    )
    SELECT id, 
    	startdate,
    	enddate, 
    	abs(datediff(day, startdate, enddate)), 
    	reportID 
    from CTE
    WHERE RowNumber = 1
    Suppose that multiple records have the same lowest datediffs, like 3 records with datadiff = 0, then you might need to add StartDate ASC to the ORDER BY clause, to select the lowest StartDate, (or StartDate DESC to get the most recent StartDate):

    ROW_NUMBER() OVER (PARTITION BY id ORDER BY abs(datediff(day, startdate, enddate)) DESC, StartDate ASC) as RowNumber
    Last edited by Wim; 09-01-11 at 13:59.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    4
    I keep getting
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'WITH'.

    on the first line WITH CTE AS

    I'm using sql server 2008

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I am using SQL Server 2008 myself. It compiles without any problem.

    Can you delete the first line(s), including
    WITH CTE AS
    and type that line again manually? I think some invisible character is causing that error message.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure, but I think that any statement before the WITH keyword needs to end with a semicolon. That may only be a SQL 2005 restriction, though. I may need to test that out....

    OK. It is true, but it gives a different error:
    Code:
    declare @var varchar(30)
    
    with CTE 
    as
    (select getdate() as Now)
    
    select *
    from CTE
    gives
    Code:
    Msg 319, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous 
    statement must be terminated with a semicolon.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Paste the SQL script in a new and empty window of SQL Server Mgmt Studio or let the previous statement terminate with a ";" or with "GO".
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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