-2

I have table in which records are date bound. Example 

UserID | Amount | StartDate | EndDate
1500    100       1/1/2014   12/31/2014
1500    100       1/1/2015   12/31/2015
1500    50        1/1/2016   12/31/2016
1500    100       1/1/2017   12/31/2099

The desired result that I am trying to get is 

UserID  Amount  StartDate   EndDate
1500    100      1/1/2014   12/31/2015
1500    50       1/1/2016   12/31/2016
1500    100      1/1/2017   12/31/2099

If the records are same, I need to join them. Please let me know if you have any suggestions to get the desired result. I need to be able to run the query on sql server 2012.

Thanks,

sql snippet to create a temp table - 

 IF OBJECT_ID('tempdb..#SplitRecords') IS NOT NULL
DROP TABLE #SplitRecords
CREATE TABLE #SplitRecords
(
    UserID INT,
    Amount DECIMAL(19,4),
    StartDate DATETIME,
    EndDate DATETIME
)

INSERT INTO #SplitRecords
        ( UserID, Amount, StartDate, EndDate )
VALUES  ( 1500, -- UserID - int
          100, -- Amount - decimal
          '1/1/2014', -- StartDate - datetime
          '12/31/2014'  -- EndDate - datetime
          )
INSERT INTO #SplitRecords
        ( UserID, Amount, StartDate, EndDate )
VALUES  ( 1500, -- UserID - int
          100, -- Amount - decimal
          '1/1/2015', -- StartDate - datetime
          '12/31/2015'  -- EndDate - datetime
          )

INSERT INTO #SplitRecords
        ( UserID, Amount, StartDate, EndDate )
VALUES  ( 1500, -- UserID - int
          100, -- Amount - decimal
          '1/1/2016', -- StartDate - datetime
          '12/31/2016'  -- EndDate - datetime
          )

INSERT INTO #SplitRecords
        ( UserID, Amount, StartDate, EndDate )
VALUES  ( 1500, -- UserID - int
          100, -- Amount - decimal
          '1/1/2017', -- StartDate - datetime
          '12/31/2099'  -- EndDate - datetime
          )
Yousuf
  • 3,105
  • 7
  • 28
  • 38

1 Answers1

0

Do a left join with first table where there s a group by and min for start date and the second table where you join on with group by and max end date. Does this help you?

I can t write code now but I can tomorrow.

cybork
  • 569
  • 2
  • 5
  • 24
  • Thank you, It was duplicate question. As mentioned by Bulat in comments above, it is duplicate of http://stackoverflow.com/questions/6068619/merging-date-intervals-in-sql-server – Yousuf Aug 26 '15 at 20:50
  • Ok. I see. And you too. Good luck! – cybork Aug 26 '15 at 20:57