Fill Date Range Gaps and Values


  

 create table #forum (Country varchar(20), City varchar (20) , Market varchar(10),  NDate date, NOpen int, NClose int)

Insert into #forum values 
('Japan','Tokyo','Fish', '2017-01-01', 100, 150),  ('Japan','Tokyo','Fish', '2017-02-01', 150, 180), ('Japan','Tokyo','Fish', '2017-04-01', 180, 160)
,('France','Paris','Coffee', '2017-01-01', 1100, 950), ('France','Paris','Coffee', '2017-02-01', 950, 1080), ('France','Paris','Coffee', '2017-06-01', 1080, 1260),
('France','Paris','Fruit', '2017-01-01', 100, 110), ('France','Paris','Fruit', '2017-03-01', 110, 130),
('Argentina','Buenos Aires','Coffee', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffee', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffee', '2017-03-01', 50, 450),
('Argentina','Buenos Aires','Coffee', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffee', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffee', '2017-06-01', 320, 200)

--Expected Result
create table #forumResult (Country varchar(20), City varchar (20) 
, Market varchar(10),  NDate date, NOpen int, NClose int)

Insert into #forumResult values 
('Japan','Tokyo','Fish', '2017-01-01', 100, 150),  ('Japan','Tokyo','Fish', '2017-02-01', 150, 180), ('Japan','Tokyo','Fish', '2017-03-01', 180, 180),
('Japan','Tokyo','Fish', '2017-04-01', 180, 160),
('France','Paris','Coffee', '2017-01-01', 1100, 950), ('France','Paris','Coffee', '2017-02-01', 950, 1080), ('France','Paris','Coffee', '2017-03-01', 1080, 1080),
('France','Paris','Coffee', '2017-04-01', 1080, 1080), ('France','Paris','Coffee', '2017-05-01', 1080, 1080),('France','Paris','Coffee', '2017-06-01', 1080, 1260),
('France','Paris','Fruit', '2017-01-01', 100, 110), ('France','Paris','Fruit', '2017-02-01', 110, 110), ('France','Paris','Fruit', '2017-03-01', 110, 130),

('Argentina','Buenos Aires','Coffee', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffee', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffee', '2017-03-01', 50, 450),
('Argentina','Buenos Aires','Coffee', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffee', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffee', '2017-06-01', 320, 200)

select * from #forumResult

declare @startdate date 
declare @enddate date   

Select @startdate = min(NDate),@enddate = max(NDate) from #forum 

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

 --Country, City, Market and Dates  
,myCountryCityMarket as
(
select Country, City, Market, dateadd(month,n-1,@startdate) dt from Nums 
Cross join (Select Distinct Country, City, Market From  #forum ) a    
)

,mycte2 as (
Select cck.Country,cck.City,cck.Market, 
f.NOpen,f.NClose,dt, f.NDate  

FROM myCountryCityMarket cck
LEFT JOIN    #forum f ON  f.NDate =cck.dt  
and f.Country=cck.Country and f.City =cck.City and f.Market= cck.Market  
WHERE cck.dt<=@enddate --last date
 )

 select Country,City,Market,dt NDate  
 , ISNULL(NOpen, NCloseFill) NOpen
 , ISNULL(NClose, NCloseFill) NClose  
 from mycte2 m
 Outer apply (select top 1 NClose from mycte2 m2 
WHERE m2.Country=m.Country and m2.City =m.City and m2.Market= m.Market
 and m2.dt<=m.dt and NClose is not null 
Order by dt DESC) d1(NCloseFill)
 Outer apply (select top 1 NClose from mycte2 m2 
WHERE m2.Country=m.Country and m2.City =m.City and m2.Market= m.Market
 and m2.dt>=m.dt and NClose is not null 
Order by dt DESC) d2(NCloseFill2)
WHERE NCloseFill2 is not null
Order by 1,3,4

drop table  #forumResult, #forum 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e5a9bf94-8f89-414c-8c42-a0bada141971/filling-the-gap-in-the-calendar-having-open-and-close-value?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s