Merge two records date if dates are continuous and key values are same

Question

I have two different scenarios. In the first scenario I need something like:

create table test
(
ItemID int,
ItemStartDate datetime,
ItemEndDate datetime,
itemType varchar(100)
)

Table test:

ItemID  ItemStartDate  ItemEndDate  itemType
------  -------------  -----------  --------
item_1  1/1/2011       3/2/2011     value A
item_1  3/3/2011       12/31/2011   value A
item_2  1/3/2011       12/31/2011   value B

It should show only two records:

ItemID  ItemStartDate  ItemEndDate  itemType
------  -------------  -----------  --------
item_1  1/1/2011       12/31/2011   value A
item_2  1/1/2011       12/31/2011   value B

Scenario 2. Here I would like to split data value to separate year periods if it's across multiple years.

Table test

create table #Scenario_2
(
ItemID int,
priceStartDate datetime,
priceEndDate datetime,
price int
)

item  startdate  enddate     value
----  ---------  ----------  -----
11    1/1/2011   5/4/2013    500
12    7/1/2013   11/12/2013  600

It should show like

item  startdate  enddate     value
----  ---------  ----------  -----
11    1/1/2011   12/31/2011  500
11    1/1/2012   12/31/2012  500
11    1/1/2013   5/4/2013    500
12    7/1/2013   11/12/2013  600

Please advise how I can achieve this.

Please add your table column definition to the question and edit the sample data to reflect the columns, and know that using `--` will make it look like a comment, so separate columns with whitespaces instead. — jpw, Oct 24 '14 at 21:29
For scenario1 you could see [this answer](http://stackoverflow.com/a/26273819/3630826). — Jaugar Chang, Oct 28 '14 at 01:48
i am getting null in enddate by using this solution even though there is enddate in source data — Bavi, Oct 31 '14 at 19:05

Pரதீப் · Answer 1 · 2014-10-28T01:35:17.920

1

Try this. from your question this is what i understood!!

SCENARIO 2
----------
CREATE TABLE #datt
  (
     itemid   int,startd DATE,endat  DATE,price int
  )

INSERT INTO #datt
VALUES      (11,'2011-01-01','2013-05-04',500),
            (12,'2013-7-1','2013-11-12',600)

;WITH cte
     AS (SELECT itemid,
                 startd st,
                case when year(endat)<> YEAR(startd) then Dateadd(yy, Year(startd) - 1899, -1)
                else endat end ed,price
         FROM   #datt
         UNION ALL
         SELECT a.itemid,
                Dateadd(yy, 1, st),
                CASE
                  WHEN Dateadd(yy, 1, ed) > b.endat THEN b.endat
                  ELSE Dateadd(yy, 1, ed)
                END,a.price
         FROM   cte a
                JOIN #datt b
                  ON a.itemid = b.itemid
                     AND a.ed < b.endat)
SELECT *
FROM   cte order by itemid,st

edited Oct 28 '14 at 01:35

answered Oct 24 '14 at 22:14

Pரதீப்

91,748
19
131
172

This seems does not working for me when i join table based on id and item value. please advise. – Bavi Oct 27 '14 at 14:25
@Bavi - In SCENARIO 2 output u didn't mention any item value column. If you want it can be added – Pரதீப் Oct 27 '14 at 16:48
how can i add it? because in my case when i try to add it its giving wrong result with all records with 1/1 to 12/31 instead for some dates if its starts from middle of the year or ends at middle of the year – Bavi Oct 27 '14 at 17:09
@Bavi update your question with proper expected output!! – Pரதீப் Oct 27 '14 at 17:16
@Pradeep, +1, you are persistent. – radar Oct 28 '14 at 01:36

score 0 · Accepted Answer · edited May 23 '17 at 12:20

0

For scenario1 you could see this answer.

For scenario2 there also have a similar answer you could reference.

But your question can be simplified like this:

with dates as
(
select number,cast(ltrim(number*10000+1231) as date) as dt
    from master..spt_values
    inner join 
        (select min(year(startdate)) as s_year
               ,max(year(enddate))  as e_year
         from Scenario_2) as y
    on number between y.s_year and y.e_year AND TYPE='P'
)
select 
    s.item
   ,case when year(dt) = year(startdate) 
         then startdate
         else dateadd(year,-1,dateadd(day,1,dt)) end  --or cast(ltrim(year(dt)*10000+101) as date)
   ,case when year(dt) = year(enddate)
         then enddate
         else dt end
   ,s.value
from 
    Scenario_2 s
inner join 
    dates d
on 
    d.number between year(s.startdate) and year(s.enddate)

SQL FIDDLE DEMO

edited May 23 '17 at 12:20

Community

1
1

answered Oct 28 '14 at 02:47

Jaugar Chang

3,176
2
13
23

i am getting null in enddate by using this solution even though there is enddate in source data – Bavi Oct 31 '14 at 19:20
I found scenario 1 logic from somewhere else and scenario 2 logic you gave worked fine. Thank you so much. – Bavi Oct 31 '14 at 19:28
Can you show me those input data can get null in enddate with this solution? You can use Sql fiddle. – Jaugar Chang Nov 01 '14 at 05:22

Merge two records date if dates are continuous and key values are same

2 Answers2