CODEWITHSUNDEEP
sqlsql-servert-sqldate
Bavi
Bavi

Reputation: 3

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

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.

Upvotes: 0

Views: 1244

Answers (2)

Jaugar Chang
Jaugar Chang

Reputation: 3196

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

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 1

Related Questions