none
Filling the gap in the calendar having open and close value RRS feed

  • Question

  • Hi guys, I need some suggestion in how I can fill the gap in the markets as below:

    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','Coffe', '2017-06-01', 1080, 1260),

    ('France','Paris','Fruit', '2017-01-01', 100, 110), ('France','Paris','Fruit', '2017-03-01', 110, 130), ('Argentina','Buenos Aires','Coffe', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffe', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffe', '2017-03-01', 50, 450), ('Argentina','Buenos Aires','Coffe', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffe', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffe', '2017-06-01', 320, 200) --Expected Result Insert into #forum 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','Coffe', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffe', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffe', '2017-03-01', 50, 450), ('Argentina','Buenos Aires','Coffe', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffe', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffe', '2017-06-01', 320, 200)


    My company worked in the fish market in Tokyo from January to April but was inactive on March. It worked in the coffee market in Paris from Jan to Feb, got inactive and started again on June. It's ok in the coffee market in Buenos Aires, never been inactive from Jan to Jun. It worked in the fruit market in Paris on January and March, it was inactive on February. 

    I do need, (data visualization issue), to fill the gap by country, city, market taking the Nclose as Nopen of the next month and having the Nclose as the Nopen if the month is null (as in the expected result). 

    Thinking to build a calendar and to take create some variable but I was wondering if there is something simpler that I can do. 

    Thanks


    • Edited by DIEGOCTN Wednesday, February 21, 2018 11:36 AM
    Wednesday, February 21, 2018 11:35 AM

Answers

  • Hi there!

    Try using this query:

    /* Identifying Gap */
    ;With c_gap
    As
    (
    	Select	*
    			,Iif
    			(
    				DateDiff
    				(
    					Month
    					,NDate
    					,Lead(NDate, 1, Null) Over(Partition By Country, City, Market Order By NDate)
    				) > 1
    				, 1
    				,0
    			) IsGap
    			,Lead(NDate, 1, Null) Over(Partition By Country, City, Market Order By NDate) NextDate
    	From	#forum
    )
    Select	*
    Into	#gap
    From	c_gap
    Where	IsGap = 1
    
    --Months
    ;With c_month
    As
    (
    	
    	Select	Country	
    			,City	
    			,Market	
    			,Convert(Date, '20170101') NDate
    	From	#forum
    	Group By Country	
    			,City	
    			,Market	
    
    	Union All
    
    	Select	Country	
    			,City	
    			,Market	
    			,Convert(Date, DateAdd(Month, 1, NDate)) NDate
    	From	c_month
    	Where	NDate < '20170601'
    
    )
    --Filling the gap
    Select	c.Country, c.City, c.Market, c.NDate, IsNull(f.NOpen, g.NClose) NOpen, IsNull(f.NClose, g.NClose) NClose
    From	c_month c
    		Left Join #forum f
    			On	c.Country = f.Country
    			And	c.City = f.City
    			And	c.Market = f.Market
    			And c.NDate = f.NDate
    		Left Join #gap g
    			On	c.Country = g.Country
    			And	c.City = g.City
    			And	c.Market = g.Market
    			And c.NDate > g.NDate And c.NDate < g.NextDate
    Where	IsNull(f.NClose, g.NClose) Is Not Null
    Order By Country, City, Market, NDate
    

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Wednesday, February 21, 2018 12:33 PM
  • Hi,

    You can try below code,

    ;WITH CTE AS
    (
    	SELECT F1.Country F1Country, F1.City F1City, F1.Market F1Market, F1.NDate BeginDate, F2.NDate EndDate , F2.NOpen NClose , 1 Frequency
    	  FROM #forum F1
    INNER JOIN #forum F2 
    		ON F1.Country = F2.Country AND F1.City = F2.City AND F1.Market = F2.Market 
    	 WHERE F2.NOpen <> F1.NOpen AND F1.NClose = F2.NOpen
    	   AND DATEDIFF(MM, F1.NDate , F2.NDate) <> 1
    	
    	UNION ALL
    	
    	SELECT F1Country, F1City, F1Market,  DATEADD(mm, Frequency, BeginDate), EndDate,  NClose, 1 Frequency
    	  FROM CTE
    	 WHERE DATEADD(mm, Frequency, BeginDate) > BeginDate AND DATEADD(mm, Frequency, BeginDate) < EndDate
    )
    
    SELECT Country , City  , Market,  NDate, NOpen, NClose 
      FROM #forum
    UNION
    SELECT F1Country AS Country, F1City City, F1Market Market,BeginDate NDate, NClose NOpen, NClose 
      FROM CTE

    Please mark as answer if it was helpful

    Regards


    Sandeep Prajapati

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Wednesday, February 21, 2018 12:43 PM
  • 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 

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Wednesday, February 21, 2018 4:10 PM
    Moderator
  • Hi DIEGOCTN,

    Please refer to following script, see if it works for you:

    ;with cte as(
    	select cast('2017-01-01' as date) as [date]
    	union all
    	select DATEADD(MONTH,1,[date]) as [date]
    	from cte
    	where [date]<'2018-12-01'
    ),
    cte2 as(
    	select Country,City,Market,MIN(NDate) as MINDate,MAX(NDate) as MAXDate
    	from #forum
    	group by Country,City,Market
    )
    
    select t.*,f1.NOpen,f2.NClose
    from (
    	select Country,City,Market,c.date	
    	from cte2 c2
    	left join cte c on c.date between MINDate and MAXDate) t
    outer apply (select top 1 f.NOpen from #forum f where t.Country=f.Country and t.City=f.City and t.Market=f.Market and t.date<=f.NDate order by f.NDate) f1
    outer apply (select top 1 f.NClose from #forum f where t.Country=f.Country and t.City=f.City and t.Market=f.Market and t.date>=f.NDate order by f.NDate desc) f2

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Thursday, February 22, 2018 2:39 AM

All replies

  • Hi there!

    Try using this query:

    /* Identifying Gap */
    ;With c_gap
    As
    (
    	Select	*
    			,Iif
    			(
    				DateDiff
    				(
    					Month
    					,NDate
    					,Lead(NDate, 1, Null) Over(Partition By Country, City, Market Order By NDate)
    				) > 1
    				, 1
    				,0
    			) IsGap
    			,Lead(NDate, 1, Null) Over(Partition By Country, City, Market Order By NDate) NextDate
    	From	#forum
    )
    Select	*
    Into	#gap
    From	c_gap
    Where	IsGap = 1
    
    --Months
    ;With c_month
    As
    (
    	
    	Select	Country	
    			,City	
    			,Market	
    			,Convert(Date, '20170101') NDate
    	From	#forum
    	Group By Country	
    			,City	
    			,Market	
    
    	Union All
    
    	Select	Country	
    			,City	
    			,Market	
    			,Convert(Date, DateAdd(Month, 1, NDate)) NDate
    	From	c_month
    	Where	NDate < '20170601'
    
    )
    --Filling the gap
    Select	c.Country, c.City, c.Market, c.NDate, IsNull(f.NOpen, g.NClose) NOpen, IsNull(f.NClose, g.NClose) NClose
    From	c_month c
    		Left Join #forum f
    			On	c.Country = f.Country
    			And	c.City = f.City
    			And	c.Market = f.Market
    			And c.NDate = f.NDate
    		Left Join #gap g
    			On	c.Country = g.Country
    			And	c.City = g.City
    			And	c.Market = g.Market
    			And c.NDate > g.NDate And c.NDate < g.NextDate
    Where	IsNull(f.NClose, g.NClose) Is Not Null
    Order By Country, City, Market, NDate
    

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Wednesday, February 21, 2018 12:33 PM
  • Hi,

    You can try below code,

    ;WITH CTE AS
    (
    	SELECT F1.Country F1Country, F1.City F1City, F1.Market F1Market, F1.NDate BeginDate, F2.NDate EndDate , F2.NOpen NClose , 1 Frequency
    	  FROM #forum F1
    INNER JOIN #forum F2 
    		ON F1.Country = F2.Country AND F1.City = F2.City AND F1.Market = F2.Market 
    	 WHERE F2.NOpen <> F1.NOpen AND F1.NClose = F2.NOpen
    	   AND DATEDIFF(MM, F1.NDate , F2.NDate) <> 1
    	
    	UNION ALL
    	
    	SELECT F1Country, F1City, F1Market,  DATEADD(mm, Frequency, BeginDate), EndDate,  NClose, 1 Frequency
    	  FROM CTE
    	 WHERE DATEADD(mm, Frequency, BeginDate) > BeginDate AND DATEADD(mm, Frequency, BeginDate) < EndDate
    )
    
    SELECT Country , City  , Market,  NDate, NOpen, NClose 
      FROM #forum
    UNION
    SELECT F1Country AS Country, F1City City, F1Market Market,BeginDate NDate, NClose NOpen, NClose 
      FROM CTE

    Please mark as answer if it was helpful

    Regards


    Sandeep Prajapati

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Wednesday, February 21, 2018 12:43 PM
  • 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 

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Wednesday, February 21, 2018 4:10 PM
    Moderator
  • Hi DIEGOCTN,

    Please refer to following script, see if it works for you:

    ;with cte as(
    	select cast('2017-01-01' as date) as [date]
    	union all
    	select DATEADD(MONTH,1,[date]) as [date]
    	from cte
    	where [date]<'2018-12-01'
    ),
    cte2 as(
    	select Country,City,Market,MIN(NDate) as MINDate,MAX(NDate) as MAXDate
    	from #forum
    	group by Country,City,Market
    )
    
    select t.*,f1.NOpen,f2.NClose
    from (
    	select Country,City,Market,c.date	
    	from cte2 c2
    	left join cte c on c.date between MINDate and MAXDate) t
    outer apply (select top 1 f.NOpen from #forum f where t.Country=f.Country and t.City=f.City and t.Market=f.Market and t.date<=f.NDate order by f.NDate) f1
    outer apply (select top 1 f.NClose from #forum f where t.Country=f.Country and t.City=f.City and t.Market=f.Market and t.date>=f.NDate order by f.NDate desc) f2

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DIEGOCTN Thursday, February 22, 2018 2:42 PM
    Thursday, February 22, 2018 2:39 AM
  • Thanks guys, 

    I am adopting Xi's code. It is the most suitable with my real code. Sandeep, I did tick your reply as answer, seen the effort you made but there is an error. For example Paris Fruit 201801 is repeated twice.

    Thursday, February 22, 2018 2:44 PM