Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: sql query help...please

    Hi to all good people out there!

    I need a little help with one sql query. Suppose we have one table with data like this:
    Date ID
    -----------------
    4-1-2013 10
    4-2-2013 10
    4-3-2013 10
    4-4-2013 10
    4-5-2013 20
    4-6-2013 20
    4-7-2013 10
    4-8-2013 10
    4-9-2013 10
    4-10-2013 30
    -----------------

    Now, I would like to get min and max dates for each ID, but like this:
    ID min max
    ------------------------------
    10 4-1-2013 4-4-2013
    20 4-5-2013 4-6-2013
    10 4-7-2013 4-9-2013
    30 4-10-2013 4-10-2013
    -------------------------------

    Is it possible somehow? Thanks in advance!
    B.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there were some gaps in date, like...
    Code:
    Date      ID
    --------- --
    5-01-2013 40
    5-02-2013 40
    5-04-2013 50
    5-05-2013 50
    5-06-2013 50
    5-07-2013 50
    5-09-2013 50
    5-10-2013 50
    which result do you want?

    (a)
    Code:
    id min       max
    -- --------- ---------
    40 5-01-2013 5-02-2013
    50 5-04-2013 5-07-2013
    50 5-09-2013 5-10-2013
    (b)
    Code:
    id min       max
    -- --------- ---------
    40 5-01-2013 5-02-2013
    50 5-04-2013 5-10-2013

  3. #3
    Join Date
    Apr 2013
    Posts
    4

    sql...

    I want result (a) because from date 5-4-13 to date 5-10-13 the ID is 50.
    and if add two more dates like this:
    Date ID
    --------- --
    5-01-2013 40
    5-02-2013 40
    5-04-2013 50
    5-05-2013 50
    5-06-2013 50
    5-07-2013 50
    5-09-2013 50
    5-10-2013 50
    5-11-2013 60
    5-12-2013 50

    I want result like this:
    id min max
    -- --------- ---------
    40 5-01-2013 5-02-2013
    50 5-04-2013 5-10-2013
    60 5-11-2013 5-11-2013
    50 5-12-2013 5-12-2013

    So, min and max dates on every ID change.

  4. #4
    Join Date
    Apr 2013
    Posts
    4

    sql...

    sorry tonkuma...here is right example

    If add two more dates in your table like this:
    Date ID
    --------- --
    5-01-2013 40
    5-02-2013 40
    5-04-2013 50
    5-05-2013 50
    5-06-2013 50
    5-07-2013 50
    5-09-2013 50
    5-10-2013 50
    5-11-2013 60
    5-12-2013 50

    I want result like this:
    id min max
    -- --------- ---------
    40 5-01-2013 5-02-2013
    50 5-04-2013 5-07-2013
    50 5-09-2013 5-10-2013
    60 5-11-2013 5-11-2013
    50 5-12-2013 5-12-2013

    So, min and max dates on every ID change including date gaps.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Not tested on Microsoft SQL Server.

    Code:
    SELECT id
         , date AS min_date
         , (SELECT MIN(date)
             FROM  sample_data AS s3
             WHERE s3.id   =  s1.id
               AND s3.date >= s1.date
               AND NOT EXISTS
                   (SELECT 0
                     FROM  sample_data AS s4
                     WHERE s4.id   = s3.id
                       AND s4.date = DATEADD(dd , 1 , date)
                   )
           ) AS max_date
     FROM  sample_data AS s1
     WHERE NOT EXISTS
           (SELECT 0
             FROM  sample_data AS s2
             WHERE s2.id   = s1.id
               AND s2.date = DATEADD(dd , -1 , date)
           )
     ORDER BY
           min_date
    ;

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    removed post (how can I delete a post?)
    Last edited by Wim; 04-30-13 at 08:52.
    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

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by tonkuma View Post
    Not tested on Microsoft SQL Server.
    Tonkuma, I have tested your code. This is the result:
    Code:
    ID	min_date	max_date
    40	2013-05-01	2013-05-01
    40	2013-05-02	2013-05-02
    50	2013-05-04	2013-05-04
    50	2013-05-05	2013-05-05
    50	2013-05-06	2013-05-06
    50	2013-05-07	2013-05-07
    50	2013-05-09	2013-05-09
    50	2013-05-10	2013-05-10
    60	2013-05-11	2013-05-11
    50	2013-05-12	2013-05-12
    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

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work.
    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'dbo.DaTable') and OBJECTPROPERTY(id, N'IsTable') = 1)
    	DROP TABLE dbo.DaTable
    GO
    
    CREATE TABLE DaTable(
    	MyDate	DATE	NOT NULL,
    	ID		INT		NOT NULL
    )
    
    INSERT INTO DaTable(MyDate, ID) VALUES
    ('5-01-2013', 40),
    ('5-02-2013', 40),
    
    ('5-04-2013', 50),
    ('5-05-2013', 50),
    ('5-06-2013', 50),
    ('5-07-2013', 50),
    
    ('5-09-2013', 50),
    ('5-10-2013', 50),
    ('5-11-2013', 60),
    ('5-12-2013', 50)
    
    --SELECT * FROM DaTable
    
    GO
    WITH CTE AS
    (
    SELECT D1.MyDate as DateFrom, D1.MyDate as DateTo, D1.ID
    FROM DaTable as D1
    	LEFT OUTER JOIN DaTable as prev ON
    		dateadd(d, -1, D1.MyDate) = prev.MyDate
    		AND D1.ID = prev.ID
    WHERE prev.ID IS NULL
    
    UNION ALL
    
    SELECT CTE.DateFrom, DaTable.MyDate, CTE.ID
    FROM CTE
    	INNER JOIN DaTable ON
    		CTE.DateTo = dateadd(d, -1, DaTable.MyDate)
    		AND CTE.ID = DaTable.ID
    ),
    CTE2 AS
    (
    SELECT CTE.DateFrom,
    	CTE.DateTo,
    	CTE.ID,
    	ROW_NUMBER() OVER (PARTITION BY CTE.DateFrom, CTE.ID ORDER BY CTE.DateTo DESC) as RowNum
    FROM CTE
    )
    SELECT CTE2.DateFrom, CTE2.DateTo, CTE2.ID
    FROM CTE2
    WHERE RowNum = 1
    ORDER BY CTE2.DateFrom
    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

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Wim View Post
    Tonkuma, I have tested your code. This is the result:
    Code:
    ID	min_date	max_date
    40	2013-05-01	2013-05-01
    40	2013-05-02	2013-05-02
    50	2013-05-04	2013-05-04
    50	2013-05-05	2013-05-05
    50	2013-05-06	2013-05-06
    50	2013-05-07	2013-05-07
    50	2013-05-09	2013-05-09
    50	2013-05-10	2013-05-10
    60	2013-05-11	2013-05-11
    50	2013-05-12	2013-05-12
    Sorry, my mistake.
    date in DATEADD functions should be qualified, like "s3." and "s1." in the following example.
    Code:
    SELECT id
         , date AS min_date
         , (SELECT MIN(date)
             FROM  sample_data AS s3
             WHERE s3.id   =  s1.id
               AND s3.date >= s1.date
               AND NOT EXISTS
                   (SELECT 0
                     FROM  sample_data AS s4
                     WHERE s4.id   = s3.id
                       AND s4.date = DATEADD(dd , 1 , s3.date)
                   )
           ) AS max_date
     FROM  sample_data AS s1
     WHERE NOT EXISTS
           (SELECT 0
             FROM  sample_data AS s2
             WHERE s2.id   = s1.id
               AND s2.date = DATEADD(dd , -1 , s1.date)
           )
     ORDER BY
           min_date
    ;
    I will update the sample code in my previous post, if my new sample worked.

  10. #10
    Join Date
    Apr 2013
    Posts
    4

    ...

    thanks tonkuma!
    I'll try your advice.

Posting Permissions

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