Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    5

    Unanswered: Combining two rows into one

    Hi All,

    I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Can someone point me in the direction of the technique I need to use to do this? Highlighted in blue is an example of the values I need to return within one record.

    Person startdate enddate Money
    7d339 7/1/2014 12/31/2014 1000
    7d339 1/1/2014 5/23/2014 355

    Thanks In Advance,
    BI_Cal

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @BI_Cal TABLE 
    (  [Person]         CHAR(5)
    ,  [startdate]      DATETIME
    ,  [enddate]        DATETIME
    ,  [Money]          MONEY
    )
    
    INSERT INTO @BI_Cal ([Person], [startdate], [enddate], [Money]) VALUES
       ('7d339', '7/1/2014', '12/31/2014', 1000)
    ,  ('7d339', '1/1/2014', '5/23/2014',   355)
    
    ; WITH cte AS (
       SELECT [Person], Min(startdate) AS first_start, Max(startdate) AS last_start
          FROM @BI_Cal
    	  GROUP BY [Person]
    )
    SELECT a.[Person], cte.[first_start] AS startdate, a.[enddate], a.[Money]
       FROM cte
       INNER JOIN @BI_Cal AS a
          ON (a.Person = cte.Person
    	  AND a.startdate = cte.last_start)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2014
    Posts
    5

    Red face

    PatP,

    Thanks for your reply. I thought of min() and max() right after I clicked post.

Posting Permissions

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